Today, I worked on a service request where I found an interesting situation running an Insert Into ... Select command when the source table is a heap table. In this situation, I saw an additional sorting operation that depending on the number of rows of the source might take some resource and time.
I have a table called source that is a heap table using the following definition.
CREATE TABLE [dbo].[Source](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NULL
) ON [PRIMARY]
I have a table called target that has the following definition.
CREATE TABLE [dbo].[Target](
[ID] [int] NOT NULL,
[NAME] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [tARGET1] ON [dbo].[Target]
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
I executed the following command to insert data from source to target table.
TRUNCATE TABLE Target
INSERT INTO Target (id,name) select id,name from Source
I saw in the execution plan two additional sort operators. First for clustered index and second for the additional index that the table target has.
If the source table has a clustered index in its definition the process will save around 6 seconds because it is not needed to sort the Source table.
In this situation, it is clear that depending on the target table definition SQL Server is performing additional operations that will take an additional time. For this reason, I performed the following changes reducing around 1 minute the execution for this workload:
Adding a PK (Clustered) in the Source table to have the same definition in terms of PK with target table.
Removing the additional index in the Target table leaving only the PK.
Run the INSERT INTO .. SELECT taking around 1 minute and 52 seconds.
Creating the additional index in the Target table that took 26 seconds. Reducing more than 1 minute the execution of this query.