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.
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 readingIf 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.
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 readingIf 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.
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.
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 readingIf 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 readingIf you’re getting an error that reads something like “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” it’s probably because you’re trying to create a UNIQUE constraint on a column that already contains duplicate values.
If you’re getting an error that reads something like “Drop table operation failed on table ‘db.dbo.TableName’ because it is not a supported operation on system-versioned temporal tables” in SQL Server, it’s probably because you’re trying to drop a temporal table that still uses system-versioning.
In SQL Server, if a table is defined as a temporal table, we can’t drop it while it’s using system-versioning.
If you really want to drop the table, turn off system-versioning first, then try again.
Continue reading