JSON (JavaScript Object Notation) has become a ubiquitous data format for storing and exchanging information. SQL Server 2016 and later versions provide robust support for working with JSON data. This article explores some useful tips and tricks for handling JSON in T-SQL.
Continue readingCategory: SQL Server
How to Delete an Index in SQL Server
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.
Fix 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.
Fix Error 11415 “Object … cannot be disabled or enabled. This action applies only to foreign key and check constraints” in SQL Server
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.
Nested WHILE Loops in SQL Server: A Beginner’s Guide with Examples
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.
Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server
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 readingHow to Delete a UNIQUE Constraint in SQL Server
If you’ve got a table that has a UNIQUE constraint, there may come a time where you need to remove it. Whether it’s a permanent thing or temporary, you can easily delete the constraint using the following method.
What is a Synonym in SQL Server?
In SQL Server, a synonym is a database object that provides an alternative name for another database object, such as a table, view, stored procedure, or function. Synonyms act as aliases, allowing us to reference objects using different names without changing the underlying object itself.
The underlying object that the synonym references is typically referred to the base object. Either way, the synonym allows us to reference the base object without actually referring to its name (we refer to the synonym’s name instead).
Continue readingFix Error 4512 “Cannot schema bind view” in SQL Server Due to “two-part format” Issue
If you’re getting an error that reads something like “Cannot schema bind view ‘vEmployees’ because name ‘Employees’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself” it could be that you’re trying to create a schema bound view, but you’re not using a two-part format for names (such as the table names within the view).
Continue readingFix Error 1505 “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” in SQL Server
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.