Kalen Delaney : indexes

Geek City: Join With Me!

Kalen Delaney — Thu, 14 Feb 2013 22:16:00 GMT

I remember one of the most surprising changes in SQL Server 2000 2005 was how the graphical plans showed the use of a nonclustered index to seek, and the plan included something that looked like a JOIN to find rows in the base table. Here’s an example....(read more)

Geek City: Build a Big Table with a Columnstore Index

Kalen Delaney — Thu, 26 Apr 2012 18:18:00 GMT

I was looking all over to find a big table to use for my columnstore examples in my SQL Server 2012 book, and the only one I found was from someone at Microsoft and was not publicly available. When I demonstrate code in my writings, I like to have the...(read more)

Geek City: What gets logged for index rebuild operations?

Kalen Delaney — Tue, 08 Mar 2011 18:54:00 GMT

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class in June asked for some information on a current problem he was having with transaction log writes causing excessive wait...(read more)

Geek City: Ordered Seeks and Scans

Kalen Delaney — Fri, 21 Jan 2011 23:28:00 GMT

I got a couple of really great questions during my SSWUG Workshop this morning , as I was discussing seeks and scans, and since the answers to the two questions are very related, I decided to address both of them in more detail in this blog post. Most...(read more)

Did You Know: My Online Seminar Lasted Almost 5 Hours!

Kalen Delaney — Sun, 25 Jul 2010 00:06:00 GMT

Yesterday was a rebroadcast of the index internals seminar I presented last February, and I had forgotten how long it was. I was in a chat room the whole time, and the questions just kept coming, fast and (not so) furious! Thanks to all the participants...(read more)

Geek City: Index Internals

Kalen Delaney — Mon, 19 Jul 2010 14:15:00 GMT

Once again, I'll be presenting an online seminar through SSWUG . This on is on Index Internals . The presentation lasts over 3 and a half hours, and I'll be online for live chat for over 4 hours. Ask away! We'll look at juicy details of exactly how clustered...(read more)

Did You Know? I'm doing 3 more online seminars with SSWUG!

Kalen Delaney — Fri, 11 Jun 2010 22:46:00 GMT

As I told you in April , I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops...(read more)

Geek City: More About Nonclustered Index Keys

Kalen Delaney — Mon, 08 Mar 2010 00:04:00 GMT

I thought I had said almost all that could be said about nonclustered index keys in a post made almost exactly two years ago , on March 16, 2008. But there's more. To get all the benefit from today's post, you'll really have to read that one, but I'll...(read more)

Did You Know? I just finished my first online seminar

Kalen Delaney — Fri, 12 Feb 2010 23:21:00 GMT

I am really jazzed! The seminar was over 3.5 hours in length, and we had 3 15-20 minute breaks, during which questions just kept coming in the chat room. I barely got out to refill my coffee! But there were some great questions and a really enthusiastic...(read more)

Geek City: Controlling Lock Granularity

Kalen Delaney — Sun, 03 May 2009 12:51:00 GMT

In all versions of SQL Server since 7.0, the engine could choose to lock rows, pages or the entire table. In addition, even if it starts out your query execution by obtaining row or page locks, if too many locks are acquired, SQL Server could escalate to a table lock. Each lock uses memory, so when escalation replaces the thousands of fine-grained locks with one table lock, there can be substantial resource savings. On the other hand, once a table is exclusively locked, no other processes can access any data at all from it, which drastically reduces concurrency.

Way back in SQL Server 6/6.5, we had some controls over what percentage of a table had to be locked in order to induce escalation. These controls were implemented as configuration options but they were removed in SQL 7. So what controls do we have now?

Lock escalation occurs in the following situations (taken from "SQL Server 2008 Internals", MS Press 2009):

The number of locks held by a single statement on one object, or on one partition of one object, exceeds a threshold. Currently that threshold is 5000 locks, but it might change in future service packs. The lock escalation will not occur if the locks are spread over multiple objects in the same statement—for example, 3000 locks in one index and 3000 in another.

Memory taken by lock resources exceeds 40 percent of the non-AWE (32-bit) or regular (64-bit) enabled memory and the locks configuration option is set to 0. (In this case, the lock memory is allocated dynamically as needed, so the 40 percent value is not a constant.) If the locks option is set to a non-zero value, memory reserved for locks is statically allocated when SQL Server starts. Escalation will occur when SQL Server is using more than 40 percent of the reserved lock memory for lock resources.

Most of the discussion of escalation that I have seen indicates that people want to prevent escalation, to maximize access to the data. One way to do this is to enable trace flag 1211 on your instance, which prevents lock escalation from occurring under any circumstances. You need to be really careful with this one, because it affects every table in every database on the instance. Another option is to trick SQL Server into thinking the table is in use, because if there is even one row locked by a different connection, escalation cannot occur. So you could add a dummy row to your table and start a process that starts a transaction, updates the dummy rows, and then keeps the transaction open. Although this can keep anyone from acquiring a table lock, this method has its own nasty side-effects. Namely, as long as the transaction is open, the log cannot truncated past that point. But if you can synchronize it with the operation updating the table, and make sure this dummy update gets closed as soon as the 'real' update finishes, you might be ok.

SQL Server 2008 provides us with a bit more control, with a new option to ALTER TABLE:

ALTER TABLE <table_name>
SET (LOCK_ESCALATION = [TABLE | AUTO |DISABLE]);

The default is escalation is TABLE, and that was the only possibility for escalation prior to SQL 2008. If you set the option to AUTO, locks can escalate to a table or to a partition, if the table is partitioned. The third option is to completely disable escalation for this table. This option is much more manageable that my trick of having another transaction lock a single row, and much more fine-grained that disallowing escalation on the entire instance.

But what if you want the opposite behavior? What if you always want to take table locks in order to conserve resources? Yes, there are lock hints available to request TABLOCK or TABLOCKX, but those must be specified in every query. What if there are only a few tables that you want to always lock at the table level.

SQL Server provides an option to ALTER INDEX to disallow ROW and PAGE locks. (In SQL 7 and 2000 you could use the system procedure sp_indexoption to do the same thing.) If the index is a clustered index, this means ROW and PAGE locks will be disallowed for the table. But, if the table is a heap, you can't use this option.

ALTER INDEX <index_name> ON <table_name>
SET (ALLOW_PAGE_LOCKS = OFF);

Also note that if you set ALLOW_PAGE_LOCKS to OFF, you cannot REORGANIZE (defragment) the index.

I usually mention this ALTER INDEX option in my Internals and Tuning class, and also mention that the only time I really had to use it was when clients turned off PAGE or ROW locks inappropriately and ended up with far too many unexplained table locks. Once you check sys.indexes (there are columns called allow_row_locks and allow_page_locks, with possible values of only 0 and 1), the behavior is no longer unexplained. (For versions prior to SQL Server 2005 you can use the INDEXPROPERTY function to check the status of these options.) It was several years ago that I last encountered someone using this option to turn off finer grained locks, and I was considering removing mention of it from my course.

And then, just last week, in my Oslo class, one of students had been asking about why her queries with CTEs ended up getting TABLE locks all the time. I didn't believe it had anything to do with the CTEs, but suggested she wait until we discussed locking, which we do on the fourth day of the class. She paid very close attention, and on Friday morning she came back and told me that the problem had not been the CTEs after all, but that 'someone' had turned off the ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS options!

So I guess I continue to mention this possibility in future classes.

~Kalen

Geek City: Too Many Indexes!

Kalen Delaney — Sun, 18 Jan 2009 20:37:00 GMT

I wrote about "Too Many Columns" last September, and along with changes in SQL Server 2008 that allow lots of columns, there is also the ability to create more than the old maximum of 249 nonclustered indexes on a table. I knew this fact, but somehow I overlooked it when updating the chapter on table structures in my new book. My omission also got past all the editors, and I just realized my mistake when reviewing the Indexes chapter, which was being updated for SQL 2008 by Kimberly Tripp.

I found that Books Online has not been completely updated (and I have filed a doc bug on this already). This page still says the limit is 249 nonclustered indexes per table:

http://msdn.microsoft.com/en-us/library/ms190197.aspx

However, the page for CREATE TABLE at http://msdn.microsoft.com/en-us/library/ms174979.aspx does say:

Each table can contain a maximum of 999 nonclustered indexes, and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

You can take my script to create a table with lots of columns, and edit it to create a UNIQUE constraint on every column.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 900;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int UNIQUE,';
    IF (@col % 3) = 1
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) UNIQUE,';
     IF (@col % 3) = 2
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) UNIQUE,';
SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create
EXECUTE (@create)

After the table is created, you can examine sys.indexes:

SELECT name, index_id, type_desc
FROM sys.indexes
WHERE object_id = object_id('wide900');

You'll definitely see more than 249 indexes! In addition, you might note that there is a gap in the index_id values. The values 251 - 255 are not used, and this is for backward compatibility. In much older versions, index_id (or indid) 255 was reserved for the LOB data belonging to a table, and the values from 251 - 254 were reserved. So that range is still not used. But you can see that I have index_id values from 256 - 905 in this table.

So, thanks Kim~

One more place that Books Online could use a little tweaking is in the section on Programmability Enhancements:

http://msdn.microsoft.com/en-us/library/cc645577.aspx

The section on "Wide Tables" seems to imply that you need to have an XML column set in order to get 1000 indexes. It doesn't actually say that, so it's not really a bug, but it does seem to imply it:

Wide tables are tables that contain one or more column sets. A wide table can contain up to 30000 columns, 1000 indexes, and 30000 statistics.

Because you can only reach the 30000 column limit with a wide table, it seems to imply that the 1000 index limit is only for wide tables. But as my script shows, it is not. You can have up to 1024 columns in a non-wide table, and you can now have indexes on every one of those columns.

But do you want that many? That's for another post, another time...

Have fun!

~Kalen

Geek City QUIZ: What happens when you change a column in an index from a key column to an included column?

Kalen Delaney — Mon, 24 Nov 2008 07:15:00 GMT

This quiz is not really a generic question about changing an index key column to be an included column; it's about a behavior noticed by a reader in one particular query. Dejan Nakarada-Kordic from New Zealand sent me a very interesting puzzle. He had a reproducible query for which he thought an existing nonclustered index should be used, and it only ended up being used if the column was defined as an INCLUDED column instead of a key column. Here is the script Dejan sent me:

-- First, create the table
IF EXISTS (SELECT * FROM sys.tables
            WHERE schema_id = 1 and name = 'Table1')
     DROP TABLE dbo.Table1;
GO
CREATE TABLE dbo.Table1(
    Transaction_Serial_No [bigint IDENTITY(1,1) NOT NULL,
    Channel] char(4) NOT NULL,
    Retry_Counter int NOT NULL,
CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED
       (Transaction_Serial_No));
GO

--populate the table with 10000 rows
SET NOCOUNT ON;
declare @counter int;
set @counter = 0;
while (@counter < 10000)
begin
    set @counter = @counter + 1;
    insert into dbo.Table1(channel, retry_counter)
    values('ch1', 0);
    insert into dbo.Table1(channel, retry_counter)
    values('ch2', 0);
end;

-- Now build a nonclustered index on
IF EXISTS (SELECT * FROM sys.indexes
            WHERE object_id = OBJECT_ID('dbo.Table1') AND name = 'IX_Channel')
    DROP INDEX IX_Channel ON dbo.Table1;
GO
CREATE NONCLUSTERED INDEX IX_Channel ON dbo.Table1 (Channel, Retry_Counter);
GO

-- Examine the query plan for this query:
SELECT TOP(20) Transaction_Serial_No
        FROM dbo.Table1
WHERE Channel = 'ch2'
         AND Retry_Counter <= 10
ORDER BY Transaction_Serial_No;

The plan should show you a Clustered Index Scan being performed, even though the nonclustered index appears to be a covering index.

The query contains all 3 columns used in the query. The columns Channel and Retry_Counter are defined index keys, and because the table has a clustered index, the clustered key Transaction_Serial_No is also part of the index.

If you rebuild the index to use Retry_Counter as an INCLUDED column, the plan changes.

CREATE NONCLUSTERED INDEX IX_Channel ON dbo.Table1 (Channel)
INCLUDE ( Retry_Counter) WITH (DROP_EXISTING = ON);

-- Examine the plan again:

SELECT TOP(20) Transaction_Serial_No
FROM dbo.Table1
WHERE Channel = 'ch2'
AND Retry_Counter <= 10
ORDER BY Transaction_Serial_No;

The plan now shows a nonclustered index seek:

WHY should changing the column from a key column to an INCLUDED column change the plan?

You can try to figure out the answer for yourself, or read on.

-------------------------------------------------------------------------------------------------------------------------

It turns out that there are two extra factors to consider.

1) Although covering indexes are a great thing, and the optimizer will choose them over other possible indexes most of the time, the cost of sorting can sometimes outweigh the benefit of a covering index.

2) When a column is an INCLUDED column in an index, it comes after all the key columns and after the clustered index key that is always part of a nonclustered index on a table that has a clustered index.

So, the original index on (Channel, Retry_Counter) is the same as an index on (Channel, Retry_Counter, Transaction_Serial_No), but moving Retry_Counter to be an INCLUDED column changes the index to be on the columns (Channel, Transaction_Serial_No), with Retry_Counter in last place, and unsorted.

The query wants the data sorted on Transaction_Serial_No, and scanning the clustered index gives us the data in Transaction_Serial_No order. The first nonclustered index is sorted first by Channel, then by Retry_Counter, then by Transaction_Serial_No so that index is not useful for the sort.

The second nonclustered index is sorted first by Channel, then by Transaction_Serial_No. BUT the query has limited the data to only data with the Channel value equal to 'ch2', so since the first column values we're retrieving are all the same, returning data in the nonclustered index order will return the data in Transaction_Serial_No order and no sorting needs to be done.

You might notice in this data set that all the values for Retry_Counter are also identical, so you might then think we should be able to use the first nonclustered index to avoid having to sort the data, but the optimizer cannot be sure that the Retry_Counter values are all identical. The statistics might indicate that all the values are the same, but the optimizer can't be sure the statistics are 100% up to date.

Here are some things to try:

1) If you remove the ORDER BY, you will see that the first index, with no INCLUDED column, is used.

2) If you change the second condition to searching for a constant (AND Retry_Counter = 0), the first index can be used. If both of the first two index columns are constants in the retrieved data, it will be sorted by the third column (Transaction_Serial_No).

3) If you force the query to use the nonclustered index when Retry_Counter is a key, you'll see that SQL Server has to do a sort:

DROP INDEX IX_Channel ON dbo.Table1;
GO
CREATE NONCLUSTERED INDEX IX_Channel ON dbo.Table1 (Channel, Retry_Counter);
GO
SELECT TOP(20) Transaction_Serial_No
        FROM dbo.Table1 WITH (index = ix_channel)
    WHERE Channel = 'ch2'
        AND Retry_Counter <= 10
ORDER BY Transaction_Serial_No;
GO

Notice that the SORT operation is estimated to be 95% of the total query cost, so SQL Server would definitely like to avoid that if possible.

So the issue wasn't really because of INCLUDED columns, it was because of the order the columns appeared in the index, and the fact that SQL Server wants to try to avoid sorting whenever possible.

Have fun!

~Kalen

Did You Know? Things Keep Changing

Kalen Delaney — Thu, 19 Jun 2008 19:07:00 GMT

My Thursday commentary for the SQL Server Magazine Update e-newsletter discussed quiet changes in SQL Server 2005 and I just realized there's another one I wanted to mention.

If you use Indexed Views at all, you're probably aware that there is a set of SET options that must on set appropriately in order for your indexed views to work as planned. One of the requirements is that ARITHABORT must be ON. In SQL Server 2000, this value had to be set explicitly. (But frequently it was set by your connection, so you didn't have to worry about it.)

In SQL 2005, if you (or your connection) enabled the setting ANSI_WARNINGS, ARITHABORT is automatically enabled.

I dug around and found the reference to this change in the BOL at
http://msdn.microsoft.com/en-us/library/ms190306.aspx

What the article doesn't make completely clear is that even if you explicitly set ARITHABORT to OFF, as long as ANSI_WARNINGS is ON, SQL Server will behave as if ARITHABORT is ON.

The SET options are particularly important when updating a table on which an indexed view is built. If the SET options have the incorrect settings, the update will actually fail.

Let's look at an example on SQL Server 2005, using the old pubs database.

-- First, create the view and the index

USE pubs
GO
CREATE VIEW sum_sales WITH SCHEMABINDING
AS
SELECT type, sum(isnull(ytd_sales,0)) AS total_sales,
count_big(*) AS number_sales
FROM dbo.titles
GROUP BY type;
GO
CREATE UNIQUE CLUSTERED INDEX idxv_sales_by_type
ON sum_sales(type);
GO

-- Next, verify the settings

SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

-- You should see that both options are ON (1).
-- Update the titles table, and it should succeed.
UPDATE dbo.titles
SET ytd_sales = ytd_sales + 1
WHERE title_id = 'BU1032';
--Now change ARITHABORT TO OFF, verify the settings, and update:
SET ARITHABORT OFF;
GO
SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
UPDATE dbo.titles
SET ytd_sales = ytd_sales - 1
WHERE title_id = 'BU1032';
-- The above should succeed.
-- Now change ANSI_WARNINGS to OFF:

SET ANSI_WARNINGS OFF;
GO
SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
UPDATE dbo.titles
SET ytd_sales = ytd_sales - 1
WHERE title_id = 'BU1032';

-- The above should fail.
-- If we change to SQL 2000 compatibility level,
-- just setting ARITHABORT OFF will cause the update to fail:
EXEC sp_dbcmptlevel pubs, 80;
GO
SET ANSI_WARNINGS ON;
GO
SET ARITHABORT OFF;
GO
SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
UPDATE dbo.titles
SET ytd_sales = ytd_sales + 1
WHERE title_id = 'BU1032';

I'm sure there are lots more quiet changes. They're just so quiet I haven't found them yet!

Have fun

~Kalen

Geek City: Clustered or Nonclustered? Why not both?

Kalen Delaney — Thu, 24 Apr 2008 21:51:00 GMT

I had been thinking of this post all day, and then noticed that Denis wrote a post with almost the same name. I was worried he might have written about something similar, but it turns out not to be the case.

A group of colleagues have been having a discussion about this topic recently, which was spurred by the fact that the Microsoft supplied Northwind database has duplicates of many of its indexes. If Microsoft does this, many new database users might assume it's a good idea. It's not! Having two identical indexes gives you no additional benefit, but does give you lots of additional overhead when those indexes need to be maintained. In SQL Server 2008, you will be able to create a policy to detect and prohibit this behavior if you choose. But that of course, is still in the future.

But what if the indexes are not quite identical? What if one is a clustered index and one is nonclustered? My colleagues report seeing this behavior frequently when someone declares a Primary Key on a column, and then doesn't realize that automatically builds an index (clustered by default), so she then builds a nonclustered on the same key. Is this necessarily all bad? The clustered index is useful when most of the columns of many of the rows need to be returned, based on the value in the PK column, or when the data needs to be returned sorted by the PK. But what if you only need a few rows, and you need only key columns? For example, what about a count(*) query?

In general, when satisfying a count(*) query, the SQL Server optimizer will choose the index with the smallest number of pages at the leaf level. A nonclustered index typically will have far fewer leaf level rows that a clustered, but still contains an entry for every single row, so the count(*) value will be accurate. So consider this example:

USE AdventureWorks;
-- create a big table by copying another one
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'newdetails')
DROP TABLE newdetails;
SELECT * INTO newdetails FROM Sales.SalesOrderDetail;

-- Build a PK and a NC index on the same column
ALTER TABLE newdetails
ADD CONSTRAINT PK_Detail PRIMARY KEY (SalesOrderDetailID);
CREATE UNIQUE INDEX UNQ_Detail ON newdetails (SalesOrderDetailID);

-- Look at the plan; the optimizer will choose the NC index
SET SHOWPLAN_TEXT ON;
SELECT count(*) FROM newdetails
WHERE SalesOrderDetailID BETWEEN 1000 and 2000;
SET SHOWPLAN_TEXT OFF;

-- Look at the performance; the nonclustered is performing better
-- Even though the NC index will be chosen without the hint,
-- i included it here to make it more obvious which index is chosen
SET STATISTICS IO ON;
SELECT count(*) FROM newdetails WITH (INDEX = UNQ_Detail)
WHERE SalesOrderDetailID BETWEEN 1000 and 2000
SELECT count(*) FROM newdetails WITH (INDEX = PK_Detail)
WHERE SalesOrderDetailID BETWEEN 1000 and 2000
SET STATISTICS IO OFF;

I am absolutely not recommending that you always duplicate your PK index with another one, but rather illustrating that it is not always completely pointless or harmful to do so. Choosing the best indexes takes a lot of careful consideration of your data distribution and your query usage patterns, and there is no one-size-fits-all answer.

I hope this is useful to you,

~Kalen

Geek City: Nonclustered Index Keys

Kalen Delaney — Mon, 17 Mar 2008 01:53:00 GMT

I recently received a question about the storage of nonclustered index keys. I am assuming you are aware of the fact that if your table has a clustered index, SQL Server uses the clustered index key (all of its columns, if it is a composite index) as a 'bookmark' in your nonclustered indexes, to allow your nonclustered indexes to uniquely identify the row that the index is pointing to.

In particular, this question was about a couple of statements in Inside SQL Server 2005: Query Tuning and Optimization, Chapter 3. Now, I didn't write Chapter 3, but I thought I knew all about how index keys were stored. But (you may want to be sitting down for this) I WAS WRONG.

Here are the statements:

“…when you create a nonunique nonclustered index on a table with a clustered index, we append the clustered index keys to the nonclustered index keys if they are not explicitly part of the nonclustered index keys”

And then a bit later on, there is another quote discussing this same effect:

“Note that the key columns for each of the nonclustered indexes on T_clu include the clustered index key column a with the exception of T_clu_f, which is a unique index.”

Although I had read this chapter when we were putting the book together, I must have read this last sentence too fast. The first quoted sentence is basically what I said in my lead-in paragraph. But the second sentence now seemed wrong. I thought it was saying that if your nonclustered index is UNIQUE, the clustered key columns are not stored in the index at all. I knew that wasn't true, so I contacted the author of Chapter 3. It turns out he was using a very specific definition of key.

KEY COLUMN: a column that is stored in the leaf pages and the interior (node, or non-leaf) pages of the B-tree and that you can use in an index seek.

He also uses another term in some of the related sections of the chapter:

COVERED COLUMN: a column that can be returned by the index without performing a bookmark lookup; it may or may not be a key column but it is stored in the leaf pages of the B-tree.

The definition of an index key includes the the property that it is stored at all levels of an index, not just the leaf level. Covered columns are in the leaf, so we have the potential of a covering index, and the possibility of retrieving the data we need without accessing the table itself. But not all covered columns are key columns.

This was news to me. I assumed that the fact that the clustered index key was in the nonclustered index meant that it was part of the key. And all my previous testing seemed to bear that out; I must never have tested at this level of detail with a unique nonclustered index. What this means is that I have been wrong in the following claim: "Explicitly declaring your clustered key columns as part of your nonclustered indexes does not make any difference in the storage of the index." That statement is true for nonunique indexes, but not true for unique.

What does this really mean?

Consider a TabA with a clustered index on col1. These two index definitions are identical:

CREATE INDEX nc_index ON TabA(col2)
CREATE INDEX nc_index ON TabA(col2, col1)

For a nonunique, nonclustered index (which the above index is), the clustered key col1 is automatically included at the leaf, and at all other levels, just as if we explicitly declared col1 to be part of the nonclustered index key.

However, these two index definitions are not exactly the same:

CREATE UNIQUE INDEX nc_index ON TabA(col2)
CREATE UNIQUE INDEX nc_index ON TabA(col2, col1)

The first index definition will create a nonclustered index on col2, and add col1 only at the leaf. The second index definition will also create an nonclustered index on col2, but it will add col1 as a true key column and it will be stored at all index levels.

Here's a specific example

Let's look at a specific example. I will be demonstrating the contents of the index rows using DBCC PAGE, and determine the index pages I am interested in by using DBCC IND. For more information about these two undocumented commands, one of the best places to look is at a couple of blog posts by Paul Randal:

http://www.sqlskills.com/blogs/paul/2007/10/01/InsideTheStorageEngineUsingDBCCPAGEAndDBCCINDToFindOutIfPageSplitsEverRollBack.aspx

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx

DBCC IND returns one row for every page of a table or index. The rows contain values indicating what type of page is listed (2 is an index page), what index the page belongs to, what level of an index the page is at (0 is the leaf), and then the file ID and page number. These last two values are then used in DBCC PAGE to get the contents of the page. The output also includes the file ID and page number for the next and previous pages in logical order. A page with no previous page is the first page in order.

When running DBCC IND on a large table, you can get lots of rows returned, and it can be tricky to find the exact rows you're interested in. For this reason, I usually take the output of DBCC IND and save it to a table, and then I can query the table. I can included the script to build a table called sp_index_info in the master database. Because of the sp_ prefix, this table can be accessed from any database.

I'm going to use a table that I copy from the AdventureWorks database into a test database. You can use any database you like to run this code on your own server.

USE testdb
GO
IF EXISTS (SELECT name FROM sys.tables WHERE name = 'Sales')
DROP TABLE Sales;
GO
SELECT * INTO Sales
FROM AdventureWorks.Sales.SalesOrderDetail;
GO

Now build a clustered index on SalesOrderID and three similar nonclustered indexes on SalesOrderDetailID, which is unique.

CREATE CLUSTERED INDEX Sales_ID_Index ON Sales(SalesOrderID);
GO
-- The first index is not declared as a unique index
CREATE INDEX Sales_DetailID_Index1 ON Sales(SalesOrderDetailID);
GO
-- The second index is declared as a unique index
CREATE UNIQUE INDEX Sales_DetailID_Index2 ON Sales(SalesOrderDetailID);
GO
-- The third index is declared as a unique index and explicitly includes the clustered index key
CREATE UNIQUE INDEX Sales_DetailID_Index3 ON Sales(SalesOrderDetailID, SalesOrderID);
GO

Now, populate the sp_index_info table with the results of DBCC IND

TRUNCATE TABLE sp_index_info
INSERT INTO sp_index_info
EXEC ('DBCC IND ( testdb, Sales, -1)' );
GO

Find the first leaf page from each index, by looking for pages with no previous page, and with an IndexLevel value of 0.

SELECT PageFID, PagePID, IndexID
FROM sp_index_info
WHERE PageType = 2 AND IndexLevel = 0
AND PrevPagePID = 0
ORDER BY IndexID;
GO

Here's my output:

PageFID PagePID     IndexID
------- ----------- -------
1       3416        2
1       1248        3
1       1680        4

To look at the actual rows, we need to use DBCC PAGE. An option value of 3 is very useful for index pages, as it gives us tabular output showing each individual index row. I'll take each of the file and page numbers and use them in DBCC PAGE, and of course you'll have to substitute whatever page numbers you get.

DBCC TRACEON (3604);
DBCC PAGE(testdb, 1, 3416, 3);
DBCC PAGE(testdb, 1, 1248, 3);
DBCC PAGE(testdb, 1, 1680, 3);

Here are my results:

The results shown are for the leaf level. Note that because my clustered index is not unique, it includes a uniqueifier for every row. The uniqueifier is never visible using SELECT statements, but is a hidden column stored within the row to make sure each row in the clustered index is unique. This uniqueifier column is always considered part of the clustered key internally, so every nonunique clustered index is really a composite index.

There is the exact same data in the leaf for all three nonclustered indexes (except for page numbers of course; all three indexes have their own pages). The same index keys are in each row of the first leaf level index page. However, you might note a couple of differences int the column headers. Just as the nonunique clustered index includes a uniqueifier, SQL Server has to have some way to make sure each nonclustered index row is unique. The column headers actually index which columns of my nonclustered indexes make up the key. For the nonunique index, SQL Server has to consider the entire clustered key as part of the nonclustered key. It knows the clustered key is unique, so adding it to the nonclustered guarantees uniqueness. You can see that all 3 columns are labeled as (key): SalesOrderDetailID, SalesOrderID (the clustered key) and the clustered index uniqueifier.

For the second index, which was declared as unique, but did not explicitly include the clustered key, the only key the nonclustered index needs is the key the index was built on. Because that single key was declared as unique, no further information is needed to be part of the key. However, the clustered key stored in the leaf level rows, because SQL Server needs that to be able to find the row in the table data.

The third index explicitly declared the clustered key to be part of the nonclustered index key. So the column labels indicate that both the column SalesOrderDetailID and SalesOrderID are key columns. Since the index was declared to be unique, only the explicitly declared key columns are part of the key. The uniqueifier is there, because as part of the clustered key is must be in the leaf level pages, but it is not marked as part of the nonclustered key.

So how are these indexes different?

The indexes differ in the upper levels. The table sp_index_info still contains the output of DBCC IND, so we can find an upper level page for each index.

At the level above the leaf the IndexLevel is 1. I don't want to see the rows for the upper level pages of the clustered index, so I filter them out.

SELECT PageFID, PagePID, IndexID
FROM sp_index_info
WHERE PageType = 2 AND IndexLevel > 0 and IndexID > 1
AND PrevPagePID = 0
ORDER BY IndexID;

Here are my results:

PageFID PagePID     IndexID
------- ----------- -------
1       3520        2
1       1360        3
1       1744        4

And then I use those results to look at the pages:

DBCC TRACEON (3604);
DBCC PAGE(testdb, 1, 3520, 3);
DBCC PAGE(testdb, 1, 1360, 3);
DBCC PAGE(testdb, 1, 1744, 3);

Here are my results:

Now you should see something different. The first index, nonunique, still has all three columns in the upper level page: the nonclustered key SalesOrderDetailID, and the two columns of the clustered key: SalesOrderID and the uniqueifier.

The second index is the unique nonclustered on a single column, and only has SalesOrderDetailID in the upper level page.

The third index is unique and composite, so both of the declared keys are in the upper level.

What other possibilities are there?

One additional question you might have is what happens if the clustered index is also unique. How do the nonclustered index rows look different? I think with the tools and examples presented here, you should be able to figure that out for yourself!

Have fun!

~Kalen

http://DVD.KalenDelaney.com

www.SQLCommunity.com

------------------------------Script to create a table to hold DBCC IND output--------------------------------------------------------

-- Create a table to hold the output of DBCC IND
USE master
GO
IF EXISTS (SELECT name FROM sys.tables
WHERE name = 'sp_index_info')
DROP TABLE sp_index_info;

GO
CREATE TABLE sp_index_info
(PageFID tinyint,
PagePID int,
IAMFID   tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
Primary Key (PageFID, PagePID));