Tag: mssql

Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server

Posted on by Ian

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 reading →

What is a Synonym in SQL Server?

Posted on by Ian

In SQL Server, a synonym is a database object that provides an alternative name for another database object, such as a table, view, stored procedure, or function. Synonyms act as aliases, allowing us to reference objects using different names without changing the underlying object itself.

The underlying object that the synonym references is typically referred to the base object. Either way, the synonym allows us to reference the base object without actually referring to its name (we refer to the synonym’s name instead).

Continue reading →

Fix Error “Drop table operation failed on table … because it is not a supported operation on system-versioned temporal tables” in SQL Server

Posted on by Ian

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 →

How to TRUNCATE a Temporal Table in SQL Server

Posted on by Ian

Temporal tables in SQL Server provide a powerful way to track historical data changes. However, when it comes to clearing out data from these tables, the standard TRUNCATE statement doesn’t work the same as it does on normal (non-temporal) tables.

This article will guide you through the process of truncating a temporal table while maintaining its integrity.

Continue reading →

Fix “Invalid object name ‘GENERATE_SERIES'” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 208 that reads “Invalid object name ‘GENERATE_SERIES’“, it could be that you’re calling GENERATE_SERIES() in a version of SQL Server that doesn’t support this function.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x), and so if we try to call it in an earlier version of SQL Server, we’ll get the above error.

So if you’re running this on an earlier version, you’ll need to upgrade before you can run it successfully. You’ll also need to run it on a database with a compatibility level of at least 160.

Continue reading →