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
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: