If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘ON’“, it appears that you’re trying to recreate an existing index, but you’re using the wrong syntax for the DROP_EXISTING argument.

The same error occurs when using the wrong syntax to set DROP_EXISTING to OFF; “Incorrect syntax near the keyword ‘OFF’“.

Either way, to fix this issue, be sure to use the right syntax.

Example of Error

Here’s an example of code that produces the error:

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName)
WITH DROP_EXISTING = ON;

Output:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ON'.

Here, I used WITH DROP_EXISTING = ON when trying to create an index. This option enables us to recreate an index that already exists (typically to change its definition). When we use this option, SQL Server drops the index first, and then creates it again with the new definition.

But if we use the wrong syntax, then it returns this error.

We’ll get the same error if we use the incorrect syntax to set it to OFF:

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName)
WITH DROP_EXISTING = OFF;

Output:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ON'.

Regardless, the error is caused by us using the wrong syntax.

Solution

To fix the problem, we need to adjust our code so that we’re using the right syntax.

The problem with the above examples is that I omitted the parentheses when using DROP_EXISTING = ON (and OFF).

When we explicitly set this option to ON or OFF, we must use parentheses.

For example:

CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName)
WITH (DROP_EXISTING = ON);

Output:

Commands completed successfully.

Here, all I did was change WITH DROP_EXISTING = ON to WITH (DROP_EXISTING = ON).

The same applies when you’re trying to set DROP_EXISTING to OFF; simply surrounded it in parentheses.

However, OFF is the default setting. So you can omit the whole argument altogether if you prefer.

So, both of the following do the same thing:

-- Explicitly set DROP_EXISTING to OFF
CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName)
WITH (DROP_EXISTING = OFF);

-- Use the default setting (which is OFF)
CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName);

Output:

Commands completed successfully.

Now, this obviously assumes that there’s not an existing index with the same name, otherwise you’ll get a different error.