I have an application that is installed world wide that uses SQL versions from SQL Server 2008 Express to SQL Server 2017 Enterprise edition.
I have tried several ways to create an index (3 columns with an include of 3 columns) on a big table where the Database is usually in simple recovery model but can be as Full recovery model... Hardware can vary drastically from one customer top another:
- Alter table create index...
- Create a tmp table with the new index and copy the data with an "insert into" followed with a drop table of the original table and a rename of the tmp table to replace the original one.
- Create a tmp table with the new index and use a PowerShell script to copy the data followed by a drop table of the original table and a rename of the tmp table to replace the original one.
The quickest of the 3 was the 1st option (the traditional way of creating a non-clustered index) which took 1h15m for a bit over 106 million records (the table has 200 columns... not ideal but it's what I'm working with) the other options took triple the time and more)
Unfortunately, I can't use the online feature since there are a variety of SQL Server versions and the online is only good for enterprise edition.
An hour and 15 minutes in my lab is too long and I would like to try to find another way to bring that down, specially that in one of our next application updates, we will need to create 23 indexes... and I haven't seen an update that takes 5-6 hours nor will customers want to wait 5-6 hours for an update of a software.
Also, the update will require no activity on the database during the update.
All I'm looking for are ideas I can try to get my indexes created in a reasonable amount of time. No code! just need theoretical ideas.
Any idea would be greatly appreciated.