September 13, 2012 at 7:46 pm
Hi All,
I need to drop indexes on a table over 500mil records, I wandering is it better to drop the indexes the following sequence
non-clustered indexes -> primary -> clustered index
or the following
Clustered index -> primary -> non-clustered indexes
I would assume the first one will be slower?
Is my assumption correct?
September 13, 2012 at 10:17 pm
Yes. The first one will be slower compared to second since the first one will be doing FTS....I mean Full Table Scan and then based on the inetrnal ordering will delete the indexes.
September 13, 2012 at 10:25 pm
How many indexes are there in the table?
Why do you want to delete all the indexes?
Why do you want to delete the primary key as well?
I believe second one would be slower as dropping the CI would rebuild the NC indexes.
September 13, 2012 at 10:43 pm
4 non-clustered, 1 clustered and 1 primary key.
For bulk insert.
September 14, 2012 at 12:03 am
Catcha (9/13/2012)
4 non-clustered, 1 clustered and 1 primary key.For bulk insert.
But do you think that dropping the index will save your time ?
Because as you mentioned that the table contains millions of records, so I guess to rebuild the indexes will be time taking.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 14, 2012 at 1:02 am
Yes, with recreating the index I will use the following order
Non-clustered -> primary key -> clustered
So rebuilding of whole table will be performed last.
Thought?
September 14, 2012 at 3:49 am
Catcha (9/14/2012)
Yes, with recreating the index I will use the following orderNon-clustered -> primary key -> clustered
So rebuilding of whole table will be performed last.
Thought?
Again I would like to know that how many records you are going to insert.As in case the number records are very less than the existing records comparatively, it is recmmonded not to drop the indexes.
Note - The records(500 millions) as you mentioned in your table will break your head during index rebuild.So better to give it a try on development or test environment instead of doing on production.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 14, 2012 at 9:02 am
Catcha (9/14/2012)
Yes, with recreating the index I will use the following orderNon-clustered -> primary key -> clustered
So rebuilding of whole table will be performed last.
Thought?
Nope. Backwards. If you create the clustered index last, it will recreate all the other indexes. Recreating, clustered first, then everything else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2012 at 10:49 pm
Yes, that makes sense.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply