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.
2 Ways to Insert an Array into a Table in PostgreSQL
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 readingFix Error 8111 “Cannot define PRIMARY KEY constraint on nullable column in table” in SQL Server
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.
How to Update Multiple Columns in SQL with a Subquery
In SQL, it’s not unusual to see subqueries in SELECT statements, where they can help narrow down the results based on a complex condition.
But the SELECT statement isn’t the only place we can put a subquery.
We can use subqueries in an UPDATE statement, and we can update multiple rows too, if required.
In this article, we look at how to update multiple columns by using a SQL subquery in the UPDATE statement.
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 reading4 Ways to Prepend an Element to an Array in PostgreSQL
When it comes to prepending a values to arrays in PostgreSQL, we have a number of options available to us. Below are four methods we can use to prepend a value to an array in PostgreSQL.
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 readingWhat is a Heap in SQL Server?
In SQL Server, a heap is a table without a clustered index. Unlike tables with clustered indexes, which sort data in a specific order, heaps store data in no particular order. That’s because the clustered index is what determines how the table is stored and sorted (it’s sorted on the clustered index’s key column).
If there’s no clustered index, then data is initially stored in the order in which the rows are inserted, although the database engine may change this in order to store the rows more efficiently.
Continue reading