Access path selection

SQL Server 2000 32 bit sp4 / Windows 2003

I have a table that has over 7.5 million rows. I have a unique clustered index (U_ORDR, D_ORDR_YR). There are 5342000 distinct occurances of U_ORDR and 24 distinct occurances of D_ORDR_YR. Database Tuning Advisor showed that there should be a nonclustered index created like (D_ORDR_YR, U_ORDR). I created this index and ran a query with show execution plan. The nonclustered index is chosen even though a bookmark lookup is required. Also the way I understand it the clustered index would be used to get to the C_ORDR column. So why is SQL Server choosing to access the nonclustered index. The plan shows a clustered index scan when I force the clustered index to be used. It also show that it would take longer to use the clustered index (85%) than to use the nonclustered/bookmark access (15%). Here is the query that I am using

declare @.OrderNumber [bigint], @.OrderYear [bigint]

select @.OrderNumber = 1753851, @.OrderYear = 02

SELECT C_ORDR

FROM CIS540T CIS540T --with (index (IX_CIS540T_Ordr_no))

WHERE CIS540T.U_ORDR = @.OrderNumber and CIS540T.D_ORDR_YR = @.OrderYear

If this is the exact query, consider just putting an index to cover:

C_ORDR, U_ORDR, D_ORDR_YR

Then you get the best of both worlds...

|||

Just to expand on Louis' suggestion in that you should specify the following order for the columns in your new index:

U_ORDR, D_ORDR_YR, C_ORDR

It sounds like the U_ORDR column offers the highest selectivity, followed by D_ORDER_YR. C_ORDR is included to form a covering index for your query.

Chris

|||My question are why is SQL Server doing an index scan when I told it that D_ORDR_YR and U_ORDR is unique. Why doesn't it use an index seek?|||

Both U_ORDR and D_ORDR_YR are defined as BIGINT in the table?

|||Both columns are defined as decimal. U_ORDR with a precision of 7 and U_ORDR_YR with a prrcision of 3. I changed the definition of @.U_ORDR and @.D_ORDR_YR to decimal the plan stayed the same. It is still doing an index scan instead of a seek.|||

Hmmm, the conversion can/will cause a scan to substituted for a scan.

try issuing:

DBCC FREEPROCCACHE

GO

first and then rerun.

|||It does seem to have been a column definition problem. I did some more swapping of data types and was able get the plan to show an index seek. Thanks for your help.