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, 1406🔥, 0💬
Popular Posts:
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...