Advertisements

Archive

Archive for January, 2011

TSQL Interview Questions – Part 2

January 10, 2011 6 comments

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

Advertisements

2010 in review…

January 2, 2011 Leave a comment

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads This blog is on fire!.

Crunchy numbers

Featured image

A helper monkey made this abstract painting, inspired by your stats.

A Boeing 747-400 passenger jet can hold 416 passengers. This blog was viewed about 1,400 times in 2010. That’s about 3 full 747s.

 

In 2010, there were 25 new posts, growing the total archive of this blog to 37 posts. There were 3 pictures uploaded, taking up a total of 236kb.

The busiest day of the year was December 21st with 85 views. The most popular post that day was TSQL Interview Questions.

Where did they come from?

The top referring sites in 2010 were social.msdn.microsoft.com, en.wordpress.com, social.technet.microsoft.com, google.co.in, and google.com.

Some visitors came searching, mostly for sql server denali, manub22, sql server denali features, t sql interview questions, and smtp security settings sql 2008 from_address.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

TSQL Interview Questions December 2010
2 comments

2

SQL Server 11, codename Denali – New Features November 2010

3

Linked Server in MS SQL Server November 2010

4

Combine multiple ROWS to CSV String… and vice-versa September 2010
4 comments and 1 Like on WordPress.com,

5

Database Mail Setup – SQL Server 2005 September 2010
5 comments