Kalen Delaney : sparse columnshttp://sqlblog.com/blogs/kalen_delaney/archive/tags/sparse+columns/default.aspxTags: sparse columnsenCommunityServer 2.1 SP2 (Build: 61129.1)Geek City: 24Hours of PASS Followuphttp://sqlblog.com/blogs/kalen_delaney/archive/2010/09/16/24HOPfollowup.aspxThu, 16 Sep 2010 18:20:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:28815Kalen Delaney0http://sqlblog.com/blogs/kalen_delaney/comments/28815.aspxhttp://sqlblog.com/blogs/kalen_delaney/commentrss.aspx?PostID=28815Thank you everyone who attended my 24HOP session yesterday. It was a wonderful event to be part of. Here are a few of the questions that I was asked, that didn't get completely answered. Question: Can we set READ_COMMITTED_SNAPSHOT ON for just a table...(<a href="http://sqlblog.com/blogs/kalen_delaney/archive/2010/09/16/24HOPfollowup.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=28815" width="1" height="1">concurrencyisolationlockingPASSseminarssparse columnsGeek City: Too Many Columns!http://sqlblog.com/blogs/kalen_delaney/archive/2008/09/20/too-many-columns.aspxSun, 21 Sep 2008 01:52:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:9013Kalen Delaney9http://sqlblog.com/blogs/kalen_delaney/comments/9013.aspxhttp://sqlblog.com/blogs/kalen_delaney/commentrss.aspx?PostID=9013<P>As I am working on my new SQL Server 2008 internals book, I am finding many test situations in which I need to create a table with LOTS of columns. First of all, you're probably aware of the new SPARSE column feature that allows you to have up to 30,000 columns in a table!&nbsp; I did some testing with sparse columns, but just today I realized I had never actually created a table with more than the old limit of 1024.&nbsp; In addition, the new row compression had some special tricks for dealing with lots of columns, and groups columns into clusters of 30 columns each. (You'll have to wait for the new book to get the details.)</P>
<P>Yesterday, when writing about row compression, I realized I needed an easier way to create wide tables, so I wrote a script that allowed me to specify the number of columns, and also get a little creative with the datatypes, lengths and properties of the columns I was creating. I'll show you that script in just a minute!</P>
<P>Then just today, I read a <A href="http://sqlblogcasts.com/blogs/simons/archive/2008/09/18/Getting-more-than-1024-columns-in-a-table.aspx" target=_blank>blog entry by Simon Sabin</A>, who indicated that even with sparse columns, you could not create a table with more than 1024 columns. In the CREATE TABLE statement, you had to limit yourself, but then could use ALTER TABLE to add more columns. Although I hadn't tested this yet, I was intrigued, and realized I could use my new script to test out this claim. </P>
<P>So here is my original script. You can replace the 100 (for the value of @numcols) by the number of columns you'd like, but the script will create one more, because it starts with a ID int identity column. So the script as is will create a table with 11 columns. In the loop that adds columns to the creation string, I had three different datatypes of columns that I can use. One third of the columns will be type int with a default of 0, one third will be char(5) with a default of 'hello' and one third will be varchar(25) with a default of 'this is a longer message'. You can modify this however you like. You can change the datatypes within the loop, or add more conditionals and change expression to use modulo of some other number. </P>
<P><FONT face="Courier New" size=1>DECLARE @create varchar(max);<BR>DECLARE @tabname sysname;<BR>DECLARE @numcols int; <BR>DECLARE @col int;<BR>SELECT @numcols = 100;<BR>SELECT @tabname = 'wide' + CONVERT(varchar, @numcols); </FONT>
<P><FONT face="Courier New" size=1>SELECT @create = 'CREATE TABLE ' + @tabname + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' (ID int IDENTITY, '; </FONT>
<P><FONT face="Courier New" size=1>SELECT @col = 1;<BR>WHILE @col &lt; @numcols BEGIN<BR>&nbsp;&nbsp;&nbsp; IF (@col % 3) = 0 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' int default 0,';<BR>&nbsp;&nbsp;&nbsp; IF (@col % 3) = 1&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' char(5) default ''hello'',';<BR>&nbsp;&nbsp;&nbsp;&nbsp; IF (@col % 3) = 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' varchar(25) default ''this is a longer message'',';<BR>&nbsp; </FONT><FONT face="Courier New" size=1>SELECT @col = @col + 1;<BR>END;<BR>SELECT @create = @create + 'col' + CONVERT(varchar, @col) + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' int default 0);'<BR>PRINT @create&nbsp; <BR>EXECUTE (@create)&nbsp; <BR>EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');<BR>EXEC ('SELECT * FROM '+ @tabname);</FONT></P>
<P>To use this script to test the creation of a table with more than 1024 columns, I knew some (or most?) of the columns would need to be sparse. In addition, I found <A href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356932" target=_blank>this Connect entry</A> which indicated that if you do have more than 1024 columns, the table must have a column set, so it was easy enough to make that the last column that is added to the create string after the loop.</P>
<P><FONT face="Courier New" size=1>DECLARE @create varchar(max);<BR>DECLARE @tabname sysname;<BR>DECLARE @numcols int; <BR>DECLARE @col int;<BR>SELECT @numcols = 1200;<BR>SELECT @tabname = 'wide' + CONVERT(varchar, @numcols); </FONT>
<P><FONT face="Courier New" size=1>SELECT @create = 'CREATE TABLE ' + @tabname + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' (ID int IDENTITY, '; </FONT>
<P><FONT face="Courier New" size=1>SELECT @col = 1;<BR>WHILE @col &lt; @numcols BEGIN<BR>&nbsp;&nbsp;&nbsp; IF (@col % 3) = 0 <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' int sparse,';<BR>&nbsp;&nbsp;&nbsp; IF (@col % 3) = 1&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' varchar(5) sparse,';<BR>&nbsp;&nbsp;&nbsp;&nbsp; IF (@col % 3) = 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @create = @create + 'col' + CONVERT(varchar, @col) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' varchar(25) sparse,';<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT @col = @col + 1;<BR>END;<BR>SELECT @create = @create + 'col' + CONVERT(varchar, @col) + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' xml column_set for all_sparse_columns);';<BR>PRINT @create&nbsp; <BR>EXECUTE (@create)&nbsp; <BR>EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');<BR>EXEC ('SELECT * FROM '+ @tabname);</FONT><BR>&nbsp;
<P>Running this script, I was able to create a table with 1200 columns.
<P>And you can too, if you're using SQL Server 2008.
<P>Have fun!
<P><FONT color=#ff00ff size=4>~Kalen</FONT></P><img src="http://sqlblog.com/aggbug.aspx?PostID=9013" width="1" height="1">scriptssparse columnsSQL Server 2008