August 29, 2012 at 8:13 am
Good question but tough......
August 29, 2012 at 8:16 am
Great topic and question. Thanks!
August 29, 2012 at 8:19 am
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.
August 29, 2012 at 8:44 am
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'))
TableName ColumnName is_sparse
Table1 RowID 0
Table1 DateTimeStamp 0
Table1 Col1 1
Table1 Col2 1
Table1 Col3 1
Table1 TblColumnSet 0
Table2 RowID 0
Table2 DateTimeStamp 0
Table2 Col1 0
Table2 Col2 0
Table2 Col3 0
Table2 TblColumnSet 0
August 29, 2012 at 10:14 am
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
August 29, 2012 at 11:06 am
Great question! Thanks, Wayne!
August 29, 2012 at 11:32 am
Nice question. Thanks!
August 29, 2012 at 1:29 pm
Never used sparse columns in my SQL experience, do any developers here use them frequently?
August 29, 2012 at 2:01 pm
August 30, 2012 at 4:44 am
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.
September 2, 2012 at 11:00 am
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
September 2, 2012 at 12:04 pm
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
December 20, 2012 at 8:05 am
Pure chance.
some way of compensation ?
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply