Using Sparse Columns and Sparse Column Sets

Question

Using Sparse Columns and Sparse Column Sets

WayneS

SSC Guru

Points: 95460
More actions
September 4, 2012 at 9:02 pm

#263812

Comments posted to this topic are about the item Using Sparse Columns and Sparse Column Sets
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2
September 5, 2012 at 1:17 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Nice question Wayne..

The only exception when insert will fail is when one tries to insert in a column (col1/col2/col3) and TblColumnSet (COLUMN_SET) in one go

Cheers 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 2

nice question . get to learn about the column sets.

thanks

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Great question Wayne.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 4

Thank you. Nice.

(same as this http://www.sqlservercentral.com/Forums/FindPost1351393.aspx with little details change. Got a impression at first that this question was repeated before and then I had to compare between these two 😛 )

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Erav SSC-Addicted Points: 411 More actions · Answer 5

Erav

SSC-Addicted

Points: 411

September 5, 2012 at 1:11 am

#1533646

Nice Question Wayne. Thanks.:-)

-Erav

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Nice question.

Raghavendra Mudugal (9/5/2012)
same as this http://www.sqlservercentral.com/Forums/FindPost1351393.aspx with little details change. Got a impression at first that this question was repeated before and then I had to compare between these two 😛 )

Well, not really "same", it's a very diffefrent question.

But certainly anyone who had seen the first one would be aware that the last insert doesn't cause an error, and deducing that three 2-row inserts and one 1-row insert will between them insert 7 rows is not very difficult. So it might have been better if these two questions had appeared in the opposite order.

Tom

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

L' Eomot Inversé (9/5/2012)
Nice question.
Raghavendra Mudugal (9/5/2012)
same as this http://www.sqlservercentral.com/Forums/FindPost1351393.aspx with little details change. Got a impression at first that this question was repeated before and then I had to compare between these two 😛 )
Well, not really "same", it's a very diffefrent question.

Not sure what I am missing here, but the both questions has the same table structure and then same data to inserts, in the end the previous question uses the SELECT INTO where the data is been copied to the new table and objective of the question completely relays on the table2. Here in this question it is just the first part of the previous question, where the new table creation is been avoided and then the objective of the question here mainly focuses on the XML column set which again can be seen working properly with no errors in the previous one.

I am not sure how it can be very different.

thank you for your reply.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

Raghavendra Mudugal (9/5/2012)
L' Eomot Inversé (9/5/2012)
Nice question.
Raghavendra Mudugal (9/5/2012)
same as this http://www.sqlservercentral.com/Forums/FindPost1351393.aspx with little details change. Got a impression at first that this question was repeated before and then I had to compare between these two 😛 )
Well, not really "same", it's a very diffefrent question.
Not sure what I am missing here, but the both questions has the same table structure and then same data to inserts, in the end the previous question uses the SELECT INTO where the data is been copied to the new table and objective of the question completely relays on the table2. Here in this question it is just the first part of the previous question, where the new table creation is been avoided and then the objective of the question here mainly focuses on the XML column set which again can be seen working properly with no errors in the previous one.
I am not sure how it can be very different.
thank you for your reply.

This question (the second, chronologically) basically asked which of the insert formats used would fail and which would work.

The other question (the first, chronologically) used these insert formats to set up sample data and than asked what the effect of a certain statement on that sample data would be. That question is completely different. The sample data could have been given in a variety of other formats without affecting the question.

So yes, they are different questions. And I think they are both good by themselves. But in combination and in the order they are published here, this (second) question is weak: the same methods of inserting were used for the first question and the rest of that question implied that they would run error-free, so anyone who looked more than casually at that question is bound to get this one right. Had the order been reversed, then both questions would have been good.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 9

Hugo Kornelis (9/5/2012)
This question (the second, chronologically) basically asked which of the insert formats used would fail and which would work.

Yes, from the previous post it has been proved that that insert data been provided here for this question also gets succeeded like the previous one.

The other question (the first, chronologically) used these insert formats to set up sample data and than asked what the effect of a certain statement on that sample data would be. That question is completely different. The sample data could have been given in a variety of other formats without affecting the question.

I apologize if my wording were not consistent.

Now I see the difference here... when i say "same" i didn;t mean the objective of the both are same, i meant the question itself as there is no difference apart from the final select statement where the SELECT INTO is been removed and this question is been changed with some difference in the detail.

So yes, they are different questions. And I think they are both good by themselves. But in combination and in the order they are published here, this (second) question is weak: the same methods of inserting were used for the first question and the rest of that question implied that they would run error-free, so anyone who looked more than casually at that question is bound to get this one right. Had the order been reversed, then both questions would have been good.

Yes, I agree if you are focusing on the objective.

Thank you for your reply. (it is always feels good to interact with experts, ~~not to argue~~) 🙂

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 10

Nice question. I am learning a lot more about SPARSE columns!

sestell1 SSChampion Points: 10230 More actions · Answer 11

sestell1

SSChampion

Points: 10230

September 5, 2012 at 6:51 am

#1533770

Very interesting, thanks for the question Waynes!

Stephen_W_Dodd SSCommitted Points: 1628 More actions · Answer 12

Stephen_W_Dodd

SSCommitted

Points: 1628

September 5, 2012 at 7:02 am

#1533777

A useful, illustrative question. Thank you, Wayne!

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 13

In researching SPARSE columns, I cannot quite see their value. I'm not that experienced with Sql Server, I wonder how often they are used? Seems examples I have seen look like better database design could have eliminated their necessity:unsure:.