August 17, 2016 at 1:31 pm
Hello all,
I'm new to SQL and also new to this forum. I hope everyone is well, I just had a couple of questions that are probably quite simple for you lot!
1) Should every primary key in a table be a clustered index so that data is organised?
2) When creating a non-clustered index on a column when I do a select statement is the index always automatically used if I select from the column the index is stored on? If this is a yes when doing an insert, update etc is it best to ignore the index and if so how can I do this?
I really appreciate any help with this!
Many Thanks,
Chris
August 17, 2016 at 1:45 pm
christiandreyer (8/17/2016)
Hello all,1) Should every primary key in a table be a clustered index so that data is organised?
As a general rule, yes. There may be exceptional cases where clustering on different columns is preferable, but that depends entirely on the mix of queries being run against the table. If you are just starting out in SQL, follow the general rule in all cases.]
2) When creating a non-clustered index on a column when I do a select statement is the index always automatically used if I select from the column the index is stored on? If this is a yes when doing an insert, update etc is it best to ignore the index and if so how can I do this?
The optimizer chooses which indexes, clustered or non-clustered, to use based on it's estimate of the various methods it could choose from to produce your results. Don't try to second-guess it. If you try to force an index to use or disregard an index, you are probably going to get worse results.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 17, 2016 at 1:49 pm
1) No. The primary key and clustering key are not necessarily directly related. The clustering index is the single most critical factor for performance. And it should be based exclusively on what's best for that table, regardless of if/what the PK is.
Edit: There should be no such thing as a "default" clustering index. Again, it's the most important performance factor, so it should always be chosen carefully based on table usage, not just an identity column slapped on the table and clustered on by default. That identity myth is the biggest overall harm to db performance.
2) No. If the index covers the entire query, it will very likely be used, unless another index also covers and has fewer pages.
when doing an insert, update etc is it best to ignore the index and if so how can I do this?
No. If you disable a nonclustered index, SQL can no longer trust the index and thus can't use it to satisfy queries. If you don't need the index, just drop it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 17, 2016 at 1:50 pm
well that answers that! thanks a lot.
August 17, 2016 at 2:45 pm
ScottPletcher (8/17/2016)
No. If you disable a nonclustered index, SQL can no longer trust the index and thus can't use it to satisfy queries. If you don't need the index, just drop it.
Disabling doesn't mean the index isn't trusted. It means the index isn't there. Disabling a nonclustered index removes the b-tree, just leaving the metadata behind.
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
August 17, 2016 at 2:49 pm
The confusing thing about clustered indexes and primary keys is how, by default, SQL server uses the PK columns as the keys for the clustered index when you create a primary key (unless you specify NONCLUSTERED when creating it). I think this causes people to see them as the same thing or as serving the same purpose. Sometimes the primary key column is the right choice for your clustered index but not always.
When you create a clustered index you are defining how the data is sorted. When you define a primary key you are defining a key that is unique to each record in that table. Clustered indexes (as Scott eluded to) are about performance. Primary keys are more about referential integrity (e.g. primary key > foreign key relationships).
Also remember that indexes (when the optimizer uses them) improve query performance for SELECT queries but slow down data modifications (insert, update, delete). Databases with frequent data modifications should be minimally indexed. Databases which are used primarily for reporting can (and usually should) have more indexes.
-- Itzik Ben-Gan 2001
August 17, 2016 at 2:55 pm
Those are excellent points. Thus, the other rule you should follow when creating indexes is to always explicitly specify CLUSTERED or NONCLUSTERED. Again, clustering is far too critical to let it happen basically by accident.
MS is trying to make it "easier" for novices to use SQL Server. There's some value to that, since if a table is only going to have a single index it should most often be a clustered index. Still, for truly good performance, you're going to have to put some knowledge and effort into determining the best clustered index key(s) for every (significant) table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 17, 2016 at 3:17 pm
Christian, just in case you're still following this thread. The answer that we all agree on is that the correct index choice depends on the particular usage of a table, and that proper indexing is critical to performance. The best advice is to educate yourself about indexes and keep asking questions about specific situations. Enjoy the ride 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply