If you’re getting SQL Server error 13681 that states “A JSON index already exists on column…” , it’s probably because you’re trying to create a JSON index on a column that already has one. In SQL Server, each JSON column can have no more than one JSON index.
The solution here is to simply stop trying to create a second JSON index. Another option would be to drop the existing one and create the new one.
If you’re trying to set the ONLINE option when creating a JSON index in SQL Server, but you’re seeing “Invalid usage of the option ONLINE in the CREATE JSON INDEX statement“, don’t worry – it might not be an error on your part.
It probably depends on things like, are you trying to set ONLINE = ON or ONLINE = OFF? And are you trying to do it around the time I write this article, or sometime in the future?
Let’s take a quick look at what could be happening when you try to use this option.
If you’re getting error Msg 5133 and error Msg 3156 in SQL Server, it would seem that you’re trying to restore a database to a different location, but you’re not specifying the different location. This can happen when you restore a database to a different environment that uses a file paths. A common cause of this error is when restoring a backup from a Windows environment to a Linux or Mac environment.
Fortunately, the fix is easy. All you need to do is map the logical file names to paths that actually exist in the new environment.
If you’ve spent any time working with SQL Server, you’ve almost certainly run into the “Violation of UNIQUE KEY constraint” error.
It’s one of those errors that can show up in a few different ways depending on how your data flows into the database. Sometimes it’s a data quality issue. Sometimes it’s a race condition. Sometimes it’s just a gap in the logic that nobody caught until production. Whatever the cause, there are quite a few different ways to handle it. Some reactively, some proactively. This article walks through the main ones.
If you’re getting SQL Server error 13683 stating “Invalid JSON paths in JSON index“, it sounds like you could be specifying overlapping paths in your JSON index definition.
A JSON index cannot contain overlapping paths.
To fix this, remove the overlap from the specified paths that make up the index definition.
If you’re getting SQL Server error 2744 that reads “Multiple identity columns specified for table…“, it looks like you’re trying to define a table to have more than one identity column.
SQL Server restricts identity columns to just one per table. If you try to add another one, you’ll get the above error.
The easiest way to address this issue is to leave the table with one identity table and be done with it. But that might not always be practical. Maybe you need a column that increments a different value than the identity column. Fortunately, there are ways of doing that.
If you’re getting an error in SQL Server that reads something like “ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or… etc etc“, it’s probably because you’re trying to add a NOT NULL column to a table that already contains data.
This error occurs due to the fact that the NOT NULL constraint will be violated for every row in the table. Think about it for a second. When you first add the column, there’s no data. It’s not until you run a subsequent INSERT statement (or some other process that populates the table) that you will get data. In the meantime, all values in your new column will be NULL. And that, of course, violates the NOT NULL constraint.
If you’re getting error 3234 that reads something like “Logical file ‘AdventureWorksLT_Data’ is not part of database ‘AdventureWorksLT2025’. Use RESTORE FILELISTONLY to list the logical file names.“, you’re referencing the wrong logical file names when restoring a database.
This issue can happen when you try to map the logical file names to a new location, but you get those logical file names wrong.
Fortunately there’s an easy fix. It involves looking up the actual logical file names, then modifying your RESTORE DATABASE statement accordingly.
If you’ve worked with SQL Server long enough, you’ve probably hit the dreaded error when trying to use ORDER BY inside a view or CTE. It usually shows up as something like:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
This rule can come as a bit of a surprise because it feels natural to want a query “pre-sorted” when wrapping it in a view or CTE. The problem is that SQL Server doesn’t allow the ORDER BY clause in this context unless it’s in conjunction with the clauses mentioned in the error message. Without such clauses, you need to explicitly request it at the outermost SELECT.
Let’s walk through an example of how to handle this.
When working with aggregate functions in SQL Server, it’s easy to overlook that certain datatypes have strict limits. If you’re summing values in large tables (or even just summing very large numbers), you might run into an arithmetic overflow error. This happens when the result of an aggregate exceeds the maximum value the datatype can hold.
Understanding how this works and how to prevent errors will help you write reliable queries.