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 readingTag: indexes
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.
What is a Clustered Index 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 readingWhat is a Nonclustered Index in SQL Server?
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 readingFix Error 8655 “The query processor is unable to produce a plan because the index … on table or view … is disabled.
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 readingHow to Disable a Primary Key in SQL Server
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 readingHow to Change SQL Server’s Default Fillfactor
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.
What is a Composite Index in SQL?
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 readingFix Error “Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong” in SQL Server
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 readingFix Error 159 “Must specify the table name and index name for the DROP INDEX statement” in SQL Server
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.