SELECT INTO is faster than CREATE & INSERT

Before we directly jump to the topic, I would like to give a basic introduction of SELECT..INTO statement.

As, we know that, select into statement can be used to create new table with same table structure and to insert a base table rows in new table. We need to consider following things before using this.

1.       Column constraints are not gets created in new table
2.       If a selected column is computed then corresponding column in new table will not be computed column. The values in new columns are the values that were computed at the time SELECT…INTO was executed.
3.        SELECT…INTO statement does not cause blocking for duration of select statement.
4.       Generally, SELECT…INTO is measurably faster than separate create and insert statements. I will try to demonstrate the same in this post.

Following steps are performed. I have run this test on SQL 2005 sp3.

Step-1 Create Base table and populate default data.

IF object_id('IntoTestBaseTable') IS NOT NULL
   DROP TABLE IntoTestBaseTable;
GO

CREATE TABLE IntoTestBaseTable (
   c1 INT           ,
   c2 NVARCHAR (MAX)
);
GO

Read more: Beyond Rational

Posted via email from jasper22's posterous