Non Clustered Index on Phone column

Question

Non Clustered Index on Phone column

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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: 10248 More actions · Answer 2

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: 1004406 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

David Burrows SSC Guru Points: 65143 More actions · Answer 4

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: 442467 More actions · Answer 5

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: 1004406 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

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: 1004406 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

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

drew.allen SSC Guru Points: 76996 More actions · Answer 9

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