• I don't think the given "correct" answer is in fact "the best way", as was asked. I even doubt if it takes the least effort, as the answer key says.

    If table XYZ is in fact a real table from a real database, and not just a throwaway table created for the purpose of doing a quick test, then there will be constraints on the table. There will be at least one PRIMARY KEY or UNIQUE constraint, probably one or two CHECK constraints, maybe a FOREIGN KEY constraint, possible a DEFAULT constraint or two, and of course NOT NULL constraints for most, if not all, columns. Of these constraints, only the NOT NULL constraints will be copied when you use SELECT INTO with an IDENTITY() function. There also will not be a UNIQUE or PRIMARY KEY constraint on the added identity column.

    So if you use a SELECT INTO with an IDENTITY() column, you'll next have to run a series of ALTER TABLE statements to add back all these constraints. Each of these (except the DEFAULT constraints) requires a table scan to check if the existing doesn't violate the constraint. And unless you have the system in single user mode, other users might be able to sneak in some bad data before you have the last constraint added. Oops. With an explicit CREATE TABLE, you can add all the constraints before copying the data; the constraints will be checked while the data is copied (with no extra scans required), and other users have no chance of bypassing them. So this is definitely the better way.

    It is also more efficient. I can use SSMS to generate a CREATE TABLE script for the existing table with all the constraints, use find and replace to change the table name, and then manually add the IDENTITY column, change the current PRIMARY KEY to UNIQUE (if required) and add a PRIMARYY KEY or UNIQUE constraint on the new column, and that's all I need to do. With the INSERT INTO method, I have to manually key in all the ALTER TABLE statements for the constraints, or generate them one by one (if that is even possible). So the CREATE TABLE / INSERT SELECT method required less typing, not more, making it more efficient for me. Since less scans of the data are needed (see above), it is also more efficient for SQL Server.

    Bottom line - INSERT INTO is a nice quick method to get a copy of the data only in a temp table. For making a real copy of the table (presumably as a first step that will be followed by dropping the original and renaming the copy - for why else would I want a copy of the table instead of a view?), INSERT INTO falls terribly short.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/