What happens when CREATE INDEX gets interrupted (manually or accidentally?

What happens when CREATE INDEX gets interrupted (manually or accidentally)?

• It takes hours or even days to create an index for a table of millions of rows. What happens to the database / data when CREATE INDEX gets interrupted? Such as when I manually used Ctrl-C or when there's an accident like a power-failure. Does it do harm to the database / data? If so, how do we recover from such an interruption?

• Answer:

  Index creation is done within a transaction. If you interrupt it, SQL Server will rollback the transaction. This might take a while, but it's harmless. Of course, there are locks held while the transaction is active so this can impact your server's performance. If you lose power in the middle, SQL Server will automatically rollback the partially completed transaction when the server boots. Again, there's a performance hit but there's no harm done to your data. http://technet.microsoft.com/en-us/library/ms188317(v=sql.105).aspx http://technet.microsoft.com/en-us/library/ms190925.aspx http://technet.microsoft.com/en-us/library/aa224747(v=sql.80).aspx