If you’re getting SQL Server error Msg 129 that reads “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100“, it appears that you’re trying to set the fillfactor of an index to zero.
While it’s true that zero is the default setting, we can’t actually explicitly set the fillfactor to zero when creating or rebuilding an index.
But all is not lost. Below are two solutions to this problem.
Continue readingIf you’ve got a primary key in SQL Server that you need to enable, you’ll need to enable its index. The index is how we enable and disable primary key constraints in SQL Server.
More specifically, we enable a primary key index (and other indexes) by rebuilding them.
Below is an example of enabling a primary key in SQL Server.
Continue readingIf you’re getting SQL Server error 1913 that reads something like “The operation failed because an index or statistics with name ‘IX_Employees_HireDate’ already exists on table ‘Employees’“, it appears that you’re trying to create an index with the same name of one that already exists.
In SQL Server, index names must be unique within a table or view, although they don’t have to be unique within a database. Also, index names must follow the rules of identifiers.
Continue readingIf 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.
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 readingIn 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.
If you’re getting SQL Server error 3723 that reads “An explicit DROP INDEX is not allowed on index…” it’s probably because you’re trying to drop a PRIMARY KEY or UNIQUE constraint by dropping its index.
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.
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 readingWe’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