Author: Ian

Using the VARIADIC Keyword with the FORMAT() Function in PostgreSQL

Posted on by Ian

When we use the format() function in PostgreSQL, we can pass any number of strings to insert into the resulting formatted string in positions that are specified in the first argument. When we do this, we might typically pass the strings as separate arguments. But this isn’t the only way to do it.

We have the option of passing the strings as array elements. That is, we can pass an array to the function, and have it extract each array element as though it’s a separate argument to insert into the formatted string.

Continue reading →

Fix Error 156 “Incorrect syntax near the keyword ‘OR'” in SQL Server When Creating a Filtered Index

Posted on by Ian

If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘OR’” when creating a filtered index in SQL Server, it’s probably because you’re using the OR operator in your WHERE clause.

Filtered indexes don’t support the OR operator.

However, filtered indexes do support the IN operator, and so it’s possible to change your query to use that.

So to fix this issue, try the IN operator instead.

Continue reading →

What is a Clustered Index in SQL Server?

Posted on by Ian

There are around a dozen types of index in SQL Server, but the most commonly used index type would have to be the clustered index. By default, a clustered index is automatically created when we create a primary key, and so if your tables have primary keys, then there’s a pretty good chance you’ve got clustered indexes all over your database.

But what exactly is a clustered index?

Let’s delve into what a clustered index is, how it works, its relationship with primary keys, and why it’s so important for our database performance.

Continue reading →

What is a Nonclustered Index in SQL Server?

Posted on by Ian

We’ll often hear SQL Server developers talk about adding indexes to a table to improve the performance of some of the more heavily run queries. Such queries could be quite complex, and/or search through large tables, perhaps with lots of joins, and generally take a long time to complete.

But it’s not just a matter of saying “add an index” and being done with it. We need to decide how to design the index, and even before that, what type of index to add.

Two of the most commonly used index types in SQL Server are the clustered index and the nonclustered index.

In this article, we’ll explore what a nonclustered index is, how it works, and when we should use it to enhance our database performance.

Continue reading →

How to Select an Element from an Array in PostgreSQL

Posted on by Ian

PostgreSQL provides us with the ability to create arrays, extract element values from them, and more. When it comes to selecting values, there’s a special syntax that we must use in order to get the exact element that we want to select.

Basically, the syntax involves the array or column name, followed by a pair of square brackets with the subscript of the element that we want to select.

Continue reading →

Fix Error 8655 “The query processor is unable to produce a plan because the index … on table or view … is disabled.

Posted on by Ian

If you’re getting SQL Server error 8655 that reads something like “The query processor is unable to produce a plan because the index ‘PK__Employee__7AD04FF1A39ECAB1’ on table or view ‘Employees’ is disabled“, it’s probably because the clustered index for the table is disabled.

Continue reading →

How to Disable a Primary Key in SQL Server

Posted on by Ian

Under most circumstances, disabling a primary key is a bad idea. A very bad idea. After all, we implement primary keys in the name of data integrity, and by disabling a primary key, we could compromise that effort.

But there may be cases where you need to disable a primary key, for one reason or another. For example, to facilitate data migration efforts, or bulk inserts, to perform certain maintenance tasks, or simply to insert dummy data in your development environment.

Whatever the reason, here’s how to disable a primary key in SQL Server.

Continue reading →

How to Change SQL Server’s Default Fillfactor

Posted on by Ian

Fillfactor is an option that we can set on SQL Server indexes in order to fine tune index data storage and performance. It determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.

The default fillfactor can be set as a configuration option. SQL Server sets this value to 0, and so this is what’s used when we create an index without specifying a fillfactor. The value of 0 is actually the equivalent of 100 (or meaning 100%). In other words, by default, the leaf-level pages are filled to capacity.

Continue reading →

2 Ways to Insert an Array into a Table in PostgreSQL

Posted on by Ian

PostgreSQL allows us to create columns that store arrays. That is, we can define the column as an array column so that we can insert arrays into the column. This enables us to work with the arrays in the columns without it being confused with plain text.

We have a couple of options for inserting arrays into columns. One is to use an array literal. Another is to use an array constructor. Examples of each of these options are below.

Continue reading →

Fix Error 8111 “Cannot define PRIMARY KEY constraint on nullable column in table” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 8111 that reads something like “Cannot define PRIMARY KEY constraint on nullable column in table ‘Employees’“, it’s probably because you’re trying to add a PRIMARY KEY constraint to an existing column that is nullable. In other words, the column isn’t defined with a NOT NULL constraint.

We can only add primary keys to columns that are defined as NOT NULL.

To fix this issue, define the table as NOT NULL before attempting to add the primary key.

Continue reading →