SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Never, Ever Use Clustered Indexes


Never, Ever Use Clustered Indexes

Author
Message
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98415 Visits: 33014
Comments posted to this topic are about the item Never, Ever Use Clustered Indexes

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Abrar Ahmad_
Abrar Ahmad_
SSC Eights!
SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)SSC Eights! (896 reputation)

Group: General Forum Members
Points: 896 Visits: 1305
So.... Never, Ever Use clustered indexes ... while working with oracle :-D

just kidding !!! Hehe.

IceDread
IceDread
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 1145
Fun post :-)

Very few system ends up chaining their database to another database. I believe in the agile way and to plan for a risk of changing db is something you should not do. That is a problem you take if it comes.
Alex Fekken
Alex Fekken
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 460
"every table within SQL Server should have a clustered index"

Fine, but should it then also always be the (preferably meaningless?) primary key? Or an index that is more likely to be used to retrieve ranges of records?
P Jones
P Jones
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2842 Visits: 1524
The problem of possibly changing databases should be catered for in the actual application code by using a data access layer that can be changed according to the database used, not in the database.
The "black box" data access layer will always take the same inputs and queries from the app and communicate appropriately with the relevant database.
Visual Studio development makes this easy despite being a Microsoft product!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98415 Visits: 33014
Alex-668179 (5/31/2012)
"every table within SQL Server should have a clustered index"

Fine, but should it then also always be the (preferably meaningless?) primary key? Or an index that is more likely to be used to retrieve ranges of records?


Ah, but there's the rub. Honest people disagree. But there's little disagreement that there are radical differences in the methods of storage between a clustered index and a heap. In the overriding majority of the time, use a clustered index.

Also, I wouldn't focus on that "range of records" argument either. I'm not sure where that came from originally, but it's long been considered bunk. I usually focus on making my clustered index the most frequent access path to the data in a given table. That's frequently the primary key, but not always.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Ian Elliott
Ian Elliott
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 971
P Jones (5/31/2012)
The problem of possibly changing databases should be catered for in the actual application code by using a data access layer that can be changed according to the database used, not in the database.
The "black box" data access layer will always take the same inputs and queries from the app and communicate appropriately with the relevant database.
Visual Studio development makes this easy despite being a Microsoft product!


But if I'm understanding the point of the post while you can do some of this type of abstraction in a data access layer you still need to have the database optimised and as far as I know (which maybe isn't that much :-D) the only way to do this is at the database level and will therefore be different for different database products.

Well that is unless you plan to load the entire DB into application memory and then I guess the data access layer could be responsible for all performance issues.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215144 Visits: 41979
In fact, I’d argue that you need to design precisely for specific relational database management systems because, let’s face it, they don’t implement the fundamentals in the same way. If you mess up the fundamentals, you’ve just messed up your entire design.



I love this article (Seriously. Well done, Grant.). It describes in yet another way how true portability is nothing more than a myth and a wish.

I've always said that NOT using the extremely powerful proprietary extensions of either SQL Server or Oracle (to name just a couple of RDBMSs) is like not using anything but the 4 basic functions on a scientific calculator because some people won't have anything but a 4 function calculator. Then, even after making the mistake of using only those proverbial 4 functions, they find out they're moving from a scientific calculator to one that uses reverse notation and even the 4 functions don't work the same way.

Oh, wait. No... Sorry. I forgot... we can use ORMs and things like Visual Studio where even a neophyte can make everthing portable without any hits on performance. Well, except for most batch code... and some front end code... Ok... most front end code especially when you have to trick the defaults into not using NVARCHAR for everything that's character based. Yep... gotta love things like Linq.

At least we don't have to worry about how to write parameterized stored procedures to be portable. We're lucky that everyone got together and decided how to declare variables and what the datatypes will be, huh? Orms can take care of any shortcomings there, too, I'm sure.

And lets talk about the true backbone of all companies... I'm certainly happy the we don't have a portability problem with differences in Reporting code and other BI code. We can just skip using what's built in and buy some 3rd party software that works on everything, right? Where it doesn't, an Excel spreadsheet will always do the job especially if we export all of our data as XML, right? What the heck... avoid the middle man.

Let's really standardize and store all of our data in a nice simple XML only table. Then we won't even have portability problems with ETL or other communications. After all, it was built with true portability in mind. You don't even have to know what's in the files because it automatically tells you. You and your vendors don't have to talk about what the files will contain at all. It's like that wonderful EDI formatting but simpler.

Ok... I have to go now. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLNightOwl
SQLNightOwl
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 523
I hear the argument "we don't want to be locked in" from the app dev folks - the same ones that aren't afraid to be locked in to their programming language and/or OS. It's a "logic free" argument.

--Paul Hunter
JChrisCompton
JChrisCompton
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 283
I almost flipped the bozo bit before I got to the fourth paragraph.

Now that I've read the whole article, I'm really not clear what your message is.

FWIW,
-Chris C.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search