Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
Does Index Speed Up SELECT Statements?
Submitted by: AdministratorIf you want to see the impact of indexes on SELECT statements, you can run the same SELECT statement on "ggl_links" and "ggl_links_indexed" tables. See the tutorial exercise below:
USE GlobalGuideLineDatabase;
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 ggl_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
<pre>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
</pre>
Milliseconds used: 46
Submitted by: Administrator
USE GlobalGuideLineDatabase;
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 ggl_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
<pre>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
</pre>
Milliseconds used: 46
Submitted by: Administrator
-- Run SELECT on the table with indexes
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
SELECT TOP 3 counts, url FROM ggl_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
<pre>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
</pre>
Milliseconds used: 0
The test result shows indexes on "url" and "counts" did improve the SELECT statement from 46 milliseconds to almost 0 millisecond.
Submitted by: Administrator
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
SELECT TOP 3 counts, url FROM ggl_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
<pre>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
</pre>
Milliseconds used: 0
The test result shows indexes on "url" and "counts" did improve the SELECT statement from 46 milliseconds to almost 0 millisecond.
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.