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:
What Happens If You Add a New Index to Large Table?
Submitted by: AdministratorAn 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 GlobalGuideLineDatabase
GO
-- Drop indexes if needed
DROP INDEX ggl_links_indexed.ggl_links_url;
DROP INDEX ggl_links_indexed.ggl_links_counts;
GO
SELECT COUNT(*) FROM ggl_links_indexed;
GO
100000
-- Create two indexes
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
CREATE INDEX ggl_links_url ON ggl_links_indexed (url);
CREATE INDEX ggl_links_counts ON ggl_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.
Submitted by: Administrator
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 GlobalGuideLineDatabase
GO
-- Drop indexes if needed
DROP INDEX ggl_links_indexed.ggl_links_url;
DROP INDEX ggl_links_indexed.ggl_links_counts;
GO
SELECT COUNT(*) FROM ggl_links_indexed;
GO
100000
-- Create two indexes
DECLARE @start_time DATETIME, @end_time DATETIME;
SET @start_time = GETDATE();
CREATE INDEX ggl_links_url ON ggl_links_indexed (url);
CREATE INDEX ggl_links_counts ON ggl_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.
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.