If you’re getting an error that reads something like “Index cannot be created on object ‘Customers’ because the object is not a user table or view” it could be that you’re trying to create an index, but there’s also a synonym of the same name (but in a different schema). It would appear that SQL Server thinks that you’re trying to create the index on the synonym instead of the table or view of the same name.

To fix this issue, be sure to provide the two-part name (including the schema) for the table or view that you’re trying to create the index on.

Example of Error

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

CREATE UNIQUE INDEX UX_Customers_Email 
ON Customers(Email);

Output:

Msg 1914, Level 16, State 2, Line 1
Index cannot be created on object 'Customers' because the object is not a user table or view.

In my case, I have a Customers table and a Customers synonym, but they belong to different schemas. The table is at Sales.Customers and the synonym is at dbo.Customers.

SQL Server seems to think that I want to create the index on the synonym, when I actually want to create it on the table.

Solution

We can easily fix this problem by using the two-part naming format (i.e. including the schema in the name):

CREATE UNIQUE INDEX UX_Customers_Email 
ON Sales.Customers(Email);

Output:

Commands completed successfully.

This time it worked without issue; the index was created.