Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQLServerCentral.com » Editorials » Never, Ever Use Clustered Indexes
Add to briefcase
65 posts, Page 1 of 7
12345»»»

Never, Ever Use Clustered Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Grant Fritchey
Posted Wednesday, May 30, 2012 10:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 13,375, Visits: 25,157
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1308736
Abrar Ahmad_
Posted Wednesday, May 30, 2012 11:03 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 5:49 AM
Points: 202, Visits: 1,043
So.... Never, Ever Use clustered indexes ... while working with oracle

just kidding !!! .
Post #1308740
IceDread
Posted Thursday, May 31, 2012 12:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 16, 2012 3:47 AM
Points: 290, Visits: 988
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.
Post #1308761
Alex Fekken
Posted Thursday, May 31, 2012 1:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 195, Visits: 444
"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?
Post #1308766
P Jones
Posted Thursday, May 31, 2012 1:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:35 AM
Points: 515, Visits: 1,016
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!
Post #1308770
Grant Fritchey
Posted Thursday, May 31, 2012 3:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:58 AM
Points: 13,375, Visits: 25,157
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1308831
Ian Elliott
Posted Thursday, May 31, 2012 4:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 6:40 AM
Points: 224, Visits: 636
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 ) 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.
Post #1308859
Jeff Moden
Posted Thursday, May 31, 2012 4:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784
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.

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1308881
SQLNightOwl
Posted Thursday, May 31, 2012 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:55 PM
Points: 179, Visits: 391
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
Post #1308933
Chris.C-977504
Posted Thursday, May 31, 2012 6:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:10 PM
Points: 349, Visits: 256
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.
Post #1308941
« Prev Topic | Next Topic »

Add to briefcase
65 posts, Page 1 of 7
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.