SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Using Sparse Columns with SELECT ... INTO / Latest Posts

RE: Using Sparse Columns with SELECT ... INTO

jfgoude — Thu, 20 Dec 2012 08:05:57 GMT

Pure chance.some way of compensation ?

RE: Using Sparse Columns with SELECT ... INTO

WayneS — Sun, 02 Sep 2012 12:04:06 GMT

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.)

RE: Using Sparse Columns with SELECT ... INTO

L' Eomot Inversé — Sun, 02 Sep 2012 11:00:18 GMT

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.

RE: Using Sparse Columns with SELECT ... INTO

(Bob Brown) — Thu, 30 Aug 2012 04:44:48 GMT

[quote][b]churlbut (8/29/2012)[/b][hr]Never used sparse columns in my SQL experience, do any developers here use them frequently?[/quote]I have never used them and am not sure I understand their true applicability.

RE: Using Sparse Columns with SELECT ... INTO

EL Jerry — Wed, 29 Aug 2012 14:01:02 GMT

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

RE: Using Sparse Columns with SELECT ... INTO

churlbut — Wed, 29 Aug 2012 13:29:11 GMT

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

RE: Using Sparse Columns with SELECT ... INTO

KWymore — Wed, 29 Aug 2012 11:32:45 GMT

Nice question. Thanks!

RE: Using Sparse Columns with SELECT ... INTO

Revenant — Wed, 29 Aug 2012 11:06:52 GMT

Great question! Thanks, Wayne!

RE: Using Sparse Columns with SELECT ... INTO

SQLRNNR — Wed, 29 Aug 2012 10:14:29 GMT

Nice question - makes you think.

RE: Using Sparse Columns with SELECT ... INTO

wdolby — Wed, 29 Aug 2012 08:44:30 GMT

[quote][b]udayroy15 (8/29/2012)[/b][hr]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.[/quote]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_sparseFROM SYS.COLUMNS WHERE object_id IN (object_id('Table1'), object_id('Table2'))TableName ColumnName is_sparseTable1 RowID 0Table1 DateTimeStamp 0Table1 Col1 1Table1 Col2 1Table1 Col3 1Table1 TblColumnSet 0Table2 RowID 0Table2 DateTimeStamp 0Table2 Col1 0Table2 Col2 0Table2 Col3 0Table2 TblColumnSet 0

RE: Using Sparse Columns with SELECT ... INTO

udayroy15 — Wed, 29 Aug 2012 08:19:36 GMT

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.

RE: Using Sparse Columns with SELECT ... INTO

Cliff Jones — Wed, 29 Aug 2012 08:16:22 GMT

Great topic and question. Thanks!

RE: Using Sparse Columns with SELECT ... INTO

udayroy15 — Wed, 29 Aug 2012 08:13:57 GMT

Good question but tough......

RE: Using Sparse Columns with SELECT ... INTO

Ernie Schlangen — Wed, 29 Aug 2012 07:38:23 GMT

Thanks for the quick answer! I suspected that I would get an answer here before I had time to try to find it myself!:-D

RE: Using Sparse Columns with SELECT ... INTO

mtassin — Wed, 29 Aug 2012 07:35:48 GMT

[quote][b]Ernie Schlangen (8/29/2012)[/b][hr]From http://msdn.microsoft.com/en-us/library/ms174979.aspx:[quote]SPARSEIndicates that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL. For additional restrictions and more information about sparse columns, see Use Sparse Columns.[/quote]If a sparse column cannot be designated as NOT NULL, is there a NULL/NOT NULL property to be transferred or does making the column SPARSE automatically make the column NULLable? Or does a SPARSE column have a NULL property that just cannot be changed to NOT NULL? Just wondering... :unsure:[/quote]Run Wayne's code... aftewards do sp_help Table1 to get the structure of the table back. There's still a Nullable property.I just never realized the Nullability of a column transferred with INTO. That's neat, just not something I ever worried about because I generally use INTO #temptables, not to make new ones, and as I"m normally dumping partial contents of tables into the #temp, the values are what I'm normally more interested in.

RE: Using Sparse Columns with SELECT ... INTO

Ernie Schlangen — Wed, 29 Aug 2012 07:24:58 GMT

From http://msdn.microsoft.com/en-us/library/ms174979.aspx:[quote]SPARSEIndicates that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL. For additional restrictions and more information about sparse columns, see Use Sparse Columns.[/quote]If a sparse column cannot be designated as NOT NULL, is there a NULL/NOT NULL property to be transferred or does making the column SPARSE automatically make the column NULLable? Or does a SPARSE column have a NULL property that just cannot be changed to NOT NULL? Just wondering... :unsure:

RE: Using Sparse Columns with SELECT ... INTO

Sean Lange — Wed, 29 Aug 2012 07:19:55 GMT

Great question Wayne.

RE: Using Sparse Columns with SELECT ... INTO

sestell1 — Wed, 29 Aug 2012 06:40:41 GMT

Nice question.

RE: Using Sparse Columns with SELECT ... INTO

(Bob Brown) — Wed, 29 Aug 2012 06:10:35 GMT

This was a great question. Had to do a lot of research, since I was not familiar with SPARSE, to get this one right.

RE: Using Sparse Columns with SELECT ... INTO

Divine Flame — Wed, 29 Aug 2012 06:09:59 GMT

Good question. Thanks.

RE: Using Sparse Columns with SELECT ... INTO

Thomas Abraham — Wed, 29 Aug 2012 05:16:32 GMT

Thanks for the question Wayne! I don't use XML in my current work, so I had to do a bit of guessing. Went with what seemed reasonable and got it right.

RE: Using Sparse Columns with SELECT ... INTO

Paul White — Wed, 29 Aug 2012 05:09:50 GMT

Good question, Wayne.

RE: Using Sparse Columns with SELECT ... INTO

DugyC — Wed, 29 Aug 2012 03:29:30 GMT

Today is a good day for an education.Thanks for the great question, learned something new.

RE: Using Sparse Columns with SELECT ... INTO

arthurolcot — Wed, 29 Aug 2012 01:41:17 GMT

Good question. thanks.

RE: Using Sparse Columns with SELECT ... INTO

Lokesh Vij — Wed, 29 Aug 2012 01:00:18 GMT

Greate question Wayne..got to learn something new today :-)

RE: Using Sparse Columns with SELECT ... INTO

Koen Verbeeck — Tue, 28 Aug 2012 23:57:25 GMT

Great question Wayne, thanks.

RE: Using Sparse Columns with SELECT ... INTO

MissTippsInOz — Tue, 28 Aug 2012 23:37:38 GMT

Hmm...an odd thing just happened. I know that I selected the correct answers for this question but then the response was that I'd answered it incorrectly and gave an explanation why one of the answers I didn't select was wrong. Maybe I've finally gone crazy - it had to happen someday.:w00t:Great question anyway - Thanks!

Using Sparse Columns with SELECT ... INTO

WayneS — Tue, 28 Aug 2012 23:35:47 GMT

Comments posted to this topic are about the item [B]Using Sparse Columns with SELECT ... INTO[/B]