Recursive CTEs continued …

In this post, I will finish the discussion of recursive CTEs that I began in my last post.  I will continue to use the CTE examples from Books Online.  To run these examples, you’ll need to install the Adventure Works Cycles OLTP sample database. In my last post, I explained that all recursive queries follow…

8

Recursive CTEs

One of the most important uses of CTEs is to write recursive queries.  In fact, CTEs provide the only means to write recursive queries.  As I noted last week, there are several excellent CTE examples, including recursive CTE examples, in Books Online.  I’ll once again start with the examples from Books Online.  To run these…

1

CTEs (Common Table Expressions)

CTEs or common table expressions, which are new in SQL Server 2005, provide an easy way to break a complex SQL statement down into smaller more manageable queries.  CTEs are is some ways very much like views.  Unlike a view which can be created once and used by many SQL statements, a CTE is associated…

5

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…

13

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…

11

Aggregation WITH ROLLUP

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 data that I used for my series of posts on the PIVOT operator.)…

11

Maintaining Unique Indexes

Consider the following schema: CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT)CREATE INDEX TA ON T(A)CREATE UNIQUE INDEX TB ON T(B) INSERT T VALUES (0, 0, 0)INSERT T VALUES (1, 1, 1) Now suppose we run the following update statement: UPDATE T SET A = 1 – A This update statement affects…

7

Optimized Non-clustered Index Maintenance in Per-Index Plans

In my last post, I showed how SQL Server 2005 only updates non-clustered indexes when the data in the index actually changes.  For my example, I used a simple update statement that results in a per-row or narrow plan.  In this post, I’ll show how this optimization works in a per-index or wide update plan….

5

Optimized Non-clustered Index Maintenance

Insert, update, and delete plans consist of two parts.  The first part or read cursor identifies the list of rows to be inserted, update, or deleted.  The second part or write cursor performs the actual insert, update, or delete.  Let’s look at a simple example: CREATE TABLE T (PK INT, A INT, B INT, C…

6

More on TOP

Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP.  This week I’ll take a look at some other interesting TOP scenarios.  In general, TOP is a fairly mundane operator.  It simply counts and returns the specified number of rows.  SQL Server 2005 does include two enhancements to TOP…

8