Multiple SELECT INTO

Question

I have a ~300 million row table with a column that has 9 different values. I need to be able to SELECT INTO 9 different new tables based on this column.

At the moment it has to do 9 separate table scans, as I just run 9 separate queries. Is there a way I could do this in one table scan with a CASE statement that defines the table to go into, or something similar?

I'm using SQL Server 2012 x64 Standard Edition SP1. There is no covering index as the column list is almost all of them. Example of what I'm trying to achieve:

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO CASE 
        WHEN col1=N'tbl1' THEN [dbo].[tbl1]
        WHEN col1=N'tbl2' THEN [dbo].[tbl2]
    END
FROM [dbo][tblBase]

There is no clustered index on the base table. There is an index on col1 but it chooses not to use it. Forcing it increases the cost dramatically as it does an RID Lookup.

The base table is 68GB, with SQL Server max server memory being 30GB (although that's increasing to ~50GB soon).

Answer 1

SELECT INTO <table-decided-by-code>

You can't dynamically set the destination table like that. If the target tables are part of a partition set partitioned on that column then SQL Server would do this for you, but you are using 2012 standard edition so that won't be possible (partitioned tables were an enterprise only feature until 2016sp1).

Forcing it increases the cost dramatically as it does an RID Lookup.

Have you verified that this produces a slower result? Sometimes the query cost derived for plans by the planner doesn't not match actual performance.

If you are only copying a few columns from the target table, perhaps you could include those in the index with something like CREATE INDEX [ix_tblBase_col1_plus] ON ([col1]) INCLUDE ([col2] ,[col3]). This will increase the size of the index but remove the need for the extra page lookups by RID, but as you say you are taking most of the rows this is unlikely to be practical.

There is no clustered index on the base table.

Adding any old clustered index would not help, you'll just replace the extra lookups by RID with extra lookups by clustering key.

You could try defining [col1] as your clustering key. When clustering on a non-unique key SQL Server adds a 4-byte "uniquifier" column which is appended to the indexed values. If your column is 4 bytes long or less this will result in a clustering key no larger than RID values. If the column contains wider data then space efficiency may be an issue as that extra data will be added to every row in every non-clustered index. You would need to experiment to see what difference this makes to the size of the table+indexes and any impact on other parts of your application (we could offer educated guesses if you provide a full table definition, and if your "col1" is variable length some information on the pattern of its values, but trying it in a dev copy of your DB would be more definitive).

Answer 2

it not use INDEX because no any WHERE condition in SELECT statement, so from SQL server point of view - it always make FULL-SCAN of table, so no reason for use index.

possible make changes

declare cursor as (it is pseudo code)
SELECT DISTINCT col1 as tbl_name FROM [dbo][tblBase]
fetch col1 INTO @tbl_name
and after LOOP for each from LIST

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO @tbl_name
FROM [dbo][tblBase] WHERE col1 = @tbl_name

but need test - it could reduce loading of server memory, but may not give benefits in total speed