Sql Server Rebuild Index

By

Rebuild Or Reorganise Index In Sql SERVER
Rebuild index is most frequently used & main functionin of SQL Server.When sql fragmentation is increased then we have to rebuild/reorganize the index. Rebuild index or  Reorganize index is a  process that reduce the fragmentation in an index .I will discuss MS SQL index fragmentation later. T SQL for rebuild all indexes is given below . I am not talking about index here or what the difference between these two.
I am here to explain T SQL rebuild all index or u may say that reindex SQL server table .
There are two way to rebuild or reorganize the index.You have to rebuild index or reorganize index on daily or weekly basis.Here we discuss the two way to rebuild index in SQL Server.
1. By the Wizard Window
2. By the Query Or T SQL to rebuild all indexs Or T SQL to reorganize all indexes

Rebuild index in SQL server

By the Wizard Window : 
Rebuild index is like sql server index maintenance script.  Connect the SQL Server.Expand the database after that select the table and expand it and right click on index. its look like below picture



In this there are option of rebuild all or reorganize all click on any one of them . I click on rebuild All then new window open like below



In above screen Total Fragmentation is 0 this indicate that you don't need to rebuild or reorganize.when you do this then may be its more than 0.When you Click on ok then its rebuild the index.You can also do same for reorganize .

By the Query : With the help of query we can also rebuild or reorganize the index.
             ALTER INDEX ALL ON codetable REBUILD
             ALTER INDEX ALL ON codetable REORGANIZE
where codetable is table name. If you want to rebuild or reorganize specific index in particular table then you have to know the index name and write the query like this
ALTER INDEX indexName ON tableName REBUILD/REORGANIZE

ALTER INDEX PK_CodeTable ON codetable REBUILD
ALTER INDEX PK_CodeTable ON codetable REORGANIZE
if you want to rebuild or reorganize all table index then you have to write a cursor or execute the below query. one line query for sql rebuild all indexes is given below

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'
Query for sql reorganize all indexes
EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REORGANIZE'

Above all query tested in SQL Server 2008 R2 .

mssql rebuild index explanation

I hope rebuilding/reorganize the index through the wizard is easy and simple but some time database have so many table then may be its take time to do  through wizard. With help of query its easy. Here we use the  sp_MSForEachTable for rebuild the index of all table.Some time question ask by interviewer how to rebuild index of database then you can explain this. The "sp_MSForEachTable" store procedure comes with SQL Server but its not documented in MSDN.Also when this procedure exist in Master database.When you execute this then you can see the definition of  sp_MSForEachTable.
sp_helptext sp_MSForEachTable 


0 comments:

Post a Comment