[quoted text, click to view]
you should hget a table scan which is probably not what you want <<

not necessary a table scan. more often he will see a clustered index scan
with an additional "WHERE OrderID ..." or if UserID selectivity is low (20-
unique values on a 100k rows table) and joining with users table he will see
a clustered index seek executed 20 times for each value of UserID (coming
from the selection of users table).

HTH,
</wqw>

When dealing with index "what if" questions, I find it best to test it
for myself. Looking at sample queries and their exection plans help and
also working with the command "set statistics IO on" to see your logical
IO (how many types you hit a page) helps to determine your best index
strategy.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for all contributions,
What happens if instead of PRIMARY KEY (USERID, ORDERID), I have PRIMARY KEY
(ORDERID, USERID) -- I just reverse the order of the columns. If I need a
SELECT on ORDERID only (no USERID specified), do I need a separate index on
ORDERID or will the PRIMARY KEY (ORDERID, USERID) index help me avoid a
table scan?

Thanks,
Krup Nugent



[quoted text, click to view]
Viba,

The index created to support the primary key can only be used to provide
keyed access if the query includes the leftmost columns in the WHERE clause,
soin this case if you included USERID. If you are only speciying selection
on ORDERID then all else being equal you should hget a table scan which is
probably not what you want. So in this case adding an additional index on
ORFDERID sounds sensible.

I would also experiment with making the table clustered on ORDERID, as well
as clustered (as I guess it is at the moment) on USERID and ORDERID. Without
knowing what you are doiung with table it is impossible to say which would
be best, but try both, measure the performance difference and should see
which is best.

Mike John


[quoted text, click to view]

As always, it depends. This will be great if what you want to get is a
particular order. However, if what you are looking for is all orders for a
particular UserID, then you'd be best to have a nonclustered index on
UserID. In the end, you have to analyze all queries going to that table to
settle on the best candidate for the clustered index.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
[quoted text, click to view]
Thanks for all contributions,
What happens if instead of PRIMARY KEY (USERID, ORDERID), I have PRIMARY KEY
(ORDERID, USERID) -- I just reverse the order of the columns. If I need a
SELECT on ORDERID only (no USERID specified), do I need a separate index on
ORDERID or will the PRIMARY KEY (ORDERID, USERID) index help me avoid a
table scan?

Thanks,
Krup Nugent



[quoted text, click to view]
Viba,

The index created to support the primary key can only be used to provide
keyed access if the query includes the leftmost columns in the WHERE clause,
soin this case if you included USERID. If you are only speciying selection
on ORDERID then all else being equal you should hget a table scan which is
probably not what you want. So in this case adding an additional index on
ORFDERID sounds sensible.

I would also experiment with making the table clustered on ORDERID, as well
as clustered (as I guess it is at the moment) on USERID and ORDERID. Without
knowing what you are doiung with table it is impossible to say which would
be best, but try both, measure the performance difference and should see
which is best.

Mike John


[quoted text, click to view]