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


Index types 1


Index types 1

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27567 Visits: 7551
This one really got the grey matter exercising this morning.
Thanks, Hugo

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
(Bob Brown)
(Bob Brown)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1227 Visits: 1145
Too much for me to handle on Monday morning. Nice question.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79602 Visits: 14975
I over thought this one, and knew I was doing it, but still did it anyway. My thinking was that, while you do not have to define a clustered index as unique, SQL Server does add a uniqufier, thus I eliminated non-unique clustered index. See what I mean about over-thinking?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Thomas Abraham
Thomas Abraham
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5019 Visits: 2256
BrainDonor (8/6/2012)
The detailed disclaimer made me laugh - trying to pre-empt any complaints?


I don't blame him. I've decided I'll have to retain an attorney before I do another QotD. :-)

Thanks, Hugo, for the question. But it was a bit too much for me for a Monday. I don't use non-unique clustered indexes, so I got lost looking for the right answer.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
sestell1
sestell1
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4943 Visits: 3584
Thanks for the question Hugo!
I had to research the XML indexes again... not something I use.
Dana Medley
Dana Medley
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3758 Visits: 1707
Thanks for the great question hugo. Got the first two, but the last one I was unsure of. Definitely learned something this morning.



Everything is awesome!
mtassin
mtassin
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10734 Visits: 72521
Put me in the group of learners...

Knew the first two. Have never played with either XML or SPATIAL data types as of yet (curious about them, haven't had a problem to solve with them), so my practical experience was missing.

Ah well... one day.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
D.Oc
D.Oc
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1401 Visits: 6480
Good question,

I got it wrong over XML part.

-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."

sknox
sknox
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5635 Visits: 3036
Jack Corbett (8/6/2012)
I over thought this one, and knew I was doing it, but still did it anyway. My thinking was that, while you do not have to define a clustered index as unique, SQL Server does add a uniqufier, thus I eliminated non-unique clustered index. See what I mean about over-thinking?


I was wondering why, at the time I answered, 45% of respondents didn't think a non-unique clustered index was possible. I'll give them the benefit of the doubt, and chalk it up to them all thinking as much as you. :-)

Since the clustered index is referenced by other indexes as the row identifier (since the b-tree is organized by the clustered index and therefore that would be the most efficient reference to use) a uniquifier is necessary on a "non-unique" clustered index. But since that uniquifier is not directly accessible through DML, the clustered index appears to the client as non-unique.

Hugo, I can see why you didn't want the "choose 3" text there -- for many of us it takes a question with 31 possible answers (2^5 -1 since I believe choosing 0 answers is not an option in this system down to a choice of 3 (if you got the non-unique clustered index) or 6 (if you weren't sure of the non-unique clustered index.)

But I for one am glad it was there, as I haven't done much with XML or Spatial data as yet, so I had to guess for the last one. I got it right and learned something today!
m mcdonald
m mcdonald
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2226 Visits: 1708
Nice. I found this link very helpful...

http://msdn.microsoft.com/en-us/library/ms175049.aspx

Cheers
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