When a nonunique nonclustered index is built on top of a clustered
index, is it guaranteed that the bookmark in the nonclustered index
will be kept in the same order as the clustered index?
Here's an example to demonstrate my question:
CREATE TABLE indextest (col1 int NOT NULL,col2 int NOT NULL,col3
int,col4 int)
ALTER TABLE indextest ADD PRIMARY KEY CLUSTERED (col1,col2)
CREATE INDEX ix_indextest ON indextest (col1,col3)
GO
INSERT indextest VALUES (1,2,1,1)
INSERT indextest VALUES (1,3,2,1)
INSERT indextest VALUES (1,4,2,1)
INSERT indextest VALUES (2,1,1,1)
INSERT indextest VALUES (1,1,1,1)
SELECT col1,col2 FROM indextest WHERE col1=1 AND col3=1
DROP TABLE indextest
The select statement above is covered by the nonclustered index, so
that index is used. However, the nonclustered index is defined only to
ensure the ordering of col1 and col3 within the index; col1 and col2
follow within the index as the bookmark to the clustered index. When I
run this query, my desired result is to have the records appear in the
order supported by the clustered index:
1,1
1,2
As it happens, the result I got was indeed in that order, but I don't
know if it was mere coincidence, or if the bookmark in the nonclustered
index is maintained in the same order as the clustered index. If I
want to ensure the above order, is it sufficient to have the
nonclustered index defined as above, or do I need to define it as:
create index ix_indextest on indextest (col1,col3,col2)
just to be sure that the results are returned in ascending order for
col1,col2? If the two-column index is sufficient, is it guaranteed to
still be sufficient in SQL2005 and future versions of SQL Server, or am
I better off adding the third column just to be safe?
Thank you,
--Dennis Culley
