May 16, 2012 at 1:36 am
Hi
I have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
Thanks in advance
May 16, 2012 at 2:18 am
This was removed by the editor as SPAM
May 16, 2012 at 2:50 am
erics44 (5/16/2012)
HiI have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
No, not at all. I'd go as far to say that nearly all tables should have a clustered index until you've tested that you get an overall benefit from a heap, but the clustered index should not always be the Primary Key.
The major exception would be a load table that's bulk inserted in an indeterminate order.
I think there are very few tables that are all insert and no select (if so, what's the point of having it). Even an audit table benefits from a clustered index (e.g. on a timestamp column). As long as it's ever-increasing and not too wide, it has very little overhead and benefits the occasional queries immensely.
May 16, 2012 at 6:03 am
Every test I've seen indicates, except in well proven edge cases, you will always receive a benefit from clustered indexes, even on inserts. If you do a search on "Which is faster, heaps or clustered indexes" you'll find lots and lots of results.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2012 at 8:05 am
erics44 (5/16/2012)
HiI have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
Thanks in advance
Think about what a clustered index is. Firstly, it is your data. Very generally, lots of inserts can cause problems if the data that is inserted is not in the order of the clustered key. This will cause fragmentation. Think of it like this... You have a book and it is ordered by chapters, right? You are the author and you already have 10 chapters written. You realize that you need to add another chapter. If it is going to be chapter 11, then you don't have to reorganize everything else in the book, you just add chapter 11 to the end. However, if your chapters are not numbered, but are named and sorted by name, you may have an issue if this chapter name falls between the 5th and 6th chapters. Now you have to insert this chapter in-between the 5th and 6th chapter. You can do this by adding pages, moving the 6th chapter and everything after it, etc. That movement causes fragmentation.
I hope that is a good example. Anyone have anything to add to it to make it more clear or correct something that makes the example bad? Please do!
Jared
CE - Microsoft
May 16, 2012 at 11:15 am
HowardW (5/16/2012)
...The major exception would be a load table that's bulk inserted in an indeterminate order.
...
Just to affirm what Howard and Grant have been saying: In 12 years of using SQL Server, this is literally the only time that I have ever seen a heap be faster than a clustered index: When doing repeated massive inserts with keys that are out-of-order compared to the existing rows.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 16, 2012 at 7:40 pm
erics44 (5/16/2012)
HiI have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
Thanks in advance
A clustered index physically sort the data and you can find the actual data page under the leaf node of the b-tree(structure of index).
Regards,
TA
Regards,
SQLisAwe5oMe.
May 16, 2012 at 8:45 pm
SQLCrazyCertified (5/16/2012)
erics44 (5/16/2012)
HiI have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
Thanks in advance
A clustered index physically sort the data and you can find the actual data page under the leaf node of the b-tree(structure of index).
Regards,
TA
Actually, all the clustered index can guarantee is the logical order of the data. It is possible due to page-splits that the data isn't in physical order on disk.
May 16, 2012 at 8:53 pm
Lynn Pettis (5/16/2012)
SQLCrazyCertified (5/16/2012)
erics44 (5/16/2012)
HiI have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
Thanks in advance
A clustered index physically sort the data and you can find the actual data page under the leaf node of the b-tree(structure of index).
Regards,
TA
Actually, all the clustered index can guarantee is the logical order of the data. It is possible due to page-splits that the data isn't in physical order on disk.
Lynn, can you give me little bit more explanation if you don't mind. I thought clustered index physically sort the data and non-clustered index logically sort the data. Please advise.
thanks,
TA
Regards,
SQLisAwe5oMe.
May 16, 2012 at 9:00 pm
SQLCrazyCertified (5/16/2012)
Lynn Pettis (5/16/2012)
SQLCrazyCertified (5/16/2012)
erics44 (5/16/2012)
HiI have always been under the impression that in tables where there are a lot of inserts a clustered index should be avoided and clustered indexes should be used in tables that are more often used in select queries.
Probably very simplistic but is this the general view?
Thanks in advance
A clustered index physically sort the data and you can find the actual data page under the leaf node of the b-tree(structure of index).
Regards,
TA
Actually, all the clustered index can guarantee is the logical order of the data. It is possible due to page-splits that the data isn't in physical order on disk.
Lynn, can you give me little bit more explanation if you don't mind. I thought clustered index physically sort the data and non-clustered index logically sort the data. Please advise.
thanks,
TA
Clustered Indexes are a logical sort of the data. Non-Clustered is not data, but is also logically sorted based on the cloumns in the index.
Jared
CE - Microsoft
May 17, 2012 at 2:10 am
so in a situation where i am using a clustered index on a table that accepts a lot of inserts it seems that everyone agrees that it would be quicker than using a heap
but what are the risks of fragmentation and what is the result if fragmentation occurs?
can tables be de-fragged?
May 17, 2012 at 3:54 am
Fragmentation can slow down large range scans from disk. That's most of the effect.
For rebuilding...
ALTER INDEX .... REBUILD.
See Books Online for details and see the articles that were posted earlier for what is recommended for a clustered index key choice.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply