TSQL Interview Questions – Part 2
Next part of TSQL Interview Questions, contd. from my previous post.
26. What are the virtual tables in Triggers?
Inserted & Deleted
27. What is benefit of a having stored-procedure?
28. Can stored-procedures be recursive? And upto how much level?
Yes, 32 levels.
29. How you can load large data in SQL Server?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format, check here: https://sqlwithmanoj.com/2011/09/09/bcp-in-out-queryout-the-bcp-util/
30. What is the trade-offs of a BCP command, when various users are loading data in a particular table at same time?
31. How can you copy schema from one SQL Server to another?
DTS, import/export wizard.
Scripting out Database objects.
33. What is a table called that has ## before its name, what is its scope?
Table with ## (double pound signs) is called Global Temp table. Scope is outside the session but only till the original session lasts.
34. What is the scope of a temporary table?
Scope is limited to its session only.
35. What is Mutex error in Triggers?
MSDN link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c86c97a7-73ea-482e-9529-e2407bd7018c
36. What is the use of WITH (nolock)?
37. What are the various Isolation levels?
a) Read Uncommitted Isolation Level
b) Read Committed Isolation Level
c) Repeatable Read Isolation Level
d) Serializable Isolation Level
e) Snapshot Isolation Level
f) Read Committed Snapshot Isolation Level
38. What are implicit & explicit cursors?
39. Define the life cycle of a Cursor.
40. How would you know that if a cursor is open or closed?
declare @mycursor cursor declare @FirstName varchar(12) select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) set @mycursor = cursor for select FirstName from Person.Contact select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed) open @mycursor select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row) fetch next from @mycursor into @FirstName select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row) close @mycursor select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed) deallocate @mycursor select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) select CURSOR_STATUS('variable','@nocursor') --// -3 (A cursor with the specified name does not exist)
41. How many non-clustered indexes can you have in a table?
Upto 999 non-clustered indexes can be created in a table in SQL Server 2012/2014.
42. What all indexes can you have in a table?
One Clustered Index, one or more than one non-clustered index, unique index, filtered, spatial, xml, etc.
MS BOL: http://msdn.microsoft.com/en-us/library/ms175049.aspx
43. How will you know what indexes a particular table is using?
1. By checking the graphical Execution Plan.
2. By using sys.dm_db_index_usage_stats DMV, link: https://sqlwithmanoj.com/2015/04/02/index-usage-stats-indexes-used-unused-and-updated/
44. What is the benefit of cross joins? How would you use a where clause with Cross Joins?
The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
45. Difference between VARCHAR & VARCHAR2?
VARCHAR2 is specific to Oracle. MS SQL Server has VARCHAR & VARCHAR(MAX) data types.
46. What is de-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8c54d1d2-5fbd-4b62-a07f-16c34a863668
47. How would you get @@ERROR & @@ROWCOUNT at the same time?
Both the statements should be applied immediately after the SQL query you want to get the details of. If either of them is used later you will miss the information. So to get @@ERROR and @@ROWCOUNT details you need to execute them together in one statement like:
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
48. What is Collation?
49. Types of Replication? Difference between Merge & Transactional Replication.
MS BOL link: http://msdn.microsoft.com/en-us/library/ms165713(v=sql.90).aspx
50. What can you do with COALESCE function?
COALESCE() and difference with ISNULL(): https://sqlwithmanoj.com/2010/12/23/isnull-vs-coalesce/
… more questions on next post [Part-3].