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 readingCategory: SQL Server
Fix Error 1505 “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” in SQL Server
If 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.
Fix Error “Drop table operation failed on table … because it is not a supported operation on system-versioned temporal tables” in SQL Server
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 readingHow to DROP a Temporal Table in SQL Server
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.
Continue readingFix Error 2788 “Synonyms are invalid in a schemabound object or a constraint expression” in SQL Server
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.
Continue readingFix Error “Truncate failed on table … because it is not a supported operation on system-versioned tables.” in SQL Server
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.
Continue readingHow to TRUNCATE a Temporal Table in SQL Server
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 readingTime Travel in SQL Server: Using Temporal Tables for Historical Data Analysis
Temporal tables, introduced in SQL Server 2016, provide a powerful mechanism for tracking historical changes to data. This feature is particularly useful for auditing purposes, allowing organisations to maintain a complete history of data modifications without the need for complex triggers or custom logging solutions.
In this article, we’ll explore how to implement and use temporal tables for auditing in SQL Server, along with examples to demonstrate.
Continue readingSQL Server WHILE Loop (with Examples)
The WHILE loop in SQL Server is a control-flow statement that allows us to repeatedly execute a block of code as long as a specified condition is true. It’s useful for iterative tasks and processing data in batches.
Boost Your Data Analysis with These SQL Rank Functions
Most SQL databases have a handful of “ranking” functions that allow us to rank data. By “handful”, I mean there’s a common set of around six SQL rank functions that most of the major RDBMSs appear to support.
SQL rank functions allow us to assign ranks or row numbers to result sets.
Continue reading