Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Index types 1 Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 3:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:08 AM
Points: 4,160, Visits: 5,557
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”
Post #1340460
Posted Monday, August 6, 2012 5:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Too much for me to handle on Monday morning. Nice question.
Post #1340519
Posted Monday, August 6, 2012 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
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

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
Post #1340538
Posted Monday, August 6, 2012 6:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:43 AM
Points: 1,925, Visits: 2,205
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
Post #1340545
Posted Monday, August 6, 2012 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 1,423, Visits: 2,446
Thanks for the question Hugo!
I had to research the XML indexes again... not something I use.
Post #1340565
Posted Monday, August 6, 2012 7:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:21 AM
Points: 1,991, Visits: 1,496
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!
Post #1340576
Posted Monday, August 6, 2012 7:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Post #1340592
Posted Monday, August 6, 2012 7:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 27, 2014 1:56 AM
Points: 998, Visits: 6,478
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."


Post #1340599
Posted Monday, August 6, 2012 7:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 1,383, Visits: 1,754
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!
Post #1340609
Posted Monday, August 6, 2012 7:52 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 1,454, Visits: 1,407
Nice. I found this link very helpful...

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

Cheers
Post #1340618
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse