SQL Server Interview Questions:

Peer-to-peer replication

Peer-to-peer replication also known as multimaster replication. Peer to peer replication configuration has the following characteristics: 1. Replication occurs between tables on two or more servers. 2. Updates on any one server are replicated to all other associated servers. 3. Applications on any of the servers can update the same rows and columns in those tables at the same time. 4. All servers are equal peers with equal ownership of the data; no server is the “master” or source owner of the data.

What is Cursor and what the difference between cursor and trigger?

CURSOR: Cursor is an Database object and retrive the rows from database row-by-row, and it’s mainly use for to reduce the network traffic, it contains 5 features. 1. DECLARE the Cursor 2. OPEN the cusrsor 3. FETCH the cursor 4. CLOSE the cursor 5. DEALLOCATE the cursor

TRIGGER : A trigger is a procedure that is executed automatically when some specific events occur in a table of a database. Triggers are mainly used for maintaining integrity in a database. Triggers are also used for enforcing business rules, auditing changes in the database and replicating data. Most common triggers are Data Manipulation Language triggers are triggered when data is manipulated.

Difference Between Trigger and Cursor. A trigger is executed automatically when some specific events occur in a table of a database, while a cursor is a control structure used in databases to go through the database records. A cursor can be declared and used within a trigger. In such a situation, the declare statement would be inside the trigger. Then the scope of the cursor would be limited to that trigger. Within a trigger, if a cursor is declared on an inserted or a deleted table, such a cursor would not be accessible from a nested trigger. Once a trigger is completed, all the cursors created within the trigger will be de-allocated.

What is CLR Integration:

Common Language Runtime (CLR ) in .NET applications using SQL Server 2008. The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. Here is the query for enabling CLR integration In SQL Server 2008.

SP_configure ‘clr enabled’, 1

GO 2

RECONFIGURE

GO

 What is Notification Services ?

Notification Services was designed to ease the pain of developing and deploying notification applications that generate personalized, timely information to subscribers

What is Constraints ?

Constraints are used to limit the type of data that can go into a table. NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT

1. Functions are used for computations where as procedures can be used for performing business logic
2. Functions MUST return a value, procedures need not be.
3. You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.
4. Function parameters are always IN, no OUT is possible.

There are so many ways to determine: 1. Right click on the server name and then click on properties from there you can see all the Server details including SP. 2. In a new Query Window window type select @@version and execute 3. SELECT SERVERPROPERTY(‘PRODUCTLEVEL’)

 What is Blocking how to troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

SP_who2 will give the list of blocking sessions. From that we can find the objects using dbcc command.

USE master

GO

Select spid,blocked from sysprocesses where blocked>0

GO

 What are statisticsStatistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to 3 an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

 Under which situvation you should update statistics?

1. If there is significant change in the key values of the index 2. If a large amount of data in an indexed column has been added, changed, or removed, or the table has been truncated using the TRUNCATE TABLE statement and then repopulated. 3) Database is upgraded from a previous version.

To update statistics here is the command.

USE AdventureWorks;

GO

UPDATE STATISTICS dbo.Tablename;

GO

 Difference between a Local temporary Table and Gloabal Temporary Table

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

 Difference between primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn’t allow Nulls, but unique key allows one NULL only.

Difference between Delete table and Truncate Table?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

 what are Constraints ?

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

What is a Join and explain difference  types of Joins?Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. 4

What are Cursors?

Cursors allow row-by-row processing of the result sets. Types of cursors: 1. Static, 2. Dynamic, 3. Forward only, 4. Key set-driven.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network round-trip, where as a normal SELECT query makes only one round-trip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors.

What is the basic difference between Clustered and Non Cluster Index?

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

 What is an Index?When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.

 What is Normalization?

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

1. Avoiding repetitive entries

2. Reducing required storage space

3. Preventing the need to restructure existing tables to accommodate new data.

4. Increased speed and flexibility of queries, sorts, and summaries.

Following are the normal forms

1NF Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.

2NF Eliminate Redundant Data – If an attribute depends on only part of a multivalued key, remove it to a separate table.

3NF Eliminate Columns Not Dependent On Key – If attributes do not contribute to a description of the key, remove them to a separate table. 5

BCNF Boyce-Cod Normal Form – If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

4NF Isolate Independent Multiple Relationships – No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF Isolate Semantically Related Multiple Relationships – There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF Optimal Normal Form – a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF Domain-Key Normal Form – a model free from all modification anomalies.

What is difference between Stored procedure and Trigger?

Stored procedure:- A stored procedure is a group of Transact-SQL statements that is compiled one time, and then can be executed many times. This increases performance when the stored procedure is executed because the Transact-SOL statements do not have to be recompiled.

Trigger:- A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

A CREATE PROCEDURE or CREATE TRIGGER statement cannot span batches. This means that a stored procedure or trigger is always created in a single batch and compiled into an execution plan.

What is isolation level ?

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed

There are four isolation levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

SNAPSHOT

READ COMMITTED SNAPSHOT

 What is Diff bet QSQL and Qyery Analyzer?

Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other usefull thing about OSQL is that its helps in scheduling

 Define Candidate Key, Alternative Key, Composite key?

Commit statement helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.COMMIT we can also use in store procedure.

ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are ROLLBACK to the database.

Candidate key: A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key.

Alternate key: There can be more than one keys which can identify each row of the table uniquely. One of them is defined as primary key and rest of them is called alternate keys of the table.

Composite Key: A key formed by combining at least two or more columns.

 What are diff types of Locks? Shared Lock?

Shared locks are used for operations that read data, such as a SELECT statement. During Shared locks used, concurrent transactions can read a resource, but cannot modify the data.

Update Lock: Update locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update locks are used to prevent a deadlock.

Exclusive Lock: Exclusive locks are used for data modification operations, such as UPDATE, INSERT, or DELETE. Other transactions cannot read or modify data locked with an Exclusive lock.

What is extent and types of extent ?

An extent is 8 continuous pages to hold server object.

Uniform extents – This type of extent contains data from one table.

Mixed extents – This type of extent contains data from two to eight different tables.

 What are different types of Joints?

INNER JOIN: Inner join shows matches only when they exist in both tables. Example, in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid.So this SQL will only give you result with customers who have orders. If the customer does not have order, it will not display that record. SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN: Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table. SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN: Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table. SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

What is diff bet Function and Stored Procedure?

1. Should return at-least one output parameter.Can return more than one parameter using OUT argument. 2. Parsed and compiled at run time.

3.Cannot affect the state of database.

4.Can be invoked from SQL statement eg. SELECT statement.

5. Functions are mainly used to compute values.

Procedure:

1. Doesn’t need to return values, but can return value.

2.Stored as a pseudo-code in database i.e. compiled form.

3.Can affect the state of database using commit etc.

4.Cannot be invoked from SQL statements eg. SELECT statement.

5.Procedures are mainly used to process the tasks.
What is diff bet Trigger and Stored Procedure?Trigger:

Trigger gets executed automatically whenever we perform the Insert, Update, Delete Operations.

Procedure:

While stored procedure are the pre-compiled one and reduce the traffic on the client/server side . It can be used by the multiple users.

What is diff bet Index Seek Vs. Index Scan? Index Scan:

1. An index scan is a complete scan of all the pages in a non-clustered index.

2. Index Scan retrieves all the rows from the table.

3. Index Scan is nothing but scanning on the data pages from the first page to the last page.

Index Seek:

1. Index Seek retrieves selective rows from the table.

2. Since a seek only touches rows that qualify and pages that contain these qualifying rows.

3. An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.

4. A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf.

 What is diff bet Union All and Union?

UNION statement eliminates duplicate rows whereas UNION ALL statement includes duplicate 8 rows. UNION statement can be used to combine any number of queries whereas UNION ALL statement can be used to combine a maximum of two queries. UNION statement cannot be used with aggregate functions whereas UNION ALL statement can be used with aggregate functions.

What is ACID Stands for ? Define transaction ?

ACID stands for Atomicity, Consistency, Isolation, Durability.

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction

1. Atomicity: A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.

2. Consistency: When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction’s modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

3. Isolation: Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

4. Durability: After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure

What’s the maximum size of a ……..? Maximum sizes/numbers
Bytes per row	8,060	8,060
Clustered indexes per table	1	1
Columns per base table	1,024	1,024
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	1,024	1,024
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	1,048,516 TB3	1,048,516 TB3
Nested stored procedure levels	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	249	249

Interview questions, sql server dba interview questions, sql server dba interview questions and answers, sql server dba interview questions and answers pdf, sql server 2008 dba interview questions, interview questions for sql server dba, sql dba interview questions, sql server production dba interview questions