Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steppinthrax
Starting Member
27 Posts |
 Posted - 2010-05-14 : 14:28:13
|
| I got into a little argument with some office buddies over Unindexed tables v.s. indexed. I understand when you add a index to a table you increase select performance because you don't have to do a full table scan. However you take a performance hit with Inserts. Because SQL has to search the structure, looking for a space to insert it.In a heap, since there is no structure inserts should be the fastest, however selects should take a performance hit. However Microsoft says otherwise!!!!!!!!!!http://support.microsoft.com/kb/297861This article indicates heaps are slower with inserts?????Someone explain |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-05-14 : 14:36:15
|
| Yeah...blows the mind doesn't it...Your "understanding" makes logical sense and is (or was) true on DB2 z/osBut There is a host of thing in SQL Server that will blow your mindEDIT: "For more details about how the space is allocated, see the "Managing Space Used by Objects" topic in SQL Server Books Online."Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
steppinthrax
Starting Member
27 Posts |
Posted - 2010-05-14 : 14:38:27
|
quote:
Originally posted by tkizer
It seems to me that it is explained in the article already that it has to do with saving space rather than performance.I don't really see the benefit of heaps anyway though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Yes, I do see that, that is what I thought. But it says further that when a heap is used Microsoft SQL is performing some "special" process ontop of the database upon each insert. Where as in a traditional database that process is possibly not performed.A heap is good if you plan to insert data into a table that will very rarely if every be selected from.... It's also good if the only selection you will ever do is a Select * from table |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-14 : 16:48:36
|
| This article indicates that there is never or almost never a performance advantage in using a heap. Comparing Tables Organized with Clustered Indexes versus Heaps SQL Server Best Practices Article http://technet.microsoft.com/en-us/library/cc917672.aspxCODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-15 : 02:58:38
|
| O/P Note that Clustered Index is required (rather than there being indexes but none of them are clustered). Clustered Index makes it possible to do rebuild of the table, which will reclaim space etc. without that the table will become fragmented (if you delete rows)Quite apart from speed of finding a row (for SELECT, UPDATE or DELETE) by Clustered Index (usually the PK) I have never had a table that I did not want to get the benefits of rebuilds on.If you just want a "log and forget" table a sequentially appended flat file might be better? |
|
|
|
|
|
|
Heap V.S. Indexed table (Insert performance)