SQL Server Interview Questions and Answers

1) Which data type not used in SQL Azure?

Ans: user-defined CLR data types, these are not supported in Windows Azure SQL Database

2)Differences Between Truncate and Delete ?

Ans:   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 removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

      DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

3)ACID properties?

 Ans:

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

4) Explain one of the query optimization in u r project? 

Ans: Your  Explanation

5) Types of locks in sql?

Ans:  

* Shared (S) 
* Update (U) 
* Exclusive (X) 
* Intent 
* Schema 
* Bulk Update (BU) 
* Key-range

For more see below link 

http://www.dotnetfunda.com/forums/show/4658/what-are-the-different-types-of-locks-available-in-sql-server  

 For Examples see below link

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server 

6)What type of transactions used in your Project?

Ans: You can explain your self

Usually Begin Tran
              Commit Tran
              RollBack Tran
We must to say the specific location and situation in the project

7)Write a query for a Factorial?  

 declare @n int,@r int

set @r=1

set @n = 4;

WITH CTE (N) AS(

SELECT CASE WHEN @N<0 THEN NULL ELSE 1 END N

UNION ALL

SELECT (N+1)

FROM CTE

WHERE N < @n

)

SELECT @r = @r*n from cte

print @r

8) Difference Between Index Scan and Index Seek

Ans:

An index scan is a complete scan of all the pages in a non-clustered index.

A clustered index scan is a complete scan of all pages in a clustered index (i.e, the table itself)

Neither scan uses the b-tree structure of the index, but just reads the leaf pages in order, using each page's reference to the next in the chain.An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf. An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf. 

9)Is there any difference between table scan and index scan?  Ans:A table scan (only present when you don't have a clustered index) is a scan of the data pages of the table. An index scan reads the leaf pages of the index. If it's a nonclustered index, it'll be a lot fewer pages than the table. A clustered index scan (only present if there's a clustered index) reads the leaf pages of the clustered index, which are the data pages of the table. It's virtually the same same as a table scan. There are some minor differences as to how SQL does the scan, but it is the entire table, like with a table scan.  Full table scan has linear complexity, index seek has logarithmic complexity. 10. About the current project/ experience ? 11. What is index scan and index seek? What is the difference? Ans: Explained above 12. Merge Operation in sql server. How it internally works? Ans: MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted. MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ... 13. What is Column store indexes, its usage?  --(new feature in sql 2012). Ans: https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/ The columnstore index in SQL Server 2012 stores columns instead of rows, and is designed to speed up analytical processing and data-warehouse queries. Whilst columnstore indexes certainly do that effectively, they are not a universal panacea since there are a number of limitations on them. When used appropriately, they can reduce disk I/O and use memory more efficiently.     14. Lookup and merge in SSIS ? 15. Rollup(tabular) and cube in sql server ? Ans:  ROLLUP ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query. Syntax  ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is: SELECT ... GROUP BY ROLLUP(grouping_column_reference_list) The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause. Difference b/w CUBE and ROLLUP: - CUBE generates a result set that shows aggregates for all combinations of values in the selected columns. - ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns. 16. How to tune a sql procedure. Steps that are involved ? Ans: 1) Use SET NOCOUNT ON statement.While we execute the statement it counts the number of records 2)Use the DB name and schema name along with the table name 3) Use "usp_"instead of "sp_", Because while executing the stored procedure  if it initiated with "sp_" then the records fetching from system stored procedure. Here the system SPs initiated "with sp_" 4) Write the select statements using with(NOLOCK) 5)Try to avoid the count(*), prefer count(1) 6) Use the UNION ALL instead of UNION   7)Use the temp tables(#tables) instead of Cursor  8)Keep the transaction as short as possible 9) Rewrite sub queries using joins 10)Use the parameterized queries  11)Use indexes with filtered clause 12)Create multiple column indexes 13)Avoid indexes on small tables  14) Always avoid the use of substring function in Query 15)Use WITH READ UNCOMMITTED isolation 16)Avoid expensive operations such as NOT LIKE 17) The operators shown below are in their decreasing order of their performance.         =         >,>=,<, <=        LIKE       <>       i.e., better to go for '=' rather than 'LIKE' or '<>' 18)Use DMVs and DMFs to know the reads and writes First, we need to understand what a Read really is. Here is a quote from a Microsoft white paper about I/O architecture that clearly defines logical and physical reads: "The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory." 17. Preparing reports from multiple datasources ? Ans: Using LOOKUP 18.Performance tuning and scalability 19. Isolations Ans:Refer Q-29 20. Pivoting 21.What is Page? Ans: The main disk allocation unit of database engines is called a page. The size of a page is typically some kilobytes. A page usually contains between dozens and hundreds of records. This is important to remember: sometimes you may think a query is optimal from the point of view of the record accesses, while it is not if you look at page accesses. 22.What is Execution plan? Ans: The Query Execution Plans describe the steps and the order used to access or modify data in the database.  Once you have this information you can identify what parts of the query are slow. SQL Server can create execution plans in two ways: Actual Execution Plan - (CTRL + M) - is created after execution of the query and contains the steps that were performed Estimated Execution Plan - (CTRL + L) - is created without executing the query and contains an approximate execution plan 23. Clustered and Non clustered Index? 24. What is SQL Profiler? Ans: Sql profiler is a tool(GUI) used to monitor the traced T-Sql queries which are running slowly and takes more time to run.The Events Selection gives us more options to choose choice of content in Database Engine may off line or online.We can cave the trace file and analyze later secession. 25. Write a query to retrieve the customers who has not placed any order? 26. What is Dirty Read and Phantom Reads ? Ans: A dirty read takes no notice of any lock taken by another process. The read is officially “dirty” when it reads data that is uncommitted. This can become problematic if the uncommitted transaction fails or for some other reason is rolled back. Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different. 27. What is Commit size in SSIS? Ans:  Rows per batch – The default value for this setting is -1 which specifies all incoming rows will be treated as a single batch. You can change this default behavior and break all incoming rows into multiple batches. The allowed value is only positive integer which specifies the maximum number of rows in a batch. Maximum insert commit size – The default value for this setting is '2147483647' (largest value for 4 byte integer type) which specifies all incoming rows will be committed once on successful completion. 28. What is Transaction? Ans; A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased. 29. Isolation Levels in SQL ? Ans:Isolation levels in SQL Server control the way locking works between transactions. SQL Server 2008 supports the following isolation levels Read Uncommitted Read Committed (The default) Repeatable Read Serializable Snapshot For Examples plz go through http://gavindraper.com/2012/02/18/sql-server-isolation-levels-by-example/ 30. What is Update Statistics? Ans: Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics. 31. What is RAISE ERROR? & Throw? Ans: RaiseError is a system defined error. We can assign this message to a variable in catch block and display this to user.Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROWstatement has just began with Sql Server 2012.  32. Response time and Total time Ans: Response time is the time it takes for a query to return the first record.  Total time is the time it takes for the query to return all records.

33. How could you know how many files processed in your package up to today?