September 25, 2008 at 9:48 am
HI All,
I'm trying to learn more about indexes etc 🙂
now I have set up a table with 3 indexes on it, 2 NON-CLUSTERED and 1 CLUSTERED
Now in my query I use the ONLY the fields contained in the CLUSTERED index however SQL Server seems to think that using one of the other indexes is better.
Even when I force the use of the CLUSTERED index my query does in fact run slower.
I'm wondering why the NON-CLUSTERED index out performs the CLUSTERED index.
Any help will be great thanks:
Here is all the same info need:
[font="Courier New"]
--CREATE TEST TABLE
CREATE TABLE dbo.CTSTest
( ROWNUM INT IDENTITY(1,1),
SomeInt INT NOT NULL,
SomeString VARCHAR(10),
SomeCSV VARCHAR(80),
SomeNumber MONEY,
SomeDate DATETIME
)
--INSERT ROWS INTO TEST TABLE
INSERT INTO dbo.CTSTest
(SomeInt,SomeString,SomeCSV,SomeNumber,SomeDate)
SELECT TOP 200000
SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),
SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
FROM dbo.Tally t1,dbo.Tally t2
--CREATE PK CLUSTERED INDEX
;CREATE CLUSTERED INDEX PK_CTS
ON dbo.CTSTest
(
[RowNum],[SomeInt]
)
--CREATE COVERING NON-CLUSTERED INDEX
;CREATE INDEX IX_CTS
ON dbo.CTSTest
(
[RowNum],[SomeString],[SomeCSV]
)
--CREATE COVERING NON-CLUSTERED INDEX
;CREATE INDEX IX_CTS2
ON dbo.CTSTest
(
[SomeCSV]
)
--QUERY 1
SELECT
[RowNum],
[SomeInt]
FROM dbo.CTSTest
WHERE [RowNUM] % 2 = 0
--QUERY 2
SELECT
[RowNum],
[SomeInt]
FROM dbo.CTSTest WITH (INDEX(PK_CTS))
WHERE [RowNUM] % 2 = 0
[/font]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 25, 2008 at 12:07 pm
Answer:
"%" operator is NOT SARGable, therefor you are forcing an scan. Because the space that the clustered index used is bigger than that of the non-clustered it is in fact cheaper to travel less pages in the non-clustered than in the clustered.
select name ,dpages
from sysindexes
where object_name(id) like 'CTSTest'
name dpages
PK_CTS 2740
IX_CTS 2274
IX_CTS2 2151
* Noel
September 26, 2008 at 2:51 am
Hi Noel,
Thanks for the reply,
What does the dpages mean, is that the space that the index takes up?
Also when I changed the query to use a straight id = x etc I got the same result.
So I guess it has more to do with the size of the index? is that the right thinking?
If so , how come the clustered index takes up more space than the non-clustered if the non-clustered contains more columns and varchars?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 26, 2008 at 6:50 am
dpages is the number of 8K data pages the index uses.
You also do not need to include RowNum in the first non-clustered index because the Clustered index key is included in any non-clustered indexes so you are actually storing RowNum twice in the non-clustered index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply