SQL Server Interview Questions

1. What is a Stored Procedure?

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure.

2. What's the difference between a primary key and a unique key?

Both primary key and unique key enforces 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 nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

3. What’s the difference between a clustered and a non-clustered index?

A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.

4. Describe the difference between truncate and delete?

 The difference between these two processes is fairly simple. Truncate means to simply empty out a table. On the other hand, the delete command lets you delete entire rows from within a table, but not all of the data within that table.

5. Define a temp table ?

In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.

6. What’s the difference between a local temp table and a global temp table?

Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.

7. How do you use transactions?

In general, there are three types of transactions that you can use in the SQL Server environment: BEGIN TRANSACTION, ROLL BACK TRANSACTION and COMMIT TRANSACTION. The gist behind deploying transactions is that they allow you to group multiple SQL commands into a single unit. From there, each transaction begins with a certain task, and ends when all the tasks within the transaction are complete. BEGIN TRANSACTION gets the ball rolling. ROLLBACK TRANSACTION functions a lot like an “undo” command, and COMMIT TRANSACTION completes all of the tasks within that transaction.

8. What are DBCC commands?

In very basic terms the Database Consistency Checker (DBCC) is used to aid in server maintenance. DBCC commands, many of which are completely undocumented, provide a set of commands that let you perform routing maintenance, status and validation checks. The most common DBCC commands are: DBCC CHECKALLOC (Lets you check disk allocation); DBCC OPENTRAN (Lets you check any open transactions); and DBCC HELP (shows a list of available DBCC commands to aid your server maintenance processes).

9. What is a view?

 A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.

10. What is a Query Execution Plan?

 SQL Server has several built-in tools that optimize how queries are executed within their databases. A query execution plan is exactly what it sounds like – a snapshot of how the optimizing tools will execute and deploy specific queries within the database. This service helps you troubleshoot problems with jobs that don’t necessarily execute perfectly.

11. What is the default port number for SQL Server? 

 While this is kind of a softball question – if you know anything about SQL Server you should at least know the basic configuration options – it’s an important one to nail in the interview. Basically, when SQL Server is enabled the server instant listens to the TCP port 1433.

12. Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

13. What are clustered and non-clustered indexes?                    

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.

14. What are the different index configurations a table can have?                                                     A table can have one of the following index configurations:                                                           (a.)No indexes                                                                                                                           (b.)A clustered index                                                                                                                 (c.)A clustered index and many nonclustered indexes                                                                   (d.)A nonclustered index                                                                                                       (e.)Many nonclustered indexes

15. What are different types of Collation Sensitivity?                                                                        (a.)Case sensitivity - A and a, B and b, etc.                                                                    (b.)Accent sensitivity                                                                                                             (c.)Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.                                                                                   (d.)Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

16. What is OLTP (Online Transaction Processing)?

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Code rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

17. What are DMVs?

 Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance.