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:
How To Rebuild Indexes with ALTER INDEX ... REBUILD?
Submitted by: AdministratorWhen an index is defragmented to a large percentage, like > 30%, you can use the "ALTER INDEX ... REBUILD" statement to rebuild the index. Here is a tutorial exercise on rebuilding indexes:
UPDATE ggl_links_indexed SET url = REVERSE(url)
WHERE id <=50000;
GO
(50000 row(s) affected)
SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
DB_ID(N'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_links_indexed'),
DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id;
GO
0 NULL 0.574712643678161
2 ggl_links_url 85.0142045454545
3 ggl_links_counts 0.448430493273543
ALTER INDEX ggl_links_url ON ggl_links_indexed REBUILD;
GO
SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
DB_ID(N'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_links_indexed'),
DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id;
GO<pre>
0 NULL 0.574712643678161
2 ggl_links_url 0
3 ggl_links_counts 0.448430493273543</pre>
Submitted by: Administrator
UPDATE ggl_links_indexed SET url = REVERSE(url)
WHERE id <=50000;
GO
(50000 row(s) affected)
SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
DB_ID(N'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_links_indexed'),
DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id;
GO
0 NULL 0.574712643678161
2 ggl_links_url 85.0142045454545
3 ggl_links_counts 0.448430493273543
ALTER INDEX ggl_links_url ON ggl_links_indexed REBUILD;
GO
SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
DB_ID(N'GlobalGuideLineDatabase'),
OBJECT_ID(N'ggl_links_indexed'),
DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
WHERE s.object_id = i.object_id
AND s.index_id = i.index_id;
GO<pre>
0 NULL 0.574712643678161
2 ggl_links_url 0
3 ggl_links_counts 0.448430493273543</pre>
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.