BT

31 Days of SQL Server Interview Questions and Answers

by Jonathan Allen on Aug 05, 2011 |

Pinal Dave has posted a 31-part series titled SQL Server – Interview Questions and Answers. It starts with general topics such as “What is Normalization?” and works its way to obscure topics such as “What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?”. Data warehousing is covered and there are even guest posts on SQL Azure.

To tempt you into reading his series we are quoting some of the material that you’ll learn. For example, day 7 includes coverage of the different types of locks.

  • Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
  • Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
  • Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time.
  • Intent Locks: Used to establish a lock hierarchy. The types of intent locks are as follows: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
  • Schema Locks: Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).
  • Bulk Update Locks: Used when bulk-copying data into a table and the TABLOCK hint is specified.

There are also plenty of tips for working around the limitations of T-SQL such as how reading @@Rowcount or @@Recordcount clears the value stored in the other.

If @@Rowcount is checked after Error checking statement, then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement, then @@Error would get reset. To get @@error and @@rowcount at the same time, include both in same statement and store them in a local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Even experienced developers may find this series useful when moving from the world of OLTP and normalized tables to the Dimensional Modeling needed for OLAP style servers.

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2014 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT