Fix Error “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server

If you’re getting an error that reads “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server, it appears that you’re trying to view or set the default fill factor configuration option.

In SQL Server, fill factor is considered an advanced configuration option. By default, advanced options aren’t available for viewing and changing. However, we can use the following technique to make them available.

Continue reading

Understanding Fillfactor in SQL Server

One of the various options we have when creating or rebuilding indexes in SQL Server is specifying a fillfactor. If we create or rebuild an index without specifying a fillfactor, then the default fillfactor is applied.

In some cases, using the default fillfactor may be fine, even ideal. In other cases it could be less than ideal, even terrible.

Let’s look at what fillfactor is, how it works, and how we can use it to enhance database performance.

Continue reading

5 Ways to Return UNIQUE Constraints in SQL Server

In SQL Server, we can use UNIQUE constraints to ensure that a column (or columns) contain only unique values. When we have a UNIQUE constraint against a column, the system will prevent any duplicate values are entered into that column.

Sometimes we need to return a list of UNIQUE constraints, so that we simply know what we’re working with. Other times we might want to create scripts for all of our UNIQUE constraints so that we can recreate the constraints later.

Regardless of the reason, here are five methods for returning UNIQUE constraints in a SQL Server database.

Continue reading

Possible Reason for Error 1914 “object is not a user table or view” in SQL Server

If you’re getting an error that reads something like “Index cannot be created on object ‘Customers’ because the object is not a user table or view” it could be that you’re trying to create an index, but there’s also a synonym of the same name (but in a different schema). It would appear that SQL Server thinks that you’re trying to create the index on the synonym instead of the table or view of the same name.

Continue reading

Understanding the BREAK Keyword in SQL Server WHILE Loops

WHILE loops are a powerful feature in most programming languages that allow developers to execute a block of code repeatedly as long as a specified condition is true. However, there are times when we may need to exit a loop prematurely based on certain criteria. SQL Server caters for this possibility with the BREAK keyword.

In this article, we’ll explore how to effectively use the BREAK keyword within WHILE loops in SQL Server.

Continue reading

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

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

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