SA0125 : Avoid use of the SELECT INTO syntax

The problem involves the use of the SELECT INTO statement, which is commonly used to create a new table and insert data into it simultaneously. While convenient, it can lead to potential performance bottlenecks due to locking issues.

For example:

1-- Example of problematic query
2SELECT * INTO NewTable FROM ExistingTable;

This example creates a new table NewTable based on the structure and data from ExistingTable. However, this operation holds schema locks on the source table, which can impact concurrency and performance, especially in high-transaction environments.

• Schema locks prevent other operations on the source table, leading to potential blocking issues.

• The operation is minimally logged in full recovery mode, which may impact transaction log performance and increase I/O.

To avoid unnecessary locks and performance issues caused by the SELECT INTO statement, replace it with an explicit table creation followed by an INSERT INTO statement.

Follow these steps to address the issue:

1. Create the new table structure using the CREATE TABLE statement. Define all necessary columns and their data types explicitly.

2. Use the INSERT INTO statement to insert data from the existing table into the newly created table.

3. Ensure that appropriate indexes and constraints are applied to the new table to maintain performance and data integrity.

For example:

 1-- Define the new table structure
 2CREATE TABLE NewTable (
 3    Column1 INT,
 4    Column2 NVARCHAR(100),
 5    -- Add other columns as needed
 6);
 7
 8-- Insert data into the new table
 9INSERT INTO NewTable (Column1, Column2)
10SELECT Column1, Column2 FROM ExistingTable;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Code Smells

INTO Clause (Transact-SQL)

INSERT (Transact-SQL)

Other Resources