dding indexes to tables is the most effective way to optimize your SQL Server query performance. Tables that you query often may require several indexes in order to handle the various search arguments you send. There is one particular situation where indexes on large tables are a hindrance, though. When you're bulk-copying data into the table, it's best to have either no index at all, or just a clustered index. For those situations it is best to use Transact-SQL scripts to remove and add those indexes. In this article you'll learn how to use Transact-SQL stored procedures to generate Transact-SQL indexing scripts that are more robust and less laborious than SQL Server's Enterprise Manager scripting tool.
Recently I was involved in a project that needed to move a number of large database tables from one server to another. We decided to use the bcp.exe command to export the data, and BULK INSERT to import it into the new database. We did not want to have any indexes, or at a minimum a clustered index on the new tables, in order to optimize the import process.
Initially we used the Enterprise Manager scripting tool but ran into a number of problems, most related to the fact that we would have to do a lot of cut-and-paste to get a script that included just primary and unique constraints, as well as indexes, and nothing else. What we really wanted was a script to create the tables with no indexes, then a script to add all the indexes, clustered key first, once the tables were loaded.
What I needed was a scripting tool that we could modify and ensure that the order of the indexes was what we wanted. I could have tried to navigate the SQL-DMO object model and call the scripting from there, just as Enterprise Manager does, but we risked running into the same inflexibility. Instead, I made a tool using Transact-SQL, which you'll see here, though in a much more refined and tested form.