Index Speeding Up SELECT Statements in SQL Server

Q

Does Index Speed Up SELECT Statements in SQL Server?

✍: FYIcenter.com

A

If you want to see the impact of indexes on SELECT statements, you can run the same SELECT statement on "fyi_links" and "fyi_links_indexed" tables. See the tutorial exercise below:

USE FyiCenterData;
GO

-- Run SELECT on the table without indexes
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
SELECT TOP 3 counts, url FROM fyi_links
   WHERE url LIKE 'a%'
   ORDER BY counts DESC;
SET @end_time = GETDATE();
PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20),
   DATEDIFF(MILLISECOND,@start_time,@end_time));
GO
counts      url
----------- ------------------------------------------------
999417      a ihgu migox mlqvi gvs n nmabwdk iehu ezjjv n  l
999008      agqbr gkjwfpjvp z c cq k inen j  ppjsu x iuk uhp
998471      a    yiu squqco eih

Milliseconds used: 46

-- Run SELECT on the table with indexes
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
SELECT TOP 3 counts, url FROM fyi_links_indexed
   WHERE url LIKE 'a%'
   ORDER BY counts DESC;
SET @end_time = GETDATE();
PRINT 'Milliseconds used: '+CONVERT(VARCHAR(20),
   DATEDIFF(MILLISECOND,@start_time,@end_time));
GO
counts      url
----------- ------------------------------------------------
999417      a ihgu migox mlqvi gvs n nmabwdk iehu ezjjv n  l
999008      agqbr gkjwfpjvp z c cq k inen j  ppjsu x iuk uhp
998471      a    yiu squqco eih

Milliseconds used: 0

The test result shows indexes on "url" and "counts" did improve the SELECT statement from 46 milliseconds to almost 0 millisecond.

 

Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-13, 789👍, 0💬