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»»

Using Sparse Columns and Sparse Column Sets Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 3:56 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: Yesterday @ 1:02 PM
Points: 998, Visits: 2,280
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.
Post #1354418
Posted Wednesday, September 5, 2012 4:13 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
Nice question. I am learning a lot more about SPARSE columns!
Post #1354427
Posted Wednesday, September 5, 2012 6:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 1,359, Visits: 2,365
Very interesting, thanks for the question Waynes!
Post #1354489
Posted Wednesday, September 5, 2012 7:02 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: Friday, October 18, 2013 6:12 AM
Points: 952, Visits: 552
A useful, illustrative question. Thank you, Wayne!
Post #1354496
Posted Wednesday, September 5, 2012 7:27 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
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.
Post #1354523
Posted Wednesday, September 5, 2012 7:30 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: Friday, September 12, 2014 10:36 AM
Points: 691, Visits: 292
Good Question...!

Best,
Naseer Ahmad
SQL Server DBA
Post #1354526
Posted Wednesday, September 5, 2012 7:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 2, 2014 6:57 PM
Points: 2,315, Visits: 1,362
Good question Wayne learned something about XML Column_Set.
Post #1354533
Posted Wednesday, September 5, 2012 7:35 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: Yesterday @ 11:29 AM
Points: 3,354, Visits: 2,001
Nice question. I only got this one right because there was another sparse column question recently that I had to do research on.

I would be interested in seeing a SSC poll on the usage of sparse columns and sets. I have yet to run across any examples of them in production.
Post #1354534
Posted Wednesday, September 5, 2012 7:39 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
KWymore (9/5/2012)
Nice question. I only got this one right because there was another sparse column question recently that I had to do research on.

I would be interested in seeing a SSC poll on the usage of sparse columns and sets. I have yet to run across any examples of them in production.


I would also like to see how much they are used!!
Post #1354537
Posted Wednesday, September 5, 2012 8:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
(Bob Brown) (9/5/2012)
KWymore (9/5/2012)
Nice question. I only got this one right because there was another sparse column question recently that I had to do research on.

I would be interested in seeing a SSC poll on the usage of sparse columns and sets. I have yet to run across any examples of them in production.


I would also like to see how much they are used!!


I guess that one of the easiest examples to give is... Sharepoint.

You can upload a document into it. You can specify various properties of that document. For each type of document, only some of the properties are relevant. As you add more properties to handle more document types, the number of irrelevant columns just increase for each type of document. With potentially an unlimited number to the types of documents, each document will have irrelevant properties... yet they are still being dragged around in the row, and they are taking up space. Unless it's a sparse column, which only takes up space if it has non-null data.

Does this help?


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1354572
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse