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.
Tag: how to
How to Extract Fields from Composite Values in PostgreSQL
In PostgreSQL, a composite value is stored as a comma separated list of values, surrounded by parentheses.
When we have a table that contains composite values, we can extract individual fields by using dot notation to refer to the specific field that we want to extract.
Continue readingPossible 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 readingUnderstanding 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.
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 readingFix 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.
How to Select an Element from an Array in PostgreSQL
PostgreSQL provides us with the ability to create arrays, extract element values from them, and more. When it comes to selecting values, there’s a special syntax that we must use in order to get the exact element that we want to select.
Basically, the syntax involves the array or column name, followed by a pair of square brackets with the subscript of the element that we want to select.
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.