SQL Server Tables
-
Comments posted to this topic are about the item SQL Server Tables
-
Thanks for start my day with easiest question 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Though it is easy enough to figure the intent of this question, I disagree with it.
A Heap can have nonclustered indexes. A heap just means that the table is without a clustered index.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
I have to agree with SQLRNNR on this one....
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
+1 to SQLRNNR for making this point
-
Good, simple, basic terminology question.
Answer is correct.
Question text is a bit strange. Yes, we call a table with no clustered or nonclustered indexes a heap. But that name also applies to tables that do have nonclustered index. The only thing implied by the name heap is the absence of a clustered index.
The explanation is partly right (it does include the correct definition for heap), partly wrong and misleading.
* "Unindexed tables are good for fast storing of data." - I am not at all sure that heaps are better for this than clustered indexes. In fact, I am not even sure what the exact comparison is about. "fast storing of data" - surely that cannot be the only objective? If all you want to do is store and never retrieve, then you can just as well throw away the data right away. I am guessing (from context) that the intention was to write "fast insert performance". And it's true that if you need insert processing to be extremely fast, a heap will be better than a randomly chose clustered index - but it will not be faster than a clustered index on an always increasing key (like for instance date/time of insert, or an identity value). So there are still at least those two options, giving you the opportunity to optimize for other requirements as well. There may be cases where the heap is indeed the fastest - but they will be the minority, not the majority. Fragmentation can make heaps incredibly slow. Google "the table scan from hell" to see a simple example that shows a table scan that gets 12.5 x more expensive after updating just 25% of the rows in a table.
* "Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that." - For nonclustered indexes, this is true. If you need to make lots of changed to the table data (not just inserting, same goes for deleting or updating), then dropping or disabling the nonclustered indexes first, then recreating or rebuilding them after the changes are made can be faster - depending on the amount of change versus the size of the table. But that is only for NONclustered indexes. Definitely not for all indexes. I have never seen a case where you could gain performance by dropping a clustered index, then later rebuilding it. Dropping a clustered index has little overhead if you make sure to drop nonclustered indexes first (otherwise, all nonclustered indexes are basically rebuilt). But creating a clustered index has a lot of overhead: all data in the heap is read, sorted, and used to create a completely new copy of the table, after which the old one is deleted. Lot of reading, lot of processing, lot of writing. (And if you have any nonclustered indexes at that time, they too need to be rebuilt).
There are valid use cases for heaps. But there are not many of them. And they can be very challenging to properly maintain. As a rule of thumb, my advice is to steer clear of them, and only ever use them if you are 100% certain that you understand all implications, and that your specific situation does indeed call for a heap.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
SQLRNNR (8/26/2013)
Though it is easy enough to figure the intent of this question, I disagree with it.A Heap can have nonclustered indexes. A heap just means that the table is without a clustered index.
Though I agree that the inclusion of nonclustered indexes is weird, I think you are wrong in saying you disagree with it; that is worded much too strong. The question is how a table with no indexes (either clustered or nonclustered) is called; disagreeing with the answer implies you think it's wrong. It's not.
(If the question had been "how do we call a table without clustered indexes on Tuesdays", the same answer would also have been correct - even though the name does not change on other days of the week)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (8/27/2013)
SQLRNNR (8/26/2013)
Though it is easy enough to figure the intent of this question, I disagree with it.A Heap can have nonclustered indexes. A heap just means that the table is without a clustered index.
Though I agree that the inclusion of nonclustered indexes is weird, I think you are wrong in saying you disagree with it; that is worded much too strong. The question is how a table with no indexes (either clustered or nonclustered) is called; disagreeing with the answer implies you think it's wrong. It's not.
(If the question had been "how do we call a table without clustered indexes on Tuesdays", the same answer would also have been correct - even though the name does not change on other days of the week)
For me, the wording of the question implies that a requirement of a heap be that it is completely devoid of indexes. Though a table without any indexes is a heap, the simple requirement is that the table not have a Clustered Index. If the intent of the wording of the question is otherwise, then so be it. The wording and explanation could be much better than it is in this case.
Too many will read this question and think that a table cannot be a heap unless it is completely without indexes. I'd rather avoid that, and have a more precisely worded question. I've dealt with too many DBAs that insist a table can't be a heap unless it has no indexes at all. That kind of thinking is wrong.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Too easy question, but nice discussion. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Is it me only who got in doubt by seeing such an easy question ? For a moment i tried to look out for some catch and tried to look between the words ("or" condition looked a little suspicious for a while :-)) but finally clicked the correct one. 😀
-
On the other note, nice to know about the real definition by SQLRNNR and Hugo for Heap table. So the correct definition goes like this:
"A heap table can or cannot have non clustered indexes because it's defined by the absence of clustered index."
Hugo/SQLRNNR, correct me if I am wrong...
-
sqlnaive (8/27/2013)
On the other note, nice to know about the real definition by SQLRNNR and Hugo for Heap table. So the correct definition goes like this:"A heap table can or cannot have non clustered indexes because it's defined by the absence of clustered index."
Hugo/SQLRNNR, correct me if I am wrong...
This is correct.
A table is either organized as a clustered index, implemented as a modified B-tree (because 1 clustered index has been defined on it); or as a heap (because 0 clustered indexes are defined on it). In SQL Server 2012 and below, those are the only options available.
(In SQL Server 2014, a third options is introduced - a clustered columnstore index, which is not a modified B-tree but uses a completely different organization structure).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Thanks Hugo for confirming it.
See thats the profit of being a part of such a wonderful forum. I learned/clarified things more or less on daily basis even out of such a simple question.
Thanks to all and sqlservercentral. :satisfied:
-
sqlnaive (8/27/2013)
Thanks Hugo for confirming it.See thats the profit of being a part of such a wonderful forum. I learned/clarified things more or less on daily basis even out of such a simple question.
Thanks to all and sqlservercentral. :satisfied:
+1
I did know this well enough but Hugo's clarification on heap use cases was worth the price of admission alone - cheers.
-
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply