Collections:
Adding a New Index to a Large Table in SQL Server
What Happens If You Add a New Index to Large Table in SQL Server?
✍: FYIcenter.com
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.
⇒ CREATE INDEX - Impact on Other User Sessions in SQL Server
⇐ Index Speeding Up SELECT Statements in SQL Server
2016-11-13, 1473🔥, 0💬
Popular Posts:
How To Drop a Stored Procedure in Oracle? If there is an existing stored procedure and you don't wan...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...
Where to find SQL Server database server tutorials? Here is a collection of tutorials, tips and FAQs...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...