Unfortunately there are some differences between SQL Server and SQL Azure. One of these differences is that SQL Azure does not support the
DBCC DBREINDEX command. Thankfully there is an alternative you can use to rebuild the indexes within your SQL Azure databases.
The alternative is to use
ALTER INDEX instead.
ALTER INDEX ALL ON TableName REBUILD
Here’s an example that loops through all the tables in a database and rebuilds all their indexes:
DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN exec('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor
You likely wont want to just execute this on your database as it will slow down any database access performed while rebuilding ALL the indexes. You could run this late at night, or just replace the
Rebuilding the table indexes on SQL Azure can be useful if there have been a lot of rows deleted from certain tables, since this will free up disk space used by the now deleted rows. This can result in a reduction in cost of your SQL Azure database if enough data rows have been deleted.
- 01 Jul 2020
- 06 Apr 2020
- 11 Mar 2020
- 15 Feb 2020
- 02 Jan 2020
This site is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com. We also participates in affiliate programs with Udemy, and other sites. This site is compensated for referring traffic and business to these companies.