Faster insert into a table with or without a clustered index

[jrsansom]

Dear All,

I currently have a problem requiring that I insert approximately 2 million rows into a database, 1 record at a time. Now I know that inserting a single row at a time is very in-efficient approach to this problem, given that operations should be set based however, I do not have an alternative at this time.

I am currently inserting data into a table that has an identity column with a clustered index on it. Would I see a performance gain if I were to remove the clustered index and to then re-build the index after having inserted all the data?

Any assistance would be very much appreciated.

Many Thanks,
John

 

[SQLDenis]

>>Would I see a performance gain if I were to remove the clustered index and to then re-build the index after having inserted all the data?


in this case probably not since the index is just an identity column and there is no need to order anything

test it out with and without the index to verify that this is thue

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mrdenny]

Typically I would say that yes you see a performance gain. However because the clustered index is on an identity column all records in the clustered index will be in the correct order already. Any performance gain will probably be minimal and be out weighed by the time it will take to recreate the clustered index.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com