March 16, 2015 at 12:02 pm
In the correct answer choice section, it should have "Clustered columnstore indexes were not available until SQL Server 2014" also as an additional choice apart of the 2 mentioned, I think. No?
Thanks.
March 17, 2015 at 4:30 am
Stewart "Arturius" Campbell (3/16/2015)
Koen Verbeeck (3/16/2015)
Carlo Romagnano (3/16/2015)
Hany Helmy (3/15/2015)
Gr8 question, thanx 🙂Did anyone tried this feature before? And is there any actual performance gained?
I tried just a little. The answer is as usual "depends".
If you query the whole table, there's a gain.
The point of columnstore indexes is that there should be a tremendous gain when you only read a few columns, because the columnstore indexes will only read those indexes, while traditional row store tables/indexes have to read all rows/pages and filter the unnecessary columns out.
+1
Have used nonclustered columnstore indexes before, with tremendous performance gains; agree whoeheartedly with Koen's statement.
Thanx 4 the info, but as far as I know nonclustered columnstore indexes cannot be updated in SQL 2012 (even in 2014 I guess) unless you perform a silly workaround of dropping the index, perform the DML operation on the table, then rebuild/create the index again! Or to use the switch/swap partitions method and both ways are just not practical for a 24/7 mission critical system running OLTP on some huge database.
I think it is best suited for the enterprise data warehouse environments not OLTP.
https://msdn.microsoft.com/en-us/library/gg492088(v=sql.110).aspx#Update
Thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
March 17, 2015 at 4:35 am
Hany Helmy (3/17/2015)
Stewart "Arturius" Campbell (3/16/2015)
Koen Verbeeck (3/16/2015)
Carlo Romagnano (3/16/2015)
Hany Helmy (3/15/2015)
Gr8 question, thanx 🙂Did anyone tried this feature before? And is there any actual performance gained?
I tried just a little. The answer is as usual "depends".
If you query the whole table, there's a gain.
The point of columnstore indexes is that there should be a tremendous gain when you only read a few columns, because the columnstore indexes will only read those indexes, while traditional row store tables/indexes have to read all rows/pages and filter the unnecessary columns out.
+1
Have used nonclustered columnstore indexes before, with tremendous performance gains; agree whoeheartedly with Koen's statement.
Thanx 4 the info, but as far as I know nonclustered columnstore indexes cannot be updated in SQL 2012 (even in 2014 I guess) unless you perform a silly workaround of dropping the index, perform the DML operation on the table, then rebuild/create the index again! Or to use the switch/swap partitions method and both ways are just not practical for a 24/7 mission critical system running OLTP on some huge database.
I think it is best suited for the enterprise data warehouse environments not OLTP.
https://msdn.microsoft.com/en-us/library/gg492088(v=sql.110).aspx#Update
Columnstore indexes are meant for the enterprise DWH and not for OLTP, that's exactly the point.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 17, 2015 at 5:28 am
Hany Helmy (3/15/2015)
Gr8 question, thanx 🙂Did anyone tried this feature before? And is there any actual performance gained?
Yes. See http://www.sqlservercentral.com/stairway/121631/[/url]
(Bit of work life balance issues at the moment, hope to get the next part finished soon)
March 17, 2015 at 5:38 am
I'm on SQL 2008, but will definitely be checking out columnstore indexes when we upgrade one of our servers. It's scheduled for this year.
March 17, 2015 at 6:58 am
Thanks for the question.
March 17, 2015 at 8:22 am
Koen Verbeeck (3/16/2015)
Can only create nonclustered columnstore index in SQL Server 2012
This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂
Anyway, I got it correct as only one single correct answer seemed a bit few.
SO, The answer is wrong!
"Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.
Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx
There is only one single correct answer!
March 17, 2015 at 8:44 am
lcajui (3/17/2015)
Koen Verbeeck (3/16/2015)
Can only create nonclustered columnstore index in SQL Server 2012
This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂
Anyway, I got it correct as only one single correct answer seemed a bit few.
SO, The answer is wrong!
"Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.
Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx
There is only one single correct answer!
+1. It is frustrating to try to learn a new topic, then discover you are "wrong" when you answered the question correctly.
It's amazing to read through questions day after day and read about the incredibly subtle (and often significant) differences in SQL functioning, then to have so many replies simply gloss over and accept an obvious mistake without a word.
But in the end, none of us is perfect and I did learn something.
March 17, 2015 at 11:48 am
It was my intention that this be a correct answer. I can see how it could be interpreted the way you and others interpreted it. Maybe it would have been clearer, or at least the way I meant for it to be, if it was worded like this: "In SQL Server 2012, can only create nonclustered columnstore index"
March 17, 2015 at 8:32 pm
RLilj33 (3/17/2015)
lcajui (3/17/2015)
Koen Verbeeck (3/16/2015)
Can only create nonclustered columnstore index in SQL Server 2012
This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂
Anyway, I got it correct as only one single correct answer seemed a bit few.
SO, The answer is wrong!
"Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.
Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx
There is only one single correct answer!
+1. It is frustrating to try to learn a new topic, then discover you are "wrong" when you answered the question correctly.
It's amazing to read through questions day after day and read about the incredibly subtle (and often significant) differences in SQL functioning, then to have so many replies simply gloss over and accept an obvious mistake without a word.
But in the end, none of us is perfect and I did learn something.
I find it extremely frustrating to see people claiming that there's only one possible meaning to a statement which is plainly and clearly ambiguous, particularly since (a) it is a well-known property of the English language that it is extremely easy to write such obviously ambiguous statements and (b) it's a well known property of every other natural language too and (c) it is extremely common for such ambiguous statements to be written unintentionally.
Tom
March 18, 2015 at 6:43 am
TomThomson (3/17/2015)
RLilj33 (3/17/2015)
lcajui (3/17/2015)
Koen Verbeeck (3/16/2015)
Can only create nonclustered columnstore index in SQL Server 2012
This makes it seem like you could only create nonclustered columnstore indexes in SQL 2012, but not in 2014. At least how I interpret it 🙂
Anyway, I got it correct as only one single correct answer seemed a bit few.
SO, The answer is wrong!
"Can only create nonclustered columnstore index in SQL Server 2012" says CAN ONLY CREATE in SQL Server 2012, but can also be created in SQL Server 2014.
Reference: https://msdn.microsoft.com/en-us/library/gg492088.aspx
There is only one single correct answer!
+1. It is frustrating to try to learn a new topic, then discover you are "wrong" when you answered the question correctly.
It's amazing to read through questions day after day and read about the incredibly subtle (and often significant) differences in SQL functioning, then to have so many replies simply gloss over and accept an obvious mistake without a word.
But in the end, none of us is perfect and I did learn something.
I find it extremely frustrating to see people claiming that there's only one possible meaning to a statement which is plainly and clearly ambiguous, particularly since (a) it is a well-known property of the English language that it is extremely easy to write such obviously ambiguous statements and (b) it's a well known property of every other natural language too and (c) it is extremely common for such ambiguous statements to be written unintentionally.
Ok, so we agree that not checking this answer is also correct. Then we can not be wrong being right 😀
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply