March 5, 2015 at 1:20 pm
Hi All,
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Any thoughts are appreciated.
Thanks,
Peter
March 5, 2015 at 2:31 pm
It appears as though the clustered keys get added to the non-clustered index in the same order as the clustered key. here is the example I used to check this.
first we create our test table and indexes.
CREATE TABLE [indexorder]([datekey] [int] NOT NULL,
[locationkey] [int] NOT NULL,
[itemkey] [int] NOT NULL,
[col1] varchar(10));
ALTER TABLE [indexorder]
ADD CONSTRAINT [PK_index] PRIMARY KEY([datekey], [locationkey], [itemkey]);
INSERT INTO [indexorder]
VALUES(1,1,1,'one'),(2,2,2,'two');
CREATE INDEX [IX_test] ON [indexorder]([Locationkey]);
CREATE INDEX [IX_test2] ON [indexorder]([itemkey]);
CREATE INDEX [IX_test3] ON [indexorder]([col1]);
then use the undocumented dmv sys.dm_db_database_page_allocations[/url] to get the page_id of the data(clustered) and index(nonclustered) pages.
SELECT [allocated_page_page_id]
FROM [SYS].[DM_DB_DATABASE_PAGE_ALLOCATIONS](DB_ID(), OBJECT_ID('indexorder'), 1, NULL, 'DETAILED')
WHERE [page_type] = 1;
SELECT [allocated_page_page_id]
FROM [SYS].[DM_DB_DATABASE_PAGE_ALLOCATIONS](DB_ID(), OBJECT_ID('indexorder'), 2, NULL, 'DETAILED')
WHERE [page_type] = 2;
SELECT [allocated_page_page_id]
FROM [SYS].[DM_DB_DATABASE_PAGE_ALLOCATIONS](DB_ID(), OBJECT_ID('indexorder'), 3, NULL, 'DETAILED')
WHERE [page_type] = 2;
SELECT [allocated_page_page_id]
FROM [SYS].[DM_DB_DATABASE_PAGE_ALLOCATIONS](DB_ID(), OBJECT_ID('indexorder'), 4, NULL, 'DETAILED')
WHERE [page_type] = 2;
in my case the pageid for the data page is 55169, and for the index pages are 55171,55173,55175. I then use the undocumented command dbcc page to examine the contents. be sure to turn on trace flag 3604 to send output to client.
dbcc traceon(3604)
dbcc page(5,1,55171,3)
when I run dbcc page for the page_id of the IX_test index, the output shows that the locationkey is first, then datekey and itekey.
FileId PageId Row Level locationkey (key) datekey (key) itemkey (key) KeyHashValue Row Size
1 55171 0 0 1 1 1 (3544a536b2e7) 16
1 55171 1 0 2 2 2 (6a6f551a2084) 16
when i run dbcc page for IX_test2, itemkey is first, then datekey, followed by locationkey.
dbcc page(5,1,55173,3)
FileId PageId Row Level itemkey (key) datekey (key) locationkey (key) KeyHashValue Row Size
1 55173 0 0 1 1 1 (3544a536b2e7) 16
1 55173 1 0 2 2 2 (6a6f551a2084) 16
and finally cleanup
dbcc traceoff(3604)
DROP TABLE [indexorder];
so based on this test it does look like the clustered keys get added in the same order. whether dbcc page is just displaying them in this order or they are actually stored on the page in this order, I am unsure.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply