How To Rebuild All Indexes on a Single Table?
Submitted by: AdministratorIf you have several indexes on a single table and want to rebuild all of them, you may use the "ALTER INDEX ALL ON table_name REBUILD" statement as shown in the tutorial exercise below:
UPDATE ggl_links_indexed
SET url = REVERSE(url), counts = -counts
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.750315258512
3 ggl_links_counts 84.040404040404
ALTER INDEX ALL 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.12987012987013
3 ggl_links_counts 0.44843</pre>
Submitted by: Administrator
UPDATE ggl_links_indexed
SET url = REVERSE(url), counts = -counts
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.750315258512
3 ggl_links_counts 84.040404040404
ALTER INDEX ALL 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.12987012987013
3 ggl_links_counts 0.44843</pre>
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Is a Transact-SQL Statement Batch in MS SQL Server? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | How To Write Character String Constants or Literals in MS SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
☺ | How To Concatenate Two Binary Strings Together? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |