Creating an index, why is it insert into itself?

Question

Creating an index, why is it insert into itself?

Maddave

SSCertifiable

Points: 6828
More actions
May 22, 2014 at 3:56 am

#289145

Hi, I am creating an non clustered index on a table which has over 30 million rows in it as follows:
CREATE NONCLUSTERED INDEX IX_ParentCode
ON [dbo].[Codes] ([ParentCode])
INCLUDE ([Id], [ExternalOrderNumber])
When using sp_whoisActive to monitor the process I am seeing the command being run as:
INSERT INTO Codes SELECT * FROM Codes
I am a bit confused as I don't see any increase in usage of the database log file (using dbcc sqlperf(logspace)). I can see the CREATE INDEX command under Sp_who2 though.
Can anyone explain what it is actually going on for this index creation and why it selects into itself?
I realise nothing may be wrong, but more curious as I know this command is going to take some time to complete.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Read from the table, insert into the index. It's just how an index creation shows up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

If you're on Enterprise Edition you can add "ONLINE = ON" and the table can still be used while the index is being built.

I prefer to always explicitly specify the "FILLFACTOR = nnn" you want; a good "default FILLFACTOR" is not really possible, because each index's needs are specific.

If you have tempdb on faster drives, you might want to specify "SORT_IN_TEMP = ON":

CREATE NONCLUSTERED INDEX IX_ParentCode

ON [dbo].[Codes] ( [ParentCode] )

INCLUDE ( [ExternalOrderNumber], [Id] )

WITH ( FILLFACTOR = 99, ONLINE = ON, SORT_IN_TEMPDB = ON )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

tim_harkin SSCrazy Points: 2399 More actions · Answer 3

Maddave (5/22/2014)
Hi, I am creating an non clustered index on a table which has over 30 million rows in it as follows:
CREATE NONCLUSTERED INDEX IX_ParentCode
ON [dbo].[Codes] ([ParentCode])
INCLUDE ([Id], [ExternalOrderNumber])
When using sp_whoisActive to monitor the process I am seeing the command being run as:
INSERT INTO Codes SELECT * FROM Codes
I am a bit confused as I don't see any increase in usage of the database log file (using dbcc sqlperf(logspace)). I can see the CREATE INDEX command under Sp_who2 though.
Can anyone explain what it is actually going on for this index creation and why it selects into itself?
I realise nothing may be wrong, but more curious as I know this command is going to take some time to complete.

Try using this parameter

@get_outer_command = 1