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 readingIn PostgreSQL, we can use the ... IS NORMALIZED expression to check whether or not a given Unicode string is in a specified Unicode normalization form.
By default, it checks whether it’s in the NFC form, but we also have the option of specifying NFD, NFKC, or NFKD.
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 readingEver found yourself needing to update a bunch of rows in your database, but the condition for the update depends on data from another table? That’s where UPDATE with a subquery comes in handy. Let’s break it down.
In PostgreSQL, factorial() is a mathematical function that returns the factorial of a given integer. We pass the integer (it accepts bigint), and it returns the factorial as a numeric value.
In mathematics, the factorial is the product of all positive integers less than or equal to a given positive integer. It’s denoted by that integer and an exclamation point.
Continue readingIn PostgreSQL, we can use the || operator to concatenate two arrays. We can also use it to append or prepend a (compatible) non-array value to an array.
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 readingPostgreSQL offers a handy feature called domains that can greatly enhance data integrity and simplify database design. In this article, we’ll explore what domains are, how they work, and when to use them in our database projects.
Continue readingPostgreSQL allows us to create arrays and store them in the database as an array type. We can then go back later and update those arrays as required.
We can either replace the whole array, append or prepend elements, or update individual elements within the array. When we update individual elements, we can update one element or a whole slice. Below are examples of updating an array in PostgreSQL.
Continue readingIf you’re trying to use either lpad() or rpad() to pad a string in PostgreSQL but it’s just not working, maybe it’s because of the reasons listed below.
Fortunately this issue has a simple fix, and it can be resolved within seconds.
Continue reading