SQL Server

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

by

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).

Read more

What is a Synonym in SQL Server?

by

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).

Read more

Fix Error 4512 “Cannot schema bind view” in SQL Server Due to “two-part format” Issue

by

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).

Read more

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

by

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.

Read more

How to DROP a Temporal Table in SQL Server

by

Temporal tables, introduced in SQL Server 2016, provide a powerful way to track historical data changes. However, there may come a time when you need to remove a temporal table from your database.

This article will guide you through the process of dropping a temporal table in SQL Server.

Read more

Fix Error 2788 “Synonyms are invalid in a schemabound object or a constraint expression” in SQL Server

by

If you’re getting SQL Server error 2788 that reads “Synonyms are invalid in a schemabound object or a constraint expression” it seems that you’re trying (whether intentionally or not) to create a schemabound object that includes a synonym in its definition, or a constraint with a synonym in its expression.

Read more

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

by

If you’re getting an error that reads something like “Truncate failed on table ‘test.dbo.ProductInventory’ because it is not a supported operation on system-versioned tables.” in SQL Server, it’s probably because you’re trying to truncate a system-versioned temporal table.

In SQL Server, if a table is defined as a temporal table, we can’t truncate it while it’s using system-versioning.

Read more

How to TRUNCATE a Temporal Table in SQL Server

by

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.

Read more