I have a SQL Server 2000 table in a production environment with about 80 million rows. I need to add a nullable bit column to the table. While adding a column with null value to a production table is a quick operation with just a schema update, I also need to add an index on that column.
Will the table/server lock up when I add the index? Is there a way of achieving this with the least possible impact on performance?
Thanks
You should always test your changes in a staging environment before updating production, and that environment should be a realistic representation of your production environment. If that is not possible for some reason, then make the change after business hours or during non-peak hours.
Adding an index on a bit column is generally not a good idea. Indexes work best when the column has a large sampling of possible values. 1,0, or null for 80 million rows will produce a large index that doesn't do much good, so to answer your question, the presence of this index will have a negative impact on server performance.
