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, 1396🔥, 0💬
Popular Posts:
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
What is test testing area for? The testing area is provided to allow visitors to post testing commen...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, y...