MSSQL Interview Questions

1)What's the difference between a 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 nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

2)Describe the purposes and advantages stored procedure?

Stored procedures manage, control and validate data.
Large queries can be avoided.
Reduces network traffic since they need not be recompiled.
Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
Permissions can be granted for stored procedures. Hence, increases security. 

3)What is the Disadvantage of StoredProcedure?

When a procedure is created a execution plan is created proc is executed according to that execution plan. When we modify the proc it also follow this execution plan.
When index is rebuild new index structure is created but the proc follows the old execution plan because it was compiled so there is a chance of unexpected result.
For this there is need to recompile the proc.

 

4)Difference between Truncate and Delete

Truncate

• Truncate command is used to remove all rows of the column.
• The removed records are not recorded in the transaction log.
• It is the fast way to remove all the records from the table.
• The records once removed can’t be rolled back.
• It can’t activate trigger.
• It resets the identity of the column.

Delete

•      Delete command removes records one at a time and logs into the transaction log.
•          It can be used with or without where clause.
•          The records can be rolled back.
•          It activates trigger.
•          It doesn’t reset the identity of the column

5) What is index? Define its types.

Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record. Index is used to speed up query performance. Both exist as B-tree structure. Kind of the indexes are clustered and non-clustered.

Clustered index

Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.

Non-clustered

Non-clustered index is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table. 

6) Define Normalization 

It is the process of organizing data into related table. To normalize database, we divide database into tables and establish relationships between the tables. It reduces redundancy. It is done to improve performance of query.

7) What are the Steps of normalization

First Normal form

Entities of the table must have unique identifier or entity key.

Second Normal Form

 
All the attributes of the table must depend on the entity key for that entity. 

Third Normal Form

 
All attributes that are not part of the key must not depend on any other non-key attributes. 

8)What is De-normalization

The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization. 

9) What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

10) What is RANK in SQL Server

 Returns the rank of each row in the result set of partitioned column

select Name,Subject,Marks,

RANK() over(partition by name order by Marks desc)Rank

From ExamResult order by name,subject

11) What is DENSE RANK in SQL Server

This is same as RANK() function. Only differencec is returns rank with out gaps.

select  Name,Subject,Marks,

DENSE_RANK() over(partition by name order by Marks desc)Rank

From ExamResult order by name  

12) What is row number in SQL Server

Returns the serial number of the row order by specified column.

select Name,Subject,Marks,

ROW_NUMBER() over(order by Name) RowNumber

From ExamResult

order by name,subject

13) What is set nocount on in SQL Server

Problem

One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible.  As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle.  This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution

Sometimes even the simplest things can make a difference.  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.  This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.  This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.
By removing this extra overhead from the network it can greatly improve overall performance for your database and application.
If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.


14) What you mean by Self-Joins in SQL

 You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself, such as the employees table shown below:

id first_name last_name manager
----------- --------------- --------------- -----------
1 Pat Crystal NULL
2 Dennis Miller 1
3 Jacob Smith 1
4 Allen Hunter 2
5 Mary Underwood 3
6 Joy Needham 3

In this table, the manager attribute simply references the employee ID of another employee in the same table. For example, Dennis Miller reports to Pat Crystal. Pat is apparently the president of this company, as she reports to no one.

Suppose you're tasked with writing a SQL query to retrieve a list of employees and their managers. You can't write a basic SQL SELECT statement to retrieve this information, as you need to cross reference information contained in other records within the same table. Fortunately, you can use a self-join to solve this dilemma by joining the table to itself.

Here's the SQL statement that will retrieve the desired results:
SELECT e.first_name AS 'Employee FN', e.last_name AS 'Employee LN', m.first_name AS 'Manager FN', m.last_name AS 'Manager LN'
FROM employees AS e LEFT OUTER JOIN employees AS m
ON e.manager =m.id

And the corresponding output:
Employee FN Employee LN Manager FN Manager LN
--------------- --------------- --------------- ---------------
Pat Crystal NULL NULL
Dennis Miller Pat Crystal
Jacob Smith Pat Crystal
Allen Hunter Dennis Miller
Mary Underwood Jacob Smith
Joy Needham Jacob Smith
(6 row(s) affected)

15) How To Get a List of All Tables with "sys.tables" View in MS SQL Server?

If you want to see the table you have just created, you can use the "sys.tables" system view to get a list of all tables in the current database. The tutorial script gives you a good example:

SELECT name, type_desc, create_date FROM sys.tables

GO

name type_desc create_date

tip USER_TABLE 2007-05-19 23:05:43.700

The output shows that there is only one table in the current database.

16)How to create new tables with "SELECT ... INTO" statements in MS SQL Server?
 

SELECT * INTO tipBackup FROM tip

17)What does a @@fetch_status of -2 mean in SQL Server 2005?

The row being fetched is missing.

This means that the row that was being fetched from the cursor is missing.