RE: Nonclustered index which includes XML column

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

In SQL 2005, you can use the new INCLUDE clause to include columns (including
XML columns) with an index. You're right than XML columns cannot be index
key columns, but a well designed index with included columns _could_ do a
better job of covering. See the BOL 'CREATE INDEX' topic for more info on
INCLUDE and covering indexes. ( Examples below )

Also, re indexes and the optimiser, it makes it's decisions based on its
statistics and the indexes you create. It pretty often gets it right and
clustered index scans really aren't that bad.

Big quote from Inside SQL Server 2000 by Kalen Delaney:

"A query using an index on a large table is often dramatically faster than a
query doing a table scan. But this is not always true, and table scans are
not all inherently evil. Nonclustered index retrieval means reading B-tree
entries to determine the data page that is pointed to and then retrieving the
page, going back to the B-tree, retrieving another data page, and so on until
many data pages are read over and over. (Subsequent retrievals can be from
cache.) With a table scan, the pages are read only once. If the index does
not disqualify a large percentage of the rows, it is cheaper to simply scan
the data pages, reading every page exactly once."

Samples:
To run these, turn on your 'Show Execution Plans' option (Ctrl+M) first.

USE tempdb

DROP TABLE tblTest

CREATE TABLE tblTest
(
intRecordId int not null PRIMARY KEY,
intUserId int not null,
xmlWhatever XML not null,
datDateProcessed datetime null,
intNumTries int not null default (0)
)

--(something like that)

--Procedure:

CREATE INDEX idx ON dbo.tblTest ( intRecordId, intUserId, intNumTries,
datDateProcessed ) INCLUDE ( xmlWhatever )

SELECT
intRecordId,
intUserId,
xmlWhatever,
intNumTries
FROM
tblTest
WHERE
datDateProcessed IS NULL
AND
intNumTries < 5

-- Force use of non-clustered index
SELECT
intRecordId,
intUserId,
xmlWhatever,
intNumTries
FROM
tblTest WITH (INDEX(IDX))
WHERE
datDateProcessed IS NULL
AND
intNumTries < 5


You'll see the plans compare 50%/50% showing the clustered index scan isn't
too bad. I would try again with a million rows and some more columns in the
table which aren't part of your non-clustered index, and I would expect that
to win.

HTH
wBob
Rate the post
.