Published 20 November 2017
Creation of table by SELECT INTO statement
SELECT … INTO is being used to create a table. Before SQL Server 2005 this would lock out all other users.
It is usually easier to understand tables created with a CREATE TABLE statement. However, SELECT INTO can be very useful for creating temporary tables.
Prior to SQL Server 2005, use of SELECT…INTO in production code was a performance 'code smell' because it acquired schema locks on system tables of the database, causing SQL Server to appear unresponsive while the query was being executed. This is because it is a DDL statement in an implicit transaction, which is inevitably long-running since the data is inserted within the same SQL Statement. However, this behavior was fixed in SQL Server 2005, when the locking model changed.
SELECT…INTO became popular because it was a faster way of inserting data than using INSERT INTO…SELECT…. This was mainly due to the SELECT…INTO operation being, where possible, bulk-logged. Although INSERT INTO can now be bulk-logged, you may still see this performance advantage in SQL Server 2012 and 2014, because SELECT…INTO can be parallelized on these versions, whereas support for parallelization of INSERT INTO only emerged in SQL Server 2016. However, with SELECT…INTO, you still have the task of defining all required indexes and constraints, and so on, on the new table.
Generally, SELECT…INTO is still a useful shortcut for development work, but it no longer has a clear performance advantage, and is of doubtful value for a live database. It is not part of the SQL Standard. It is usually easier to work with tables created with a CREATE TABLE statement because you then have the advantage of specifying constraints and datatypes in advance, and this also means that you're less likely to allow inconsistencies to sneak into the data.
See also: When to use the SELECT…INTO statement