There are situations where you are faced to create index on a really huge table. This can be really troubling because it’s no joke at all and it can cause you headache for weeks to even attempt to start creating the index on the table. I have experienced this on many occasions and l have done it using many techniques so l just want to share one simple way of accomplishing this task.
First of all, if you start by just writing simple create index script for your index and executing it on the table, you will be in trouble for many reasons such as your transaction log becoming full and the operation gets aborted or your database itself gets overly exploded. Yep, this could cause you trouble so it’s highly recommended to research the database structure and understand what is deep in there before you even start. So my simple approach which could be done by many DBAs is to create a new table with the same structure as the original table. Then create all the indexes on that new table. This operation will be in seconds since there is no data in this new table. What you do next is to dump the data from the original table into the new table. This will take time to complete but far easier to do than trying to apply the index straight to the original index. It will also not cause your transaction log to get full as compared to creating the index directly on the original large table because you will be performing a sql dumb of the data in the old table into the new table. This can be accomplish using different tools and one simple SQL script will be the BulkInsert which can dump data without writing each record to the transaction log.
After dumping all the data into the new table, drop the old table and rename the new table to the name of the old table. Someone might be asking how can l rename the table?
Well, its the easiest thing to do and can be accomplished with either an SQL script or using SQL Server Management Studio(SSMS). In SSMS is quite simple, just right click on the table and choose rename and perform the renaming. To perform the rename using SQL script, you use the simple in built stored procedure SP_RENAME. Using SP_RENAME is as easy as ABC. For example, if l have a table in my database by name 2smart4school_Visitor and l want to rename it to Visitor, I will use the sp_rename like this
EXEC SP_RENAME ‘2smart4school_visitor’,’visitor’
For more information on sp_rename, check http://technet.microsoft.com/en-us/library/ms188351.aspx
So that’s one way to get the index created on a really large table.
Hope this helps someone from pulling their hair off.
Comment, suggestions welcome.
