Using Sparse Columns with SELECT ... INTO

Question

Using Sparse Columns with SELECT ... INTO

WayneS

SSC Guru

Points: 95460
More actions
August 28, 2012 at 11:35 pm

#263359

Comments posted to this topic are about the item Using Sparse Columns with SELECT ... INTO
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

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

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

MissTippsInOz Hall of Fame Points: 3160 More actions · Answer 1

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!

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

Great question Wayne, thanks.

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

Greate question Wayne..got to learn something new today 🙂

~ 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

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 4

Arthur Olcot

SSCertifiable

Points: 6639

August 29, 2012 at 1:41 am

#1531240

Good question. thanks.

DugyC Hall of Fame Points: 3804 More actions · Answer 5

Today is a good day for an education.

Thanks for the great question, learned something new.

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 6

Good question, Wayne.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Thomas Abraham SSChampion Points: 10761 More actions · Answer 7

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.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Divine Flame SSCoach Points: 15943 More actions · Answer 8

Divine Flame

SSCoach

Points: 15943

August 29, 2012 at 6:09 am

#1531326

Good question. Thanks.

Sujeet Singh

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

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

sestell1 SSChampion Points: 10230 More actions · Answer 10

sestell1

SSChampion

Points: 10230

August 29, 2012 at 6:40 am

#1531351

Nice question.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Great question Wayne.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ernie Schlangen SSCrazy Points: 2382 More actions · Answer 12

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

SPARSE
Indicates 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.

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:

mtassin SSC-Insane Points: 23099 More actions · Answer 13

Ernie Schlangen (8/29/2012)
From http://msdn.microsoft.com/en-us/library/ms174979.aspx:
SPARSE
Indicates 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.
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:

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Ernie Schlangen SSCrazy Points: 2382 More actions · Answer 14

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