CODEWITHSUNDEEP
t-sqlazure-sql-database
Nick Olsen
Nick Olsen

Reputation: 6369

Rebuild Index with Hyphen in Name

Using SQL Azure with Index Advisor it generates indices automatically for tables based on some fancy algorithm. We are attempting to implement some maintenance scripts that rebuild fragmented indices but the problem is that the indices generated by the Index Advisor have Guids in them so the query to rebuild the index looks like the following:

ALTER INDEX nci_wi_Table_C17CD092-869C-407C-8623-9F05C8D64E67 ON [dbo].[Table] REBUILD WITH (FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON)

If you attempt to run this, you get the error:

Incorrect syntax near '-'.

I tried wrapping the index name in [ ] and in ' ' but neither works.

Anyone have any ideas how to execute the ALTER INDEX query when there are hyphens in the name of the index?

Upvotes: 0

Views: 1407

Answers (2)

Borko Novakovic
Borko Novakovic

Reputation: 116

This is not Azure SQL DB specific. Wrapping index name in [ ] works fine. Please try out the following example:

CREATE TABLE [Table] (Col1 int, Col2 int);
GO

CREATE INDEX [nci_wi_Table_C17CD092-869C-407C-8623-9F05C8D64E67] ON [dbo].[Table] (Col1);

GO

ALTER INDEX [nci_wi_Table_C17CD092-869C-407C-8623-9F05C8D64E67] ON [dbo].[Table]  REBUILD WITH (FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON)

If you reference index with hyphen in name, you'll get "Incorrect syntax near '-' " both with SQL Server (on-premise) and Azure SQL Database.

Upvotes: 1

Paul Andrew
Paul Andrew

Reputation: 3253

This must be an Azure "feature". With square brackets it works fine on prem.

Have you tried something totally unnecessary like this:

DECLARE @SQL VARCHAR(MAX)

SELECT
    @SQL = 'ALTER INDEX ' + QUOTENAME('nci_wi_Table_C17CD092-869C-407C-8623-9F05C8D64E67') + ' ON [dbo].[Testing] REBUILD'

EXEC(@SQL)

Failing that its probably time to just rename your index to exclude the GUID.

Upvotes: 2

Related Questions