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.
Background
CREATE TABLE [dbo].[Source](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](200) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Target](
[ID] [int] NOT NULL,
[NAME] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [tARGET1] ON [dbo].[Target]
(
[NAME] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
TRUNCATE TABLE Target
INSERT INTO Target (id,name) select id,name from Source
Enjoy!!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.