50 Frequently Asked SQL Server Interview Questions – Part I

  1. How will you to performance tuning ?
https://www.youtube.com/watch?v=P97_oFfD218
  1. What will you look for in an execution plan ?
http://www.mssqltips.com/sqlservertutorial/285/query-execution-plans/
  1. What is bookmark look-up related to execution plan
http://www.qdpma.com/CBO/s2kCBO_2a.html
  1. Table variables vs temp tables
http://engineering.picscout.com/2012/04/
sql-temporary-tables-vs-table-variables_17.html
  1. How do you pass output parameter in a stored procedure…
If you specify the OUTPUT keyword for a parameter in the procedure definition, 
the stored procedure can return the current value of the parameter to the calling
 program when the stored procedure exits. To save the value of the parameter in 
a variable that can be used in the calling program, the calling program must use 
the OUTPUT keyword when executing the stored procedure.
http://msdn.microsoft.com/en-us/library/ms187004%28v=sql.105%29.aspx
  1. If we have a stored procedure which have a 2 parameters in it but now we have to add a another Parameter how will you modify the stored procedure.
Add a comma along with another variable and it’s associated data type in the 
Declaration of the Store Procedure.
  1. When you pass a output parameter how do you return a value?
The calling program must use the OUTPUT keyword when executing the stored procedure.
  1. Why do we use cursors?
Cursors are a mechanism to explicitly enumerate through the rows of a result set, 
rather than retrieving it as such.  However, while they may be more comfortable 
to use for programmers accustomed to writing While Not RS.EOF Do ..., they are 
typically a thing to be avoided within SQL Server stored procedures if at all 
possible -- if you can write a query without the use of cursors, you give the 
optimizer a much better chance to find a fast way to implement it.
  1. Select * from emp —- will give us a table as a result….how can we get the result in XML format ?
Add - FOR XML RAW  or FOR XML AUTO to the end of the select statement.
  1. Which is better sub queries…or temp tables?
The only way to know for sure which is best is to first make sure both ways 
you are testing return the same results and then performance test them and 
check out the execution plans
  1. How do we debug a stored procedure ?
In SQL Server Management Studio, there is a Debug button in the toolbar. 
Set a break point in a query window to step through.
  1. Difference between stored procedure and view?
A view represents a virtual table. You can join multiple tables in a view and 
use the view to present the data as if the data were coming from a single table.

A stored procedure uses parameters to do a function... whether it is updating
 and inserting data, or returning single values or data sets. Stored procedures 
are best used for INSERT-UPDATE-DELETE statements and Views are used for SELECT 
statements.
  1. How do we do error handling in stored procedures?
http://www.sqlteam.com/article/handling-errors-in-stored-procedures
  1. Difference between Union and union all?
The difference between Union and Union all is that Union all will not eliminate 
duplicate rows, instead it just pulls all rows from all tables fitting your 
query specifics and combines them into a table.
  1. Talk about delete vs truncate
http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate
-and-delete-in-sql-server/
  1. Talk about clustered index vs non clustered index.
The main difference between a clustered and non-clustered index is that for a 
clustered index the data rows are sorted in sequence for the index key. The data 
rows are essentially the bottom level of the clustered index. For a non-clustered
index, the data rows are not sorted in the index key sequence. The bottom level 
of the non-clustered index is a set of pointers to whatever rows match each key 
in the index.
  1.  What are the different types of joins…..what is a self-join
 1.Self Join – Joining a table to itself
 2.Inner Join
 3.Outer Join
 3.1. Right Outer Join
 3.2. Left Outer Join
 3.3 Full Outer Join
 4.Cross join
  1. How will get the second greatest value in a table.
SELECT         max(score )
FROM           scores
WHERE          score not in (SELECT max(score) FROM scores)
  1. What are the Normalization Forms
1NF: Eliminate Repeating Groups- 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- 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- 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

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.

20. What is difference between Block and deadlock?

http://www.teradataforum.com/teradata/20030221_033051.htm

21.How do you monitor Sql Server?

https://blog.serverdensity.com/monitor-sql-server/

22.Did you ever use UPDATE Statistics?

http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date
-update-statistics/

23. What is normalization and de-normalization.

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.

 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.

24. How to link two databases which are not on same servers(linked servers)

How to create a link Server - http://msdn.microsoft.com/en-us/library/
aa560998%28v=bts.20%29.aspx

25. Talk about Pros & Cons of Views

Pros - Hide data complexity. Instead of forcing your users to learn the T-SQL 
JOIN syntax you might wish to provide a view that runs a commonly requested SQL 
statement.

Cons - Even though views can be a great tool for securing and customizing data, 
they can be slow. Indeed, they are not any faster than the query that defines 
them
http://sqlserverpedia.com/wiki/Views_-_Advantages_and_Disadvantages

26. Talk about Full outer join vs Cartesian joins

A cross join is a Cartesian join, for sets of A and B rows, you'll get a 
result of A * B rows. A full join will match all possible rows, meaning it 
will return AT MOST A + B rows. For large values of A and B, the difference 
can be huge

27. Talk about Delete vs Truncate vs Drop

Delete - The DELETE command is used to remove rows from a table. A WHERE clause 
can be used to only remove some rows. If no WHERE condition is specified, all 
rows will be removed. After performing a DELETE operation you need to COMMIT or 
ROLLBACK the transaction to make the change permanent or to undo it. Note that 
this operation will cause all DELETE triggers on the table to fire.

Truncate - TRUNCATE removes all rows from a table. The operation cannot be 
rolled back and no triggers will be fired. As such, TRUCATE is faster and 
doesn't use as much undo space as a DELETE.

Drop - The DROP command removes a table from the database. All the tables' rows, 
indexes and privileges will also be removed. No DML triggers will be fired. The 
operation cannot be rolled back.

Drop - The DROP command removes a table from the database. All the tables' rows, 
indexes and privileges will also be removed. No DML triggers will be fired. The 
operation cannot be rolled back.

28. By doing which command you can roll back previous operation

Rollback

29. What is disadvantage of set nocount on

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for 
each statement in a stored procedure. For stored procedures that contain several 
statements that do not return much actual data, setting SET NOCOUNT to ON can 
provide a significant performance boost, because network traffic is greatly 
reduced.

30. What is disadvantage of having many Non-Clustered indexes on a table

They can slow down INSERT/UPDATE/DELETE performance

31. Write a query group the data by date

SELECT   TestDate,SUM(Sales) AS Sales
FROM     Test_TABLE
GROUP BY TestDate

32. How do you analyze SQL Query in text format

http://msdn.microsoft.com/en-us/library/ms173477.aspx

33. Have you worked with SQL query analyzer

Used in SQL Server 2000 and replaced by SQL Server Management Studio(SSMS) in 2008

34. If you have done a mistake in updating a table instead updating 2 rows, you have updated entire columns involves 500 rows what will you do, It’s all transactional data, How do you approach?

Rollback the data

35. What is Index- defragmentation

Physical/Internal fragmentation is caused when there is wasted space in the 
index caused by page splits, deletes, FILLFACTOR and PAD_INDEX. Logical/External 
fragmentation is when the pages that make up the leaf levels of the index are 
not in good order. This is usually caused by page splits. Both cause performance 
problems. Internal fragmentation causes problems because the indexes get bigger 
and bigger requiring more and more pages to store the data, which means that 
reads from the index slow things down. External fragmentation causes problems 
because when the data needs to be read from the index it has to skip all over 
the place following the links between pages, again, slowing things down.
When you look at the fragmentation percentage, you're looking at external 
fragmentation. It means the percentage of pages that are out of order showing 
an inefficient storage mechanism.

36. Can you name 4 new commands in Sql Server 2005 onwards

Pivot, Unpivot, Except,Intersect

37. How do you optimize store procedures

http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-
tips-best-practices/

38. What is ghost record?

Records that have been logically deleted but not physically deleted from the 
leaf level of an index.

39. What are aggregate commands

Aggregate functions perform the calculation on the data and will return a single 
value as the output.

40. what is left outer join, right outer join

Left Join – All Values from left table matching values from right query and if 
the right query doesn’t match they return nulls

Right Join – All Values from right table matching values from left query and if 
the left query doesn’t match they return nulls

41. Table variable where does it stores the data, Temp table where does it stores the data

Table Variable – memory

Temp Table – tempdb

42. What is a Unique key

Unique Key enforces uniqueness of the column on which they are defined. Unique 
Key creates a non-clustered index on the column. Unique Key allows only one NULL 
Value.

43. How will you trouble shoot deadlocks? Given 2 pages of data and statements to find deadlock  and how to troubleshoot it

http://blogs.msdn.com/b/bartd/archive/2006/09/09/747119.aspx

44. Best Practices for Better Database Performance

http://blog.sqlauthority.com/2008/04/06/sql-server-15-best-practices-for-better-
database-performance/

45. Difference between DDL and DML

Data definition language (DDL) statements are SQL statements that support the 
definition or declaration of database objects (for example, CREATE TABLE, DROP 
TABLE, and ALTER TABLE).
You can use the ADO Command object to issue DDL statements. To differentiate 
DDL statements from a table or stored procedure name, set the CommandType 
property of the Command object to adCmdText. Because executing DDL queries with 
this method does not generate any recordsets, there is no need for a Recordset 
object.

Data Manipulation Language (DML), which is used to select, insert, update, and 
delete data in the objects defined using DDL

46. What are statistics, under what circumstances they go out of date, and how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has 
unique values then the selectivity of that index is more, as opposed to an index 
with non-unique values. Query optimizer uses these indexes in determining whether 
to choose an index or not while executing a query.
 Some situations under which you should update statistics:
 1) If there is significant change in the key values in the index
 2) If a large amount of data in an indexed column has been added, changed, or 
removed (that is, if the distribution of key values has changed), or the table 
has been truncated using the TRUNCATE TABLE statement and then repopulated
 3) Database is upgraded from a previous version

47. How will you raise an error in sql?

 RAISERROR - Returns a user-defined error message and sets a system flag to 
record that an error has occurred. Using RAISERROR, the client can either 
retrieve an entry from the sysmessages table or build a message dynamically with 
user-specified severity and state information. After the message is defined it is 
sent back to the client as a server error message.

48. What is a transaction?

 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:

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

Consistency - When completed, a transaction must leave all data in a consistent 
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.

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.

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.

49. What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?

 Cursors allow row-by-row processing of the result sets.
 Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
 Disadvantages of cursors: Each time you fetch a row from the cursor, it 
results in a network roundtrip. 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.
 How to avoid cursor:
Most of the times, set based operations can be used instead of cursors. Here is 
an example: If you have to give a flat hike to your employees using the following 
criteria:
 Salary between 30000 and 40000 -- 5000 hike
 Salary between 40000 and 55000 -- 7000 hike
 Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's 
salary and update his salary according to the above formula. But the same can be 
achieved by multiple update statements or can be combined in a single UPDATE 
statement as shown below:

 UPDATE tbl_emp SET salary =
 CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
 END
You need to call a stored procedure when a column in a particular row meets 
certain condition. You don't have to use cursors for this. This can be achieved 
using WHILE loop, as long as there is a unique key to identify each row. For 
examples of using WHILE loop for row by row processing, check out the 'My code 
library' section of my site or search for WHILE.

50. What is a Heap?

A heap is a table without a clustered index. One or more nonclustered indexes
however can be created on tables stored as a heap. There is also no particular 
order for the way data is stored in a heap.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: