SQL Server FAQ - Adding a New Index to a Large Table

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

What Happens If You Add a New Index to Large Table?

An index can be added when you create a new table. New rows will be indexed as they are inserted into the table. But you can also add a new index to an existing table with the same CREATE INDEX statement. The existing rows will be indexed as part of the CREATE INDEX statement.

If you add a new index to an existing table with a large number of rows. The CREATE INDEX statement could take some time to finish. See the tutorial exercise below:

USE FyiCenterData
GO

-- Drop indexes if needed
DROP INDEX fyi_links_indexed.fyi_links_url;
DROP INDEX fyi_links_indexed.fyi_links_counts;
GO

SELECT COUNT(*) FROM fyi_links_indexed;
GO
100000

-- Create two indexes
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
CREATE INDEX fyi_links_url ON fyi_links_indexed (url);
CREATE INDEX fyi_links_counts ON fyi_links_indexed (counts);
SET @end_time = GETDATE();
PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20),
   DATEDIFF(MILLISECOND,@start_time,@end_time));
GO

-- First time
Milliseconds used: 12626

-- Second time
Milliseconds used: 11763

-- Third time
Milliseconds used: 13890

You can see creating two indexes on a table of 100000 rows costs about 12 seconds.

(Continued on next topic...)