Reindex after insert to empty table?

Question

Reindex after insert to empty table?

clary.david

Old Hand

Points: 303
More actions
April 24, 2012 at 3:01 pm

#392098

I've been around for a long, time, but I'm at a new shop, and you always see new/different things being done. This one's a puzzler for me though.
The code is thus:
TRUNCATE TABLE blah
INSERT INTO blah
SELECT whatever, data, they, want FROM sometable
DBCC DBREINDEX ("blah", blahIndex,80)
Now.. blah has no primary key, and the blahIndex is a nonclustered index on an identity int field.
My question is this: Is there a valid case for reindexing after a large insert ... on an empty table?
It would seem to me that we could simply change the identity field to a primary key, clustered index identity field. In this case the table is automatically ordered/indexed and no reindex is needed.
What say you?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

How much data? Is the non-clustered index going to change to be on the identity or is it on a column that is not ordered? How is the data loaded?

Jared
CE - Microsoft

clary.david Old Hand Points: 303 More actions · Answer 2

Looks to be between 700K rows per insert.

Raw data is loaded into a table from CSV (I'm guessing, I'm not involved in how the data gets to us). The insert pulls from that table to this one.

I would remove the non-clustered index on the identity column and change it to a clustered index. In either case, I don't see how (or why) you would reindex an identity column index (clustered or not) after an insert... wouldn't rows be added to both the table and the index in the same order?

Jared SSC Guru Points: 61793 More actions · Answer 3

They should. That's why I was asking about other non-clustered indexes. You should not have to reindex, but if you have doubs you can simply look at the fragmentation after loading the data. If it is an "IDENTITY" column and that is the only index, you should not have any fragmentation. NOTE: If there are only a small number of pages, it will show fragmentation. This can be ignored.

Jared
CE - Microsoft

clary.david Old Hand Points: 303 More actions · Answer 4

There are two other nonclustered indexes, but they are not being reindexed here, so I'm not sure they'd have a bearing anyway.

Jared SSC Guru Points: 61793 More actions · Answer 5

Not going to have fragmentation when the clustered index is on an identity column after the data has been truncated.

Jared
CE - Microsoft

clary.david Old Hand Points: 303 More actions · Answer 6

That's what I figured. I just wanted to make sure I wasn't talking out of my uneducated half.