Introduction to Indexing in SQL

Posted on by Ian

When working with databases, performance can be very important. This is especially true in production environments where the end users expect their queries and reports to be generated within seconds (or even milliseconds).

While blistering fast queries may be the norm with smaller datasets, as our databases grow larger and more complex, it can become much more of a challenge to keep our queries nice and snappy. When working with smaller datasets, it’s often possible to get lightning speed results even when not optimizing for speed. But as the datasets grow larger, we need more efficient tools and techniques to retrieve and manipulate data. One such tool is the index.

Continue reading →

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

Posted on by Ian

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

A Quick Look at the FACTORIAL() Function in PostgreSQL

Posted on by Ian

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

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

Posted on by Ian

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

How to Update an Array in PostgreSQL

Posted on by Ian

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