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.