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


Index types 2


Index types 2

Author
Message
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12149
Comments posted to this topic are about the item Index types 2


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34169 Visits: 13270
Great question Hugo, made me do some research :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Steve Hall
Steve Hall
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3366 Visits: 11679
Me too - quite a bit of hunting to get the right answers. Thanks for the interesting question.

Steve Hall
Linkedin
Blog Site
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12149
Thanks, Koen! (EDIT: And Braindonor, who added a reply while I was typing)

I submitted this question quite a long time ago - and I found that I had to spend time to research some of the options again. w00t

BTW, maybe it's a bit early to say anything about the percentages, but at this point I am mainly surprised at:
* 28% wrong answers on the "bitmap on computed" option - I expected this to be lower
* 17% wrong answers on the "nonclustered on a view" option - here, I expected a higher failure rate

The other percentages are (so far) about on par with my expectation.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4701 Visits: 5913
Nice question Hugo, thanks.
Got 3 of the 4 - knew bitmap indexes were available on other DB software, thought it might have been introduced with 2012.
Thought 260 columns for columnstore indexes was a slightly random number so didn't go for that :-)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12149
Gazareth (8/30/2012)
Nice question Hugo, thanks.
Got 3 of the 4 - knew bitmap indexes were available on other DB software, thought it might have been introduced with 2012.
Thought 260 columns for columnstore indexes was a slightly random number so didn't go for that :-)

Thanks, Gazareth!
The 260 number is deliberately chosen to exceed 256, 28 or the number of values in a tinyint. This vlaue is a common boundary value in software. (For example, before SQL Server 2008, nonclustered indexes on a table were identified by a tinyint column with two reserved values, leaving room for 254 other values; the actual maximum number was 250 - I think they simply rounded down to a pretty number). So I picked the number 260 to distinguish those who believe in a maximum of 256 columns per columnstore index from those who believe in a larger or no maximum.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
DugyC
DugyC
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1550 Visits: 779
Thanks for the great question Hugo, got the brain matter working. Had to answer by process of elimination and learned something new on the way. Excellent.

Cheers :-D

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12149
DugyC (8/30/2012)
Had to answer by process of elimination

Thanks, Dugy!

Frankly, I am a bit disappointed that the site moderators included the number of correct answers. I had included a note that there are multiple correct answers without specifying how many, to make it a true test of knowledge - but apprently, including the number of correct answers for questions with more than one answer is now standard practice, applied to all those questions. Sad


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2747 Visits: 2816
Very good question! Thanks.


Sujeet Singh
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3087 Visits: 2255
Thanks for the question Hugo. I should have stretched out before running that hard early in the morning. I think I strained something! :-) I certainly learned something.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
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