Non Clustered Index on Phone column

  • Lynn Pettis

    SSC Guru

    Points: 442141

    Joe Torre - Monday, August 20, 2018 4:27 PM

    @sunilkmr284,
    If you are joining to other tables frequently by LeadID then I would create a clustered PK on that. Clustering on the phone number may lead to fragmentation and affect performance negatively. As Jeff already stated, a non-clustered index should perform well for singleton searches. What is the cardinality? Run this query and let us know the results:

    SELECT Count(*) TotalCount, Count(DISTINCT Telephone) PhoneCount
    ROM dbo.tbl_Lead;

    out of curiosity, what does having the primary key being the clustered index buy you when joining to other tables?  You could just as well have the primary key being a non-clustered index as well.  It really depends on how the data is being accessed.

  • Joe Torre

    SSChampion

    Points: 10238

    Clustering on a phone number column can introduce fragmentation as inserts are not "in order" and updates can cause page splits.

  • Jeff Moden

    SSC Guru

    Points: 994645

    Joe Torre - Monday, August 20, 2018 6:10 PM

    Clustering on a phone number column can introduce fragmentation as inserts are not "in order" and updates can cause page splits.

    Only if you're caught unaware.  It turns out that things like GUIDs (for  example) have this nice even distribution across the entire domain of rows in the table or index and it's something you can take huge advantage of that you can't even do with ever-increasing "append only" rows and that's go WEEKS and even months without ANY page splits, not even the "good" page splits that plague append-only indexes.

    Unless there's a concentrated campaign, telephone numbers are frequently very close to the even distribution of GUIDs and can take that same advantage.

    The trouble is, people keep screwing up such wonders by using current "Best Practice" methods of index maintenance which, ironically, creates a nasty drug-habit-like Catch-22 that creates the need for defragmentation.

    And, no... updates alone don't cause page splits unless unless you have a page that's 100% full or you have an "expAnsive".

    It all comes down to Fill Factor, when to defrag, and whether to use Reorg or Rebuild... and the current "Best Practice" methods aren't the right combination for any of that.  Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • David Burrows

    SSC Guru

    Points: 64518

    Jeff Moden - Monday, August 20, 2018 7:11 PM

     Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    Cool. Wish I could be there for that :crying:

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Lynn Pettis

    SSC Guru

    Points: 442141

    David Burrows - Tuesday, August 21, 2018 1:49 AM

    Jeff Moden - Monday, August 20, 2018 7:11 PM

     Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    Cool. Wish I could be there for that :crying:

    So do I.

  • Jeff Moden

    SSC Guru

    Points: 994645

    Lynn Pettis - Tuesday, August 21, 2018 9:00 AM

    David Burrows - Tuesday, August 21, 2018 1:49 AM

    Jeff Moden - Monday, August 20, 2018 7:11 PM

     Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    Cool. Wish I could be there for that :crying:

    So do I.

    Thank you both for the vote of confidence! 🙂  I wish both of you could make it because you're both on my bucket list.

    Lynn, I had to cancel Denver because of a problem that I discovered in my presentation that I didn't have an easy fix for but that issue has been resolved.  I'm looking forward to the next SQLSaturday in Colorado Springs to present it there and, hopefully, cross one more important person to meet (you) off of my bucket list in the process.

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182349

    Jeff Moden - Tuesday, August 21, 2018 9:51 AM

    Lynn Pettis - Tuesday, August 21, 2018 9:00 AM

    David Burrows - Tuesday, August 21, 2018 1:49 AM

    Jeff Moden - Monday, August 20, 2018 7:11 PM

     Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    Cool. Wish I could be there for that :crying:

    So do I.

    Thank you both for the vote of confidence! 🙂  I wish both of you could make it because you're both on my bucket list.

    Lynn, I had to cancel Denver because of a problem that I discovered in my presentation that I didn't have an easy fix for but that issue has been resolved.  I'm looking forward to the next SQLSaturday in Colorado Springs to present it there and, hopefully, cross one more important person to meet (you) off of my bucket list in the process.

    Jeff, do you have a fixed date for the presentation?
    😎

  • Jeff Moden

    SSC Guru

    Points: 994645

    Eirikur Eiriksson - Tuesday, August 21, 2018 10:13 AM

    Jeff Moden - Tuesday, August 21, 2018 9:51 AM

    Lynn Pettis - Tuesday, August 21, 2018 9:00 AM

    David Burrows - Tuesday, August 21, 2018 1:49 AM

    Jeff Moden - Monday, August 20, 2018 7:11 PM

     Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    Cool. Wish I could be there for that :crying:

    So do I.

    Thank you both for the vote of confidence! 🙂  I wish both of you could make it because you're both on my bucket list.

    Lynn, I had to cancel Denver because of a problem that I discovered in my presentation that I didn't have an easy fix for but that issue has been resolved.  I'm looking forward to the next SQLSaturday in Colorado Springs to present it there and, hopefully, cross one more important person to meet (you) off of my bucket list in the process.

    Jeff, do you have a fixed date for the presentation?
    😎

    It's not yet been announced but the first "public" presentation will be at the Pittsburgh SQL Saturday on 29 Sep 2018.  http://www.sqlsaturday.com/770/Sessions/SubmittedSessions.aspx

    Ed Wagner and I are also doing a precon there again.  We first gave it in 2016 at Pittsburgh.  Here's the link for that.  33% of the proceeds go to the Pittsburgh Pass Chapter to help them continue to put on great SQL Saturdays.
    https://www.eventbrite.com/e/sql-server-black-arts-high-performance-tools-and-techniques-tickets-49076936438?ref=ecount

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • drew.allen

    SSC Guru

    Points: 76580

    Jeff Moden - Tuesday, August 21, 2018 10:43 AM

    Eirikur Eiriksson - Tuesday, August 21, 2018 10:13 AM

    Jeff Moden - Tuesday, August 21, 2018 9:51 AM

    Lynn Pettis - Tuesday, August 21, 2018 9:00 AM

    David Burrows - Tuesday, August 21, 2018 1:49 AM

    Jeff Moden - Monday, August 20, 2018 7:11 PM

     Heh... I've got a couple of hundred hours in testing to prove all of this and am going to try to summarize it in a 2 hour presentation at the Pittsburgh SQL Saturday this year.

    Cool. Wish I could be there for that :crying:

    So do I.

    Thank you both for the vote of confidence! 🙂  I wish both of you could make it because you're both on my bucket list.

    Lynn, I had to cancel Denver because of a problem that I discovered in my presentation that I didn't have an easy fix for but that issue has been resolved.  I'm looking forward to the next SQLSaturday in Colorado Springs to present it there and, hopefully, cross one more important person to meet (you) off of my bucket list in the process.

    Jeff, do you have a fixed date for the presentation?
    😎

    It's not yet been announced but the first "public" presentation will be at the Pittsburgh SQL Saturday on 29 Sep 2018.  http://www.sqlsaturday.com/770/Sessions/SubmittedSessions.aspx

    Ed Wagner and I are also doing a precon there again.  We first gave it in 2016 at Pittsburgh.  Here's the link for that.  33% of the proceeds go to the Pittsburgh Pass Chapter to help them continue to put on great SQL Saturdays.
    https://www.eventbrite.com/e/sql-server-black-arts-high-performance-tools-and-techniques-tickets-49076936438?ref=ecount

    I'm tempted to attend this.  If only it were a little closer and/or my life was a little less busy.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 9 posts - 16 through 24 (of 24 total)

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