If you’re getting SQL Server error 3723 that reads “An explicit DROP INDEX is not allowed on index…” it’s probably because you’re trying to drop a PRIMARY KEY or UNIQUE constraint by dropping its index.
Tag: errors
Possible Reason for Error 1914 “object is not a user table or view” in SQL Server
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.
Continue readingFix Error 156 “Incorrect syntax near the keyword ‘OR'” in SQL Server When Creating a Filtered Index
If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘OR’” when creating a filtered index in SQL Server, it’s probably because you’re using the OR operator in your WHERE clause.
Filtered indexes don’t support the OR operator.
However, filtered indexes do support the IN operator, and so it’s possible to change your query to use that.
So to fix this issue, try the IN operator instead.
Fix Error 8655 “The query processor is unable to produce a plan because the index … on table or view … is disabled.
If you’re getting SQL Server error 8655 that reads something like “The query processor is unable to produce a plan because the index ‘PK__Employee__7AD04FF1A39ECAB1’ on table or view ‘Employees’ is disabled“, it’s probably because the clustered index for the table is disabled.
Continue readingFix Error 8111 “Cannot define PRIMARY KEY constraint on nullable column in table” in SQL Server
If you’re getting SQL Server error 8111 that reads something like “Cannot define PRIMARY KEY constraint on nullable column in table ‘Employees’“, it’s probably because you’re trying to add a PRIMARY KEY constraint to an existing column that is nullable. In other words, the column isn’t defined with a NOT NULL constraint.
We can only add primary keys to columns that are defined as NOT NULL.
To fix this issue, define the table as NOT NULL before attempting to add the primary key.
Fix Error “Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong” in SQL Server
If you’re getting SQL Server error Msg 15248 that reads something like “Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong’“, it appears that you’re trying to perform an operation on an index, but you’ve got the naming syntax slightly wrong. Perhaps you’re trying to rename it.
When we do stuff like rename an index, we need to include the table name when referring to the existing index. It’s possible that you’ve not included this in your code.
To fix this issue, be sure to include the table name.
Continue readingHow to Delete an Index in SQL Server
If you find yourself with an index in SQL Server that you no longer need, you may decide to disable it, or you may opt to get rid of it altogether. That way you can declutter your database, free up space, and perhaps help improve performance of updates to the data.
Typically, to delete an index in SQL Server, we use the DROP INDEX statement. There are cases where we might drop it via other means (for example, if it’s implemented as part of a PRIMARY KEY or UNIQUE constraint – also shown below), but DROP INDEX is usually the go to command for such operations.
Fix Error 11415 “Object … cannot be disabled or enabled. This action applies only to foreign key and check constraints” in SQL Server
If you’re getting SQL Server error 11415 that reads something like “Object ‘UQ_Employees_Email’ cannot be disabled or enabled. This action applies only to foreign key and check constraints“, it’s probably because you’re trying to disable either a DEFAULT, UNIQUE or PRIMARY KEY constraint.
Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server
If you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.
To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.
The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).
Continue readingFix Error 4512 “Cannot schema bind view” in SQL Server Due to “two-part format” Issue
If you’re getting an error that reads something like “Cannot schema bind view ‘vEmployees’ because name ‘Employees’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself” it could be that you’re trying to create a schema bound view, but you’re not using a two-part format for names (such as the table names within the view).
Continue reading