SQL with Manoj

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.

32. What is the benefit of a Temporary Table, how would you define it?
Benefits of a Temp #Table: http://wp.me/p12rgl-r | YouTube

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)?
https://sqlwithmanoj.com/2013/10/04/difference-between-nolock-and-readpast-table-hints/
MSDN: http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx?wa=wsignin1.0

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
http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

38. What are implicit & explicit cursors?
http://geekexplains.blogspot.com/2008/11/implicit-vs-explicit-cursors-static-vs.html

39. Define the life cycle of a Cursor.
https://sqlwithmanoj.wordpress.com/2010/10/24/sql-server-cursor-life-cycle/

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.
http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx
http://www.dotnetspider.com/forum/44591-why-we-use-cross-join-sqlserver.aspx

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
Normalization: https://sqlwithmanoj.com/2009/03/25/database-normalization/

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?
https://sqlwithmanoj.com/2010/02/23/collation-conflict-in-sql-server/
https://sqlwithmanoj.com/2010/03/25/change-a-database-collation/
MSDN: http://msdn.microsoft.com/en-us/library/aa174903(v=sql.80).aspx

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].