Sign in
Craig Freedman's SQL Server Blog
A discussion of query processing, query execution, and query plans in SQL Server.
Translate This Page
Translate this page
Powered by
Microsoft® Translator
Options
About
Email Blog Author
RSS for posts
Atom
RSS for comments
OK
Search
Tags
Aggregation
Common Table Expressions
Conversions
Hints
I/O
Isolation Levels
Joins
Parallelism
Partitioned Tables
Pivot and Unpivot
Ranking Functions
Rollup and Cube
Scans and Seeks
Subqueries
Top
Updates
Archive
Archives
January 2010
(1)
July 2009
(1)
June 2009
(1)
April 2009
(1)
March 2009
(2)
February 2009
(1)
October 2008
(2)
September 2008
(1)
August 2008
(2)
July 2008
(1)
June 2008
(2)
May 2008
(2)
April 2008
(1)
March 2008
(2)
February 2008
(1)
January 2008
(2)
November 2007
(1)
October 2007
(3)
September 2007
(3)
August 2007
(3)
July 2007
(4)
June 2007
(2)
May 2007
(5)
April 2007
(2)
December 2006
(1)
November 2006
(4)
October 2006
(4)
September 2006
(4)
August 2006
(5)
July 2006
(4)
June 2006
(6)
Posts
Subscribe via RSS
Sort by:
Most Recent
|
Most Views
|
Most Comments
Excerpt View
|
Full Post View
Craig Freedman's SQL Server Blog
GROUPING SETS in SQL Server 2008
Posted
over 6 years ago
by
Craig Freedman
10
Comments
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. ...
Craig Freedman's SQL Server Blog
Nested Loops Join
Posted
over 7 years ago
by
Craig Freedman
23
Comments
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...
Craig Freedman's SQL Server Blog
Hash Join
Posted
over 7 years ago
by
Craig Freedman
8
Comments
When it comes to physical join operators, hash join does the heavy lifting. While nested loops join works well with relatively small data sets and merge join helps with moderately sized data sets, hash join excels at performing the largest joins. Hash...
Craig Freedman's SQL Server Blog
Read Committed Isolation Level
Posted
over 6 years ago
by
Craig Freedman
13
Comments
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...
Craig Freedman's SQL Server Blog
Subqueries in CASE Expressions
Posted
over 7 years ago
by
Craig Freedman
5
Comments
In this post, I’m going to take a look at how SQL Server handles subqueries in CASE expressions. I’ll also introduce some more exotic join functionality in the process. Scalar expressions For simple CASE expressions with no subqueries, we can just...
Craig Freedman's SQL Server Blog
Bookmark Lookup
Posted
over 7 years ago
by
Craig Freedman
15
Comments
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...
Craig Freedman's SQL Server Blog
Partitioned Tables in SQL Server 2008
Posted
over 5 years ago
by
Craig Freedman
13
Comments
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...
Craig Freedman's SQL Server Blog
Introduction to Joins
Posted
over 7 years ago
by
Craig Freedman
8
Comments
Joins are one of the most important operations performed by a relational database system. An RDBMS uses joins to match rows from one table with rows from another table. For example, we can use joins to match sales with customers or books with authors...
Craig Freedman's SQL Server Blog
Aggregation WITH ROLLUP
Posted
over 6 years ago
by
Craig Freedman
6
Comments
In this post, I'm going to discuss how aggregation WITH ROLLUP works. The WITH ROLLUP clause permits us to execute multiple "levels" of aggregation in a single statement. For example, suppose we have the following fictitious sales data. (This is the same...
Craig Freedman's SQL Server Blog
Merge Join
Posted
over 7 years ago
by
Craig Freedman
14
Comments
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...
Craig Freedman's SQL Server Blog
Repeatable Read Isolation Level
Posted
over 6 years ago
by
Craig Freedman
21
Comments
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...
Craig Freedman's SQL Server Blog
Ranking Functions: ROW_NUMBER
Posted
over 5 years ago
by
Craig Freedman
5
Comments
SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are collectively referred to as ranking functions. These functions differ from ordinary scalar functions in that the result that they produce for a given row depends...
Craig Freedman's SQL Server Blog
Subqueries in BETWEEN and CASE Statements
Posted
over 5 years ago
by
Craig Freedman
8
Comments
Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT) CREATE TABLE T2 (A INT, B INT) SELECT * FROM T1 WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2 Observe that the subquery in this query only needs...
Craig Freedman's SQL Server Blog
Serializable vs. Snapshot Isolation Level
Posted
over 6 years ago
by
Craig Freedman
9
Comments
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...
Craig Freedman's SQL Server Blog
Aggregation WITH CUBE
Posted
over 6 years ago
by
Craig Freedman
6
Comments
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...
Craig Freedman's SQL Server Blog
What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?
Posted
over 5 years ago
by
Craig Freedman
6
Comments
SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats - that are extremely useful for monitoring which indexes are used as well as how and when they are used. Both DMVs report similar statistics on information...
Craig Freedman's SQL Server Blog
Ranking Functions: RANK, DENSE_RANK, and NTILE
Posted
over 5 years ago
by
Craig Freedman
3
Comments
In my previous post , I discussed the ROW_NUMBER ranking function which was introduced in SQL Server 2005. In this post, I'll take a look at the other ranking functions - RANK, DENSE_RANK, and NTILE. Let's begin with RANK and DENSE_RANK. These functions...
Craig Freedman's SQL Server Blog
Scans vs. Seeks
Posted
over 7 years ago
by
Craig Freedman
4
Comments
Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. These iterators are among the most fundamental ones that we support. They appear in nearly every query plan. What is the difference between a scan and a seek...
Craig Freedman's SQL Server Blog
The PIVOT Operator
Posted
over 6 years ago
by
Craig Freedman
10
Comments
In my next few posts, I'm going to look at how SQL Server implements the PIVOT and UNPIVOT operators. Let's begin with the PIVOT operator. The PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table...
Craig Freedman's SQL Server Blog
Introduction to Parallel Query Execution
Posted
over 7 years ago
by
Craig Freedman
5
Comments
SQL Server has the ability to execute queries using multiple CPUs simultaneously. We refer to this capability as parallel query execution. Parallel query execution can be used to reduce the response time of (i.e., speed up) a large query. It can also...
Craig Freedman's SQL Server Blog
Seek Predicates
Posted
over 7 years ago
by
Craig Freedman
11
Comments
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...
Craig Freedman's SQL Server Blog
Summary of Join Properties
Posted
over 7 years ago
by
Craig Freedman
7
Comments
The following table summarizes the characteristics of the three physical join operators which I described in my three prior posts. Nested Loops Join Merge Join Hash Join Best for … Relatively small inputs...
Craig Freedman's SQL Server Blog
Query Failure with Read Uncommitted
Posted
over 6 years ago
by
Craig Freedman
8
Comments
Over the past month or so, I've looked at pretty much every isolation level except for read uncommitted or nolock. Today I'm going to wrap up this series of posts with a discussion of read uncommitted. Plenty has already been written about the dangers...
Craig Freedman's SQL Server Blog
The UNPIVOT Operator
Posted
over 6 years ago
by
Craig Freedman
3
Comments
The UNPIVOT operator is the opposite of the PIVOT operator. As I explained in my earlier post , the PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table are derived from the values in the original...
Craig Freedman's SQL Server Blog
Partitioned Indexes in SQL Server 2008
Posted
over 5 years ago
by
Craig Freedman
2
Comments
In my last post , I looked at how SQL Server 2008 handles scans on partitioned tables. I explained that SQL Server 2008 treats partitioned tables as tables with a logical index on the partition id column and that SQL Server 2008 implements partition elimination...
Page 1 of 3 (74 items)
1
2
3