March 13, 2014 at 6:12 am
Hi,
If you have a heap table which has non-clustered indexes, does this mean it will always do a full table scan?
What if you had a primary key in your table and non-clustered indexes only. Would it still do a full table scan?
I know clustered indexes do a table seek and don't scan all of the tables records.
Do clustered indexes only have to be applied to primary keys or can they be applied to any column in the table (primary key or not)?
Thanks.
March 13, 2014 at 7:35 am
zedtec (3/13/2014)
If you have a heap table which has non-clustered indexes, does this mean it will always do a full table scan?
No.
What if you had a primary key in your table and non-clustered indexes only. Would it still do a full table scan?
Depends on the query and indexes which exist.
I know clustered indexes do a table seek and don't scan all of the tables records.
No such thing as a table seek (seek on a clustered index is a clustered index seek), a clustered index does not prevent table scans, queries against a table with a clustered index may still do a full scan of all rows, depending on the query and indexes
Do clustered indexes only have to be applied to primary keys or can they be applied to any column in the table (primary key or not)?
Any column or set of columns which are valid as index keys can be used as the clustered index key.
See:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
March 13, 2014 at 10:14 am
That's useful thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply