SQL Server Interview Questions

Basic:
What is normalization?
Database normalization is a data design and organization processes applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
What are different normalization forms?
1NF: Eliminate Repeating Groups
i. Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
i. If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
i. If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
i. If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.
Stored procedures reduce network traffic and improve performance.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.
A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table.
Nested Trigger:
i. A trigger that contains data modification logic within itself is called a nested trigger.
What are the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
What is cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
Difference between primary and unique key?
Difference between delete and truncate command?
Difference between function and stored procedure?
Explain types of possible joins.
What are primary keys and foreign keys?


Intermediate:
How do you remove duplicate records from table?
What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
Explain the steps to work with cursors with different cursor types.
How do you truncate transaction log file?
What is linked server and purpose of having it?
What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared.
Explain the LOCK functionality in SQL server.
Explain about SQL profiler.
What are sub queries and co-related queries?
Supported types of authentication modes?
Error handing in SQL 2000 and 2005?
Transaction handling in stored procedures?
How do you rename database object?
Types of constraints?
What is De-normalization?
Virtual table names?
Difference between HAVING and WHERE clause?
What's the maximum size of a row?


Advanced:
Types of replication?
Purpose of sp_configure command?
OS SQL services?
Difference between STUFF and REPLACE function?
Steps to take database offline?
What are the basic functions for master, msdb, model, tempdb databases?
What is a Scheduled Jobs or what is a Scheduled Tasks?
What is BCP? When does it used?
How do you load large data to the SQL server database?
Explain DTS services?
What is Data Warehousing?
Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
What is OLTP (OnLine Transaction Processing)?
Role of XML in SQL Server and usage? Explain FOR XML (ROW, AUTO, EXPLICIT).
What is an execution plan? When would you use it? How would you view the execution plan?
Steps involved in granting denying permissions with example?
Explain different isolation levels.
What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.
What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks” in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.