January 12, 2016 at 10:31 am
I'll grant you that if there are no non-clustered indexes, this isn't a problem, but you have other problems if you have no indexes on the table. If it's a staging table, then why rebuild it? There may be other cases, but they'd be edge cases.
So the rule would be, don't do this. Unless you have a good reason to.
The super simple rule works. Override it if you have a reason, but not if you are following someone else's advice, which either you can articulate (then override) or you cannot (then don't).
January 12, 2016 at 12:53 pm
Steve Jones - SSC Editor (1/12/2016)
I'll grant you that if there are no non-clustered indexes, this isn't a problem, but you have other problems if you have no indexes on the table. If it's a staging table, then why rebuild it? There may be other cases, but they'd be edge cases.So the rule would be, don't do this. Unless you have a good reason to.
The super simple rule works. Override it if you have a reason, but not if you are following someone else's advice, which either you can articulate (then override) or you cannot (then don't).
SQL can sometimes leave extra space in heaps, not properly releasing the space (or improperly allocating too much space, I guess). A rebuild is needed to correct that.
Some super-simple rules work -- never use ISNULL() in a WHERE or JOIN & never use a default clustered index on a table -- but not "never"/"just don't" rebuild a heap.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 12, 2016 at 9:32 pm
ScottPletcher (1/12/2016)
never use a default clustered index on a table
I've got to say that I totally disagree with that. The clustered index is frequently used for row control rather than any advantage in a query and, for wide tables, can actually be a performance disadvantage if you cluster based on the most common queries. Even with row lookups, Non-Clustered indexes can frequently out strip clustered indexes for performance on wide tables. Yeah... I know that wide tables aren't usually good but these are actually fully normalized tables. Such monsters actually do exist whether properly normalized or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2016 at 9:20 am
Jeff Moden (1/12/2016)
ScottPletcher (1/12/2016)
never use a default clustered index on a tableI've got to say that I totally disagree with that. The clustered index is frequently used for row control rather than any advantage in a query and, for wide tables, can actually be a performance disadvantage if you cluster based on the most common queries. Even with row lookups, Non-Clustered indexes can frequently out strip clustered indexes for performance on wide tables. Yeah... I know that wide tables aren't usually good but these are actually fully normalized tables. Such monsters actually do exist whether properly normalized or not.
My point is that even then the clustered index should be carefully chosen and not based on general rules about static, narrow, etc.. The clus index is so critical it should never just be defaulted because of some super-simple, generalized "rule".
Edit: In particular, don't just slap an identity on the table and assume that's automatically the best clustering key.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 13, 2016 at 9:31 am
ScottPletcher (1/13/2016)
In particular, don't just slap an identity on the tableand assume that's automatically the best clustering key.
Fixed that for you. 😉
January 13, 2016 at 9:42 am
Hugo Kornelis (1/13/2016)
ScottPletcher (1/13/2016)
In particular, don't just slap an identity on the tableand assume that's automatically the best clustering key.Fixed that for you. 😉
Not at all :-D. Slapping an identity on the table isn't particularly damaging in and of itself. It's when that column is automatically assumed to be the clus key that it's a major problem.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 13, 2016 at 1:59 pm
ScottPletcher (1/13/2016)
Slapping an identity on the table isn't particularly damaging in and of itself.
It actually is. If not needed, it is a waste of storage both on disk, in all backups, and in the buffer pool. If not needed, it also results in more and complexer joins for some queries, and more effort for modification queries (insert, update, merge).
Also, based on what I see both on forums, and at clients, and in commercial products by big venndors, once the identity is there almost everyone simply forgets to define and declare the business key in the table, leaving the identity as the only defined candidate key - unvariably resulting in duplicated data. (Yes, I know, just "slapping on identity on the table" does not automatically imply this - in theory. But in practice, it actually does).
January 13, 2016 at 2:21 pm
Hugo Kornelis (1/13/2016)
ScottPletcher (1/13/2016)
Slapping an identity on the table isn't particularly damaging in and of itself.It actually is. If not needed, it is a waste of storage both on disk, in all backups, and in the buffer pool. If not needed, it also results in more and complexer joins for some queries, and more effort for modification queries (insert, update, merge).
Also, based on what I see both on forums, and at clients, and in commercial products by big venndors, once the identity is there almost everyone simply forgets to define and declare the business key in the table, leaving the identity as the only defined candidate key - unvariably resulting in duplicated data. (Yes, I know, just "slapping on identity on the table" does not automatically imply this - in theory. But in practice, it actually does).
Quite true. And in general I agree, which is why I've been pushing so long to never consider it the "default" clustering key, as far, far too many people do, particularly developers. Or to supplant or supersede other keys, as it, again, far, far too often does.
That said, an identity column is often useful on a table, although not always. However, so many are addicted to the crutch of having an identity there that I just don't think realistically it will be completely removed from most tables, albeit that it's not really needed a majority of the time. Given that, my fallback position is to at the very least get rid of the extraordinarily harmful myth that identity should be considered a presumed clustering key.
Edit: I've seen new table "designs" where literally every table starts with an identity and every table is clustered on that identity. Yikes!!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 13, 2016 at 2:27 pm
ScottPletcher (1/13/2016)
That said, an identity column is often useful on a table, although not always. However, so many are addicted to the crutch of having an identity there that I just don't think realistically it will be completely removed from most tables, albeit that it's not really needed a majority of the time. Given that, my fallback position is to at the very least get rid of the extraordinarily harmful myth that identity should be considered a presumed clustering key.Edit: I've seen new table "designs" where literally every table starts with an identity and every table is clustered on that identity. Yikes!!
I absolutely agree. Identity is a great feature when used with care. It's the "with care" part that most people seem to forget.
As to the designs you see - unfortunately those are the majority of database designs I see nowadays. *sigh*
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply