March 8, 2011 at 5:52 am
Hi All!!!!
I have a table which will have millions of records and want to add a non-clustered index on one of the column.
There are two approaches of doing this:
Approach1:
Create the index at the time of table creation and then populate the data into table.
Approach2:
Create the table with index, populate the data into table and then create an index on that column.
So which approach is efficient and why??
March 8, 2011 at 6:50 am
If you're talking a nonclustered index, typically approach 2. If you're talking a clustered index, it depends, I've seen both approaches work.
Best bet, try both, compare execution time, IOs, CPU time and see which is better for your situation.
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
March 8, 2011 at 8:22 am
In the case of non clustered indexes, each one needs to be maintained for every insert, update and delete - so that the index matches the table data.
So if you are doing a bulk load, of sorts, then adding the NCI after, will be a bit faster because all those insert statements do not have to also manage the NCI. instead, it is built in one shot and is more efficient to build after.
A clustered index actually IS the 'table', and doesn't have the same issue, however there are other impacts. Which is why it is recommended to check the server load to choose the best method for your table and server config.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply