Tag: indexes

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 →

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 →

What is a Composite Index in SQL?

Posted on by Ian

When looking at ways to fix slow running SQL queries, one of the first things we might do is look at what indexes are available to support the query. If none are available, then we might look at creating one. And if there is an index, perhaps it doesn’t cater for the query as well as it could.

Indexes are often created on a single column, but this isn’t the only way to create an index. We can also create indexes on multiple columns. These are typically known as composite indexes.

Continue reading →

Fix Error “Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong” in SQL Server

Posted on by Ian

If you’re getting SQL Server error Msg 15248 that reads something like “Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong’“, it appears that you’re trying to perform an operation on an index, but you’ve got the naming syntax slightly wrong. Perhaps you’re trying to rename it.

When we do stuff like rename an index, we need to include the table name when referring to the existing index. It’s possible that you’ve not included this in your code.

To fix this issue, be sure to include the table name.

Continue reading →

Fix Error 159 “Must specify the table name and index name for the DROP INDEX statement” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 159 that reads “Must specify the table name and index name for the DROP INDEX statement“, it’s probably because you’re trying to drop an index without specifying the table name.

When we drop an index, we must specify both the index name and the table name.

To fix this issue, include both the index name and the table name in your DROP INDEX statement.

Continue reading →

Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.

To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.

The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).

Continue reading →

Understanding Filtered Indexes in SQL

Posted on by Ian

In SQL, indexing can be a great way to improve query performance across our database. We can create indexes to support the most commonly used queries, and these indexes can help our queries run much faster.

But it’s not always a matter of just “adding an index” and leaving it at that. If we really want the best bang for our buck, we usually need to look at how best to implement the index. There are many decisions that go into designing the best index for the job. And one such decision might be, should this be a filtered index?

But what exactly is a filtered index, and how can it improve our database efficiency?

Let’s find out.

Continue reading →

Fix Error 1505 “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” in SQL Server

Posted on by Ian

If you’re getting an error that reads something like “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” it’s probably because you’re trying to create a UNIQUE constraint on a column that already contains duplicate values.

Continue reading →