How to rebuild an Index in SQL Azure using ALTER INDEX

Indexes help with database performance. If you do not have an index on a table, SQL Azure has no choice but to use a Table Scan. If you do not know, Table Scan is slowest method of retrieving data from SQL. You can either have a clustered or a non-clustered index in SQL azure. As new data is inserted in a table and existing data gets updated, table indexes can become scattered or fragmented. This typically happens when the logical ordering of a data page does not match the physical ordering. In order to defrag an index, you can either rebuild or reorganize an index. If there is heavy fragmentation, rebuilding an index is a better choice.

This method is what we are going to use in today’s topic on SQL Azure.

–How to rebuild an Index in SQL Azure using ALTER INDEX ALL command

/*

Here are the specifics of this script:
-Using Try Catch block
-Create a temp table
-Load the table names into the temp table
-Loop through the records (table) one by one
-Rebuild the index on a table using ‘alter index all’ statement
-Drop the temp table

*/

BEGIN TRY

–DECLARATION SECTION

DECLARE

@NUM_RECORDS INT,

@LOOP_COUNTER INT,

@NUM_DB INT,

@DB_LOOP_COUNTER INT,

@NAME_SCHEMA VARCHAR(50),

@NAME_TABLE VARCHAR(100),

@NAME_DB VARCHAR(100),

@SQL_STATMENT NVARCHAR(1000)

–TEMP TABLE FOR DATABASE INFO

CREATE TABLE #TL_ALL_TABLES

(

TABLE_ID INT IDENTITY(1,1),

NAME_DB VARCHAR(50),

NAME_SCHEMA VARCHAR(100),

NAME_TABLE VARCHAR(100)

)

SET @NAME_DB=‘NORTHWIND’

–INSERT ALL THE TABLES INTO TEMP TABLE

SET @SQL_STATMENT=N’INSERT INTO #TL_ALL_TABLES

(NAME_DB, NAME_SCHEMA,NAME_TABLE)

SELECT

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE=”BASE TABLE”

ORDER BY TABLE_NAME’


–EXECUTE THE SQL STATEMENT

PRINT @SQL_STATMENT

EXEC (@SQL_STATMENT)

SELECT @NUM_RECORDS=COUNT(*) FROM #TL_ALL_TABLES

SET @LOOP_COUNTER=1

–LOOP THROUGH ALL THE TABLES

–RUN ALTER INDEX ALL STATEMENT

WHILE @LOOP_COUNTER <= @NUM_RECORDS

BEGIN

SELECT

@NAME_DB=NAME_DB,

@NAME_SCHEMA=NAME_SCHEMA,

@NAME_TABLE=NAME_TABLE

FROM #TL_ALL_TABLES

WHERE TABLE_ID=@LOOP_COUNTER

PRINT ‘RUNNING INDEX OPTIMIZATION ON DATABASE ‘ + @NAME_DB

PRINT ‘—————————————————’

PRINT ‘TIME IS ‘ + CAST(GETDATE() AS VARCHAR(50))

–PRINT ‘SCEMA IS ‘ + @NAME_SCHEMA + ‘NAME IS ‘ + @NAME_TABLE

SET @SQL_STATMENT=‘ALTER INDEX ALL ON ‘ + @NAME_DB + ‘.’ + @NAME_SCHEMA + ‘.’ + @NAME_TABLE + ‘ REBUILD’

–PRINT AND EXECUTE THE SQL STATEMENT

PRINT ‘EXECUTING THIS STATEMENT ‘ + @SQL_STATMENT

PRINT ‘—————————————————’

EXEC (@SQL_STATMENT)

SET @LOOP_COUNTER=@LOOP_COUNTER+1

END –TABLE LOOP


DROP TABLE #TL_ALL_TABLES

PRINT ‘—————————————————’

PRINT ‘END OF EXECUTION’


END TRY

–CATCH BLOCK FOR ERROR HANDLING

BEGIN CATCH

SELECT ‘THERE WAS AN ERROR! ‘ + ERROR_MESSAGE()

RETURN

END CATCH


For additional reading, please visit this page:

http://technet.microsoft.com/en-us/library/ms188388.aspx

This entry was posted in Administration, Development, SQL Programming, TSQL and tagged , , . Bookmark the permalink.

Comments are closed.