Kalen Delaney : included columns

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

Geek City: Included Columns

Kalen Delaney — Mon, 31 Dec 2007 19:53:00 GMT

When an issue comes up more than once in a short period of time, I figure it is a message from the Universe. I might even assume it is a gift of blog fodder. So I won't turn down the gift, even if it means another juicy post before the end of year, much to Denis' disappointment.

The issue is SQL Server 2005 "Included Columns". There was a post on the public newsgroups about them a couple of days ago, and just yesterday, there was a similar post on the SQL Server MVP private newsgroup. Simply put, the questions were expressing concern that there were no built-in tools in SQL Server 2005 to list which columns were included columns. The information is of course available in the metadata views, but it can require a join of at least three of them to get a simple list of which columns are "included columns" in a given index. The poster on the MVP newsgroup wanted to know if someone had already written such a query and could share it, to save him some work.

There actually was a third post about included columns that I came across, that was asking about the reasons for using included columns. I will address that question first, and then provide some scripts to return information about included columns.

You need to be aware of two 'features' of indexes to fully appreciate included columns.

First: all indexes have a limit of no more than 16 key columns, with a combined total of no more than 900 bytes.

Second: a 'covering' index, which is a nonclustered index that contains all the columns referenced in a query from one table, can provide an incredible performance advantage. If all the information a query needs is contained in the index keys, SQL Server will never need to actually access the table data, and not having to do this table lookup can be a very good thing. (Disclaimer: covering indexes are not the solution to ALL query performance problems, and I don't have time today to provide a full discussion of covering indexes.)

SQL Server 2005 allows you to get around the 16 column and 900 byte limit and add additional column to a nonclustered index to provide greater opportunity for covering indexes. The syntax would look something like this:

CREATE INDEX bigindex on mybigrowtable(keycolumn) INCLUDE (bigcolumn1, bigcolumn2)

If all the columns you would like to have in your index fit within the limits, there is no technical reason for having included columns; all your columns could just be regular key columns. However, there are some other issues to consider.

1) Space requirements: Normal key columns, as part of the key, are propagated up through all levels of your indexes. So if you have some very large columns, even if they fit in the 900 byte limit, you can save space by defining them as included columns. Here is an example, using the AdventureWorks database:

USE AdventureWorks
-- set the database to bulk_logged recovery
-- prior to copying tables
ALTER DATABASE AdventureWorks
    SET RECOVERY bulk_logged
GO
---------------------
IF ( OBJECT_ID('Person.Address1') is not null)
    DROP TABLE Person.Address1
GO

IF ( OBJECT_ID('Person.Address2') is not null)
    DROP TABLE Person.Address2
--------------------------------------
-- Create two copies of the Person.Address table and enlarge one of the columns
SELECT * INTO Person.Address1
    FROM Person.Address
GO
ALTER TABLE Person.Address1
    ALTER COLUMN AddressLine2 nchar(350)
GO
-- Create an index with two included columns
CREATE INDEX IX_Address_City
    on [Person].[Address1] (City, StateProvinceID)
    INCLUDE(AddressLine1, AddressLine2)
GO
-------------------
SELECT * INTO Person.Address2
    FROM Person.Address
GO
ALTER TABLE Person.Address2
    ALTER COLUMN AddressLine2 nchar(350)
GO
-- Create a 'regular' index with no included columns;
-- All four columns are keys, and included at all index levels
CREATE INDEX IX_Address_City
    on [Person].[Address2]
        (City, StateProvinceID, AddressLine1, AddressLine2)
GO

-- Note that the Address2 index uses about 1.5 MB
-- more than the index on Address1, because it contains
-- all 4 keys in all levels of the index.
EXEC sp_spaceused 'Person.Address1'
EXEC sp_spaceused 'Person.Address2'
GO

2) Maintenance costs: Normal key columns are maintained in sorted order. An index on (lastname, state, city) would have 3 sort columns and every new row would have to be put in the proper location based on all 3 columns. But an index on lastname, with (state, city) as included columns, would not have to maintain sort order on state and city and that could improve the performance of data modification operations. I haven't done any tests yet to determine how much savings you might realize, but it will be faster with included columns.

The downside of included columns not being sorted, is that those columns will not be useful for searching if those columns are in the WHERE clause, in particular if those columns are using in an inequality expression.

So now, how can you retrieve information about which columns are included and which are key columns? Sp_helpindex does not display this information, in fact, it makes no mention of included columns. The information is available in a catalog view called sys.index_columns, in a column called 'is_included_column'. The code below creates a view that you can use to return information about all the columns in your indexes.

CREATE VIEW index_column_info
AS
SELECT object_name = object_name(ic.object_id),
                  index_name = i.name,
                  'column' = c.name,
                  'column usage' = CASE ic.is_included_column
                               WHEN 0 then 'KEY'
                               ELSE 'INCLUDED'
                   END
   FROM sys.index_columns ic JOIN sys.columns c
              ON ic.object_id = c.object_id
              AND ic.column_id = c.column_id
       JOIN sys.indexes i
              ON i.object_id = ic.object_id
              AND i.index_id = ic.index_id

You can select from this view and supply a table name and/or an index name or id in a WHERE clause:

SELECT * FROM index_column_info
WHERE object_name = 'Address1'

A student in one of my classes earlier this year wanted a way to get input back that looked like the sp_helpindex output. I am attaching a script to build a stored procedure called new_helpindex which will do that. However, the new procedure is used a bit differently than sp_helpindex. sp_helpindex requires a schema name and new_helpindex does not. So to get information about my new Address1 table created above, the two procedures would be called as shown:

EXEC new_helpindex 'address1'
EXEC sp_helpindex 'person.address1'

If you want the new_helpindex procedure to accept schema names, and to return exactly the same details as sp_helpindex (plus the included columns), it is certainly possible, and I leave that as an exercise to my readers.

Have fun!

~Kalen