Insert Record on a Table with Clustered INDEX or without a Clustered INDEX

1
votes

I just want to ask which is better with regards to performance.

I have created a stored procedure that has

OPTION A

1. DROP TABLE Statement
2. SELECT INTO Statement
3. CREATE CLUSTERED INDEX

Note: basically the stored procedure drops and re-creates the table, then creates a CLUSTERED INDEX on it.

And I am thinking if this one should be better compared to the one I created.

OPTION B

1. TRUNCATE TABLE Statement
2. INSERT INTO Statement

Note: with my OPTION B there will be no creation of CLUSTERED INDEX because the table already has a CLUSTERED INDEX.

Thank you for all of you who will going to help me with this one.

1
sql-servertsqlindexing
Have you compared both (with execution plans) to see what works better for you? – Rich Benner
The answer is almost always "it depends" when it comes to questions like this, and the best way to find out is to do some tests for yourself. You may wish to read this article about "Which is faster?" questions – GarethD

1 Answers

0
votes

Its generally better to create the index after inserting the data.

EDIT: I seem to be getting voted down, so thought I should qualify this a little. Notice the word "generally" - this isn't going to always be true, and several people have already suggested you just try both and see for yourself which is quicker.

But as a general rule of thumb this is true. A standard practice when loading a large amount of data (and if you are concerned about performance, I am taking that as a given) is to drop or disable the indexes on the table, load the data, and then rebuild the indexes afterwards.

For an analogy as to why, think of it like writing an index for a book. If you are writing the book in some random order, adding pages and updating the index as you go, you will have to frequently move around the entries in the index to make room. If you wait until the entire book is written, then you can write the index much more efficiently.