January 21, 2009 at 6:29 am
I optimised my queries using Tuning Advisor, which suggested that some indexes be created. Some of these indexes are covered indexes using "INCLUDE" keyword. May someone out there advise me on how to write the covered indexes in SQL 2000. I've noticed that they're not compatible with SQL 2000.
I don't have knowledge of SQL 2000 but hope the indexes must be tweaked a bit for compatibility purpose. Are statistics created the same way in both versions?
Any help will be highly appreciated.
January 21, 2009 at 6:41 am
Statistics are enough the same between the versions that you don't really need to worry about them too much.
The only way to create a covering index in 2000 was to put all the columns into the key of the index itself. There is no INCLUDE option at all. So, for example if the 2005/2008 index looked like this:
CREATE INDEX i ON dbo.x(col1)
INCLUDE (col2,col3)
The 2000 index would have to be:
CREATE INDEX i ON dbo.x(col1,col2,col3)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2009 at 6:52 am
And just note there is a 16 column/900 byte limit on index key size.
Edit: 16, not 60.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2009 at 7:05 am
True.
And one other thing I just thought of, your clustered index is a part of the covering index even though you can't see it.
Assume that ID is the clustered index. A query like this:
SELECT ID
,Col1
,Col2
FROM x
WHERE Col3 = 42
This query will still be covered by the index in my last post because the ID, while not part of the index defintion, is a part of the index since the cluster key is stored with the index as a pointer between the index and the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 21, 2009 at 7:12 am
Thanks a lot guys out there.
I've noticed that the key columns include a sort order eg. asc but the columns in the include clause don't, do I add this sort order to non key columns as well or I remove it from all columns.
Will this clause below be compatible with 2000? Unfortunately I'm not able to test this myself since I do not have any SQL 2000 instance running within my network, I've to send the script to be tested somewhere else.
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
January 21, 2009 at 7:25 am
thulani.moyana (1/21/2009)
I've noticed that the key columns include a sort order eg. asc but the columns in the include clause don't, do I add this sort order to non key columns as well or I remove it from all columns.
Doesn't matter. By default (if no direction is supplied) it will be ascending
Will this clause below be compatible with 2000?
No.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply