Dynamic Partition Elimination Performance

In this post on partitioned tables, I mentioned that SQL Server 2008 has a much more efficient implementation of dynamic partition elimination as compared to SQL Server 2005.  In response, a reader posted this comment asking how much dynamic partition elimination really costs in SQL Server 2005.  While I was sure that the answer is…

3

Partitioned Indexes in SQL Server 2008

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 by performing a logical index seek on the partition id column. …

2

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…

13

Subqueries in BETWEEN and CASE Statements

Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT)CREATE TABLE T2 (A INT, B INT) SELECT *FROM T1WHERE (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 to be evaluated once for each row of T1.  Indeed running on…

8

Implicit Conversions

In my last couple of posts, I wrote about how explicit conversions can lead to errors.  In this post, I’m going to take a look at some issues involving implicit conversions.  SQL Server adds implicit conversions whenever you mix columns, variables, and/or parameters with different (but compatible) data types in a single expression.  For example,…

5

Query Processing Presentation

Last week, I had the opportunity to talk to the New England SQL Server Users Group.  I would like to thank the group for inviting me, Adam Machanic for organizing the event, and Red Gate for sponsoring it.  My talk was an introduction to query processing, query execution, and query plans in SQL Server.  I’ve…

2

Conversion and Arithmetic Errors: Change between SQL Server 2000 and 2005

In this post from last week, I gave an example of a query with a conversion where the optimizer pushes the conversion below a join.  The result is that the conversion may be run on rows that do not join which could lead to avoidable failures.  I ran this query on SQL Server 2005.  After…

2

Conversion and Arithmetic Errors

Let’s take a look at a simple query: CREATE TABLE T1 (A INT, B CHAR(8))INSERT T1 VALUES (0, ‘0’)INSERT T1 VALUES (1, ‘1’)INSERT T1 VALUES (99, ‘Error’) SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1 There is no way to convert the string “Error” into an integer, so it should come as no surprise that…

4

Ranking Functions: RANK, DENSE_RANK, and NTILE

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 are similar – both in functionality and implementation – to ROW_NUMBER.  The difference…

4

Ranking Functions: ROW_NUMBER

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 on the other rows in the result set.  They also differ from aggregate functions in that…

5