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 2 Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 11:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1352010
Posted Thursday, August 30, 2012 12:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1352020
Posted Thursday, August 30, 2012 1:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 1,541, Visits: 8,212
Me too - quite a bit of hunting to get the right answers. Thanks for the interesting question.

BrainDonor
Linkedin
Blog Site
Post #1352034
Posted Thursday, August 30, 2012 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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.

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
Post #1352035
Posted Thursday, August 30, 2012 2:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 1,946, Visits: 3,210
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
Post #1352059
Posted Thursday, August 30, 2012 3:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1352067
Posted Thursday, August 30, 2012 3:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1352080
Posted Thursday, August 30, 2012 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1352083
Posted Thursday, August 30, 2012 4:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:26 AM
Points: 1,315, Visits: 2,524
Very good question! Thanks.


Sujeet Singh
Post #1352085
Posted Thursday, August 30, 2012 5:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:26 AM
Points: 1,803, Visits: 2,168
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
Post #1352127
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse