October 3, 2002 at 7:02 am
In SQL Server 7 and SQL2K:
When you create and Primary Key, does SQL Server automatically create an Index? If so, is it in an index that can be used in queries involving the primary key?
I had thought so but yesterday looking up an explain query, it was showing a table scan on a query by primary key. We created an index and then explain query showed using the index. What gives?
Jesus Ramos
October 3, 2002 at 7:28 am
When you create a primary key, by default it creates a clustered index. If you specify PRIMARY KEY NONCLUSTERED, it'll create a non-clustered index instead.
Run an sp_help on the table and see what indexes are listed.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
October 3, 2002 at 8:48 am
Just an aside really on the query optimiser. SQLServer may not always use a particular index, if it deduces that to do so won't be too efficient, for example where an index has a high density of duplicate values it may not be useful in a single row seek.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply