Never, Ever Use Clustered Indexes

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Chris.C-977504 (5/31/2012)


    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.

    Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • DEK46656

    SSCrazy

    Points: 2001

    This should have been an April Fool's editorial; well with a little tweaking of content.

    Beer's Law: Absolutum obsoletum
    "if it works it's out-of-date"

  • TravisDBA

    SSCoach

    Points: 15780

    Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Grant Fritchey

    SSC Guru

    Points: 396288

    TravisDBA (5/31/2012)


    Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D

    The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • TravisDBA

    SSCoach

    Points: 15780

    Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.

    Agreed, people believe lots of things, but that doesn't necessarily make them relevant, or the best way to go. Like I have said to many customers in the past without reservation. "There is what you want, and then there is what you end up getting." and my boss backs me on it every time too. Our business is still thriving. As far as the interview goes, you are asking the questions not the interviewee. You can ask anything you want within reason to satisfy your need to assess his/her specific skill set.. 😀

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Dean Cochrane

    SSC Eights!

    Points: 962

    Grant Fritchey (5/31/2012)


    The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.

    I'll echo what Grant says. Misunderstanding and mistrust of databases is widespread in the industry, and many people who should know better treat the relational database as nothing more than a file system with built in backup capability. For such people, the leap to 'vendor independence' is simple. They have no grasp of (or interest in) the complexities of a relational database, and so they have no grasp of any of the issues that 'vendor independence' will bring.

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    TravisDBA (5/31/2012)


    Agreed, people believe lots of things, but that doesn't necessarily make them relevant, or the best way to go.

    If people believe them then it's relevant to address in an editorial. It may be that most people that read this already know it. However, some don't and others can use this to show their boss or developers that it isn't a good idea.

    TravisDBA (5/31/2012)


    As far as the interview goes, you are asking the questions not the interviewee. You can ask anything you want within reason to satisfy your need to assess his/her specific skill set.

    Which doesn't do much to help the community which is what I imagine Grant was hoping for in writing this.

  • TravisDBA

    SSCoach

    Points: 15780

    Dean,

    Misunderstanding and mistrust of "everything" is widespread nowadays anyway. Database structure doesn't have the corner on this. Hmmmm. I'm still not sure what the point is. In my shop for example, we dictate the database structure, not the untrusting customer. 😀

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • patrickmcginnis59

    SSCertifiable

    Points: 6423

    Grant Fritchey (5/31/2012)


    TravisDBA (5/31/2012)


    Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D

    The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.

    I'm one of those I guess, I spend a bit of effort comparing different server features just to stay in tune with how things are handled between different systems. I've dealt with a few systems that also try for a measure of independence from database vendors even to the extent of having in house programming languages, so theres clearly some pretty smart folks out there who believe there are reasons for doing so which you dismissed without comment. Its an interesting topic that maybe deserves a better writeup then you are able to deliver. Oh well, this is a pretty vendor specific forum so I guess it is to be expected.

  • JChrisCompton

    SSCommitted

    Points: 1533

    Grant Fritchey (5/31/2012)


    ...I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    Well you didn't completely fail because that was my best guess. (I reread the article but still wasn't sure - hence my post)

    It is a good point about the foundation of each.

    I hadn't thought about the foundation of SQL Server being clustered vs. the foundation of Oracle being the heap. This is an interesting discussion (though the topic has digressed).

    I learned on Oracle in `92 while still in college, and I have used it often since then, but I haven't done a db design in Oracle... uh, this century?

    So, my follow up question is:

    Can't you still write specific optimized code for both Oracle and SSvr in one program if you have two different DALs with different schemas underneath?

    Yes, you'd need a forked code base for the DALs, but if you're dealing with 'big data' ("big enough data"?) and tight performance constraints then it could be worth the headaches and (huge) costs.

    Maybe that's a dumb question, forgive me as I'm a applications developer (by job category) not a a DBA - though I do all the SSvr db design and query optimizing for my area 🙂

    I do now agree with your premise which I think I can over simplify as "Don't gold plate your code" (the gold plating in this case being the fiction of the generic backend database)

    Thanks, HTH,

    -Chris C.

    edited to add "in one program" to the DAL question

  • jfogel

    SSCarpal Tunnel

    Points: 4131

    patrickmcginnis59 (5/31/2012)


    Grant Fritchey (5/31/2012)


    TravisDBA (5/31/2012)


    Sorry about that. I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    I'm a little confused too Grant . Who really uses "generic' database designs nowadays anyway? Particularly, when the underlying database structure is constantly changing in a fast paced environment. At least mine anyway. This seems to me like something that is already very apparent to most DBA's IMHO anyway. So, Oracle and SQL Server are fundamentally different, Uhhhhh... yes, they are....no kidding! I'm with Chris, I'm not sure what point the message was trying to convey, that we didn't already know.:-D

    The message may have been unclear, again apologies, but you're wrong that this is common knowledge. Tons and tons of developers and plenty of DBAs still believe in not hooking yourself to a single vendor. I interviewed someone just a little over a year ago who lectured me on my technical questions because they were too "Microsoft & SQL Server specific." His argument was that only lowest common denominator SQL should be used so that it's easier to port between database systems. People really do still believe this.

    I'm one of those I guess, I spend a bit of effort comparing different server features just to stay in tune with how things are handled between different systems. I've dealt with a few systems that also try for a measure of independence from database vendors even to the extent of having in house programming languages, so theres clearly some pretty smart folks out there who believe there are reasons for doing so which you dismissed without comment. Its an interesting topic that maybe deserves a better writeup then you are able to deliver. Oh well, this is a pretty vendor specific forum so I guess it is to be expected.

    True. We sell our software and not a particular vendors database. Our app had to play nice with Oracle or SQL to give the buyer a choice. At the time Oracle was the major player and SQL Server was at 6.5 and not really trusted. Then things changed and the majority of clients chose the SQL Server back end mainly because of price. Have a clustered or heap index, our app doesn't care as long as the index exists and if it does not it will create it using vendor specific syntax. Our app did not try to use say ANSI only SQL, the "mode" is detected and either Oracle or SQL Server specific code is executed within executables. For reports we had two sets of procedures, one with Oracle syntax and one with SQL Server so we did not rely on one set of code to be all things to all people. Was that a lot of work? Yes but it avoided much of what is the root of this topic.

    Cheers

  • LadyRuna

    SSCrazy

    Points: 2174

    I believe the rule regarding indexes (clustered or not) is the infamous "It Depends".... because your choice of indexing style really does depend upon how you're planning to access your data and what you're going to do with it.

    It always boggles my mind when someone calls a meeting and starts by saying, "I think we should set up a rule that we always use ___________ in all of our stored procedures/table designs/ etc". Usually this is because the guy just read an article that made some sweeping generalization and he decided that that should be enforced throughout our code. As soon as I hear that I think "DANGER!!" because I know that there are exceptions where that rule should not be applied.

  • TravisDBA

    SSCoach

    Points: 15780

    LadyRuna (5/31/2012)


    I believe the rule regarding indexes (clustered or not) is the infamous "It Depends".... because your choice of indexing style really does depend upon how you're planning to access your data and what you're going to do with it.

    It always boggles my mind when someone calls a meeting and starts by saying, "I think we should set up a rule that we always use ___________ in all of our stored procedures/table designs/ etc". Usually this is because the guy just read an article that made some sweeping generalization and he decided that that should be enforced throughout our code. As soon as I hear that I think "DANGER!!" because I know that there are exceptions where that rule should not be applied.

    I absolutely agree, people have to be careful using wide sweeping generalizing terms like "widespread". Even me.:-D

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Your Name Here

    SSCrazy

    Points: 2341

    Hey, Grant!

    Excellent topic. I get the jab at "one size fits all just doesn't work". I haven't had a ton of experience with multi-platform apps ("Oracle, SQL, MySQL, our app will work on any platform") but the few I have worked with were pretty much epic fail. Yeah, "it worked" sort of like using a hammer "works" to put in screws.

  • Grant Fritchey

    SSC Guru

    Points: 396288

    Chris.C-977504 (5/31/2012)


    Grant Fritchey (5/31/2012)


    ...I was trying to make the point that "generic" database designs aren't really possible due to the nature of the underlying infrastructure. Clearly failed.

    Well you didn't completely fail because that was my best guess. (I reread the article but still wasn't sure - hence my post)

    It is a good point about the foundation of each.

    I hadn't thought about the foundation of SQL Server being clustered vs. the foundation of Oracle being the heap. This is an interesting discussion (though the topic has digressed).

    I learned on Oracle in `92 while still in college, and I have used it often since then, but I haven't done a db design in Oracle... uh, this century?

    So, my follow up question is:

    Can't you still write specific optimized code for both Oracle and SSvr in one program if you have two different DALs with different schemas underneath?

    Yes, you'd need a forked code base for the DALs, but if you're dealing with 'big data' ("big enough data"?) and tight performance constraints then it could be worth the headaches and (huge) costs.

    Maybe that's a dumb question, forgive me as I'm a applications developer (by job category) not a a DBA - though I do all the SSvr db design and query optimizing for my area 🙂

    I do now agree with your premise which I think I can over simplify as "Don't gold plate your code" (the gold plating in this case being the fiction of the generic backend database)

    Thanks, HTH,

    -Chris C.

    edited to add "in one program" to the DAL question

    No such thing as a dumb question.

    Yeah, if you need performance, you're going to have to fork the code base in some manner. You can get acceptable performance using lowest common denominator approaches, absolutely. But to make code hum, in any platform, you'll need to design for that platform.

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 16 through 30 (of 65 total)

You must be logged in to reply to this topic. Login to reply