- How will you to performance tuning ?
- What will you look for in an execution plan ?
- What is bookmark look-up related to execution plan
- Table variables vs temp tables
- 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
- 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.
- 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.
- 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.
- 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.
- 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
- 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.
- 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.
- How do we do error handling in stored procedures?
- 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.
- Talk about delete vs truncate
- 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.
- 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
- How will get the second greatest value in a table.
SELECT max(score ) FROM scores WHERE score not in (SELECT max(score) FROM scores)
- 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?
21.How do you monitor Sql Server?
22.Did you ever use 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
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
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
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
44. 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.