T-SQL: SELECT INTO sparse table?

Question

I am migrating a large quantity of mostly empty tables into SQL Server 2008.

The tables are vertical partitions of one big logical table.

Problem is this logical table has more than 1024 columns.

Given that most of the fields are null, I plan to use a sparse table.

For all of my tables so far I have been using SELECT...INTO, which has been working really well.

However, now I have "CREATE TABLE failed because column 'xyz' in table 'MyBigTable' exceeds the maximum of 1024 columns."

Is there any way I can do SELECT...INTO so that it creates the new table with sparse support?

Answer 1

You cannot (and probably don't want to anyway). See INTO Clause (TSQL) for the MSDN documentation.

The problem is that sparse tables are a physical storage characteristic and not a logical characteristic, so there is no way the DBMS engine would know to copy over that characteristic. Moreover, it is a table-wide property and the SELECT can have multiple underlying source tables. See the Remarks section of the page I linked where it discusses how you can only use default organization details.

Answer 2

What you probably want to do is create the table manually and populate it with an INSERT ... SELECT statement.

To create the table, I would recommend scripting the different component tables and merging their definitions, making them all SPARSE as necessary. Then just run your single CREATE TABLE statement.