Tag: mssql

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 →

Fix Error 8111 “Cannot define PRIMARY KEY constraint on nullable column in table” in SQL Server

Posted on by Ian

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.

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 →

What is a Heap in SQL Server?

Posted on by Ian

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 →

How to Delete an Index in SQL Server

Posted on by Ian

If you find yourself with an index in SQL Server that you no longer need, you may decide to disable it, or you may opt to get rid of it altogether. That way you can declutter your database, free up space, and perhaps help improve performance of updates to the data.

Typically, to delete an index in SQL Server, we use the DROP INDEX statement. There are cases where we might drop it via other means (for example, if it’s implemented as part of a PRIMARY KEY or UNIQUE constraint – also shown below), but DROP INDEX is usually the go to command for such operations.

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 11415 “Object … cannot be disabled or enabled. This action applies only to foreign key and check constraints” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 11415 that reads something like “Object ‘UQ_Employees_Email’ cannot be disabled or enabled. This action applies only to foreign key and check constraints“, it’s probably because you’re trying to disable either a DEFAULT, UNIQUE or PRIMARY KEY constraint.

Continue reading →

Nested WHILE Loops in SQL Server: A Beginner’s Guide with Examples

Posted on by Ian

WHILE loops are a fundamental concept in T-SQL programming, allowing us to execute a block of code repeatedly as long as a specified condition is true. Nested WHILE loops take this concept further by placing one WHILE loop inside another, enabling more complex iterations.

This guide will walk you through the basics of nested WHILE loops in SQL Server, complete with simple examples and a demonstration of the BREAK statement.

Continue reading →