Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to create a table with a clustered key from a query in a single step or do I have to do this:

select 1 as id into foo;
create unique clustered index pk_foo on foo(id)
share|improve this question

2 Answers 2

up vote 3 down vote accepted

You have to add the index in a separate step.

From MSDN:

Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you must create them after executing the SELECT...INTO statement.

share|improve this answer

You can't specify this, but it doesn't matter

I believe it's not possible. However, I think that there is relatively little inefficiency in having to do it as two separate actions. In order to create a clustered index the table would have to be sorted anyway, and a copy of the unsorted table would have to be created somewhere in tempdb before the sort operation.

A point to note about temp tables is that the intermediate join results are persisted in exactly the same data structures in tempdb as temporary tables. Therefore temp tables are no less efficient in terms of processing or I/O, particulaly if they are created with minimally logged SELECT INTO statements.

Given that most table creation by SELECT INTO tends to happen in tempdb anyway the actual I/O from a SELECT INTO followed by a clustered index creation isn't going to be radically different from an operation that creates the temp table with a clustered index anyway. Ergo, I dare say MS decided (fairly sensibly) that they don't need to fix it as it ain't broke.

More on Temp Tables

A corollary to this is that most arguments against temp table usage based on performance are factually incorrect. As the system uses tempdb to store intermediate join results it is using the same data structures as a temp table would.

One feature I really would like to see, though

It would be nice to be able to use minimally logged SELECT INTO operations to create partitions on the appropriate file group and then swap them into a partitioned table (think pupulating monthly snapshots on a snapshot fact table).

There are a couple of workarounds. You might be able to use the technique described here but it's a bit yuck - for example it wouldn't support concurrent operations on the same table in different file groups. Another way to do it is to create the table in the default filegroup and, create a clustered index on the desired filegroup and then drop the clustered index.

share|improve this answer
    
+1 and thanks. The sorting caveat doesn't apply if the source data is already sorted - eg you are cloning a pre-existing table with a clustered index. –  Jack Douglas Dec 1 '11 at 14:29
    
@Jack Douglas - For a one-off operation it probably doesn't matter anyway. Also, IIRC SQL Server checks to see whether the data is already sorted. –  ConcernedOfTunbridgeWells Dec 1 '11 at 14:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.