Nested Loops Join

SQL Server supports three physical join operators: nested loops join, merge join, and hash join.  In this post, I’ll describe nested loops join (or NL join for short). The basic algorithm In its simplest form, a nested loops join compares each row from one table (known as the outer table) to each row from the…


Repeatable Read Isolation Level

In my last two posts, I showed how queries running at read committed isolation level may generate unexpected results in the presence of concurrent updates.  Many but not all of these results can be avoided by running at repeatable read isolation level.  In this post, I’ll explore how concurrent updates may affect queries running at…


Serializable vs. Snapshot Isolation Level

Both the serializable and snapshot isolation levels provide a read consistent view of the database to all transactions.  In either of these isolation levels, a transaction can only read data that has been committed.  Moreover, a transaction can read the same data multiple times without ever observing any concurrent transactions making changes to this data. …


Read Committed Isolation Level

SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read committed, repeatable read, and serializable.  SQL Server 2005 adds two new isolation levels: read committed snapshot and snapshot.  These isolation levels determine what locks SQL Server takes when accessing data and, therefore, by extension they determine the level of concurrency and consistency…


Bookmark Lookup

In my last post, I explained how SQL Server can use an index to efficiently locate rows that qualify for a predicate.  When deciding whether to use an index, SQL Server considers several factors.  These factors include checking whether the index covers all of the columns that the query references (for the table in question)….


Merge Join

In this post, I’ll describe the second physical join operator: merge join (MJ).  Unlike the nested loops join which supports any join predicate, the merge join requires at least one equijoin predicate.  Moreover, the inputs to the merge join must be sorted on the join keys.  For example, if we have a join predicate “T1.a…


Partitioned Tables in SQL Server 2008

In this post, I introduced how SQL Server 2005 implements query plans on partitioned tables.  If you’ve read that post or used partitioned tables, you may recall that SQL Server 2005 uses a constant scan operator to enumerate the list of partition ids that need to be scanned.  As a refresher, here is the example…


GROUPING SETS in SQL Server 2008

In my last two posts, I gave examples of aggregation WITH ROLLUP and CUBE.  SQL Server 2008 continues to support this syntax, but also introduces new more powerful ANSI SQL 2006 compliant syntax.  In this post, I’ll give an overview of the changes. First, let’s see how we rewrite simple WITH ROLLUP and CUBE queries…


Seek Predicates

Before SQL Server can perform an index seek, it must determine whether the keys of the index are suitable for evaluating a predicate in the query. Single column indexes Single column indexes are fairly straightforward.  SQL Server can use single column indexes to answer most simple comparisons including equality and inequality (greater than, less than,…


Aggregation WITH CUBE

In my last post, I wrote about how aggregation WITH ROLLUP works.  In this post, I will discuss how aggregation WITH CUBE works.  Like the WITH ROLLUP clause, the WITH CUBE clause permits us to compute multiple “levels” of aggregation in a single statement.  To understand the difference between these two clauses, let’s look at an…