If you’re getting SQL Server error 13680 that reads something like “Column ‘details’ on table ‘table_name’ is not of JSON data type, which is required to create a JSON index on it.” it looks like you’re trying to create a JSON index on a non-JSON column. You can only create JSON indexes on columns defined with the JSON type.
To fix this issue, be sure that the column is of JSON type before running CREATE JSON INDEX on it.
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.
Before SQL Server 2025, indexing JSON data meant creating a computed column to extract the value you cared about, then indexing that column. It works, and it’s still a solid approach, but it requires you to know upfront which paths you’ll query, maintain separate columns for each one, and keep those columns in sync with any schema changes. SQL Server 2025 introduces JSON indexes as a native alternative. It’s a single index structure that covers the JSON column directly, without the computed column overhead.
JSON indexes are currently in preview and only available in SQL Server 2025 on-premises. They aren’t available yet on Azure SQL Database, Managed Instance, or Fabric. That said, things could be different by the time you read this article.
SQL Server has a concept of “missing indexes”. And no, it’s not referring to an index that used to be there but has now disappeared. Rather, the missing index concept is designed to help us improve the performance of our database.
A missing index is an index that doesn’t yet exist on your table but probably should. SQL Server actually tracks queries that would benefit from indexes and stores suggestions in a set of dynamic management views (DMVs) called the missing index DMVs.
When you run a query and the optimizer thinks that this would be way faster with an index on those columns, it logs that suggestion. Over time, these suggestions accumulate, giving you a prioritized list of indexes that could improve your database’s performance.
A columnstore index is a type of database index that stores data by column rather than by row. Traditional indexes (and tables) store data row-by-row, where all the values for a single record are kept together. Columnstore indexes flip this around, storing all values from a single column together instead.
This might seem like a small difference, but it fundamentally changes how the database reads and processes data. Columnstore indexes are designed for analytical queries that scan large amounts of data, performing aggregations, calculations, and filtering across millions or billions of rows.
A query hint is a directive you add to your SQL statement that tells the database optimizer how to execute that query. You’re basically overriding the optimizer’s judgment with your own instructions.
Most of the time, your database’s query optimizer does a pretty solid job figuring out the best execution plan. It analyzes statistics, indexes, and table structures to determine the most efficient path. But sometimes you know better (or at least you think you do) and that’s where query hints can be useful.
An index scan is a method databases use to retrieve data by reading through an index from start to finish. The database reads every entry in the index sequentially, checking each one to see if it matches your query conditions.
This is different from an index seek, where the database jumps directly to specific values in the index. Index scans happen when the database determines it needs to examine a large portion of the index, or when it can’t use the index’s sorted structure to go directly to the data you need.
An index seek is the fastest way a database can use an index to find data. When you perform a seek, the database jumps directly to the exact location in the index where your data lives, grabs what it needs, and moves on. No scanning, no reading through irrelevant entries. Just a precise lookup using the index’s sorted structure.
This is fundamentally different from an index scan, where the database reads through the index sequentially. Seeks are only possible when your query conditions allow the database to pinpoint specific index entries without examining others.
In SQLite, a partial index includes only the rows that meet a specified condition, set by a WHERE clause in the index definition. This is different from a regular, or “full,” index, which covers all rows in a table. Partial indexes are helpful when you frequently query specific subsets of data, as they can improve query performance and reduce storage.
If you ever need to check a table to see if it contains any partial indexes, you can use either of the following methods.