Using Sparse Columns with SELECT ... INTO

Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

wdolby Old Hand Points: 376 More actions · Answer 4

udayroy15 (8/29/2012)
Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

I was just looking into this.

http://msdn.microsoft.com/en-us/library/cc280604.aspx

"Catalog views for a table that has sparse columns are the same as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined."

"Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table."

SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName, is_sparse

FROM SYS.COLUMNS

WHERE object_id IN (object_id('Table1'), object_id('Table2'))

TableNameColumnNameis_sparse

Table1 RowID 0

Table1 DateTimeStamp0

Table1 Col1 1

Table1 Col2 1

Table1 Col3 1

Table1 TblColumnSet0

Table2 RowID 0

Table2 DateTimeStamp0

Table2 Col1 0

Table2 Col2 0

Table2 Col3 0

Table2 TblColumnSet0

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Nice question - makes you think.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Revenant SSC-Forever Points: 42467 More actions · Answer 6

Revenant

SSC-Forever

Points: 42467

August 29, 2012 at 11:06 am

#1531524

Great question! Thanks, Wayne!

Ken SSCoach Points: 17191 More actions · Answer 7

Ken

SSCoach

Points: 17191

August 29, 2012 at 11:32 am

#1531536

Nice question. Thanks!

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 8

Never used sparse columns in my SQL experience, do any developers here use them frequently?

EL Jerry SSCertifiable Points: 7063 More actions · Answer 9

Great question, Wayne. Made me think and research a lot.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

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

churlbut (8/29/2012)
Never used sparse columns in my SQL experience, do any developers here use them frequently?

I have never used them and am not sure I understand their true applicability.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Good question.

I find it rather surprising that 19% of people to date think the column name doesn't carry over, and 17% think the data type doesn't carry over - I would expect more than 90% of people to know that both those properties are transferred to the new table.

30% of people think that nullability doesn't carry over to the new table, and that's not just surprising it's appalling.

Only 18% of people thought that sparseness carried only, so about as many people understand that sparseness is not copied as understand that the column names are or that data types are. Seems quite bizarre! And about twice as many get sparseness right than nullability, which seems even bizarrer.

Tom

WayneS SSC Guru Points: 95460 More actions · Answer 12

Thanks Tom,

I have to agree with your assessment. I would have expected the name, data type and null-ability to all be >95%.

I actually felt that this question was so easy, that I asked Steve to not show the number of correct answers... if it showed 3, I felt that everyone would just check those 3. (My thinking was that they would apply deductive reasoning, instead of thinking about the question. I wanted more thought to go into it.)

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

jfgoude SSCrazy Points: 2586 More actions · Answer 13

jfgoude

SSCrazy

Points: 2586

December 20, 2012 at 8:05 am

#1570557

Pure chance.

some way of compensation ?