indexes

What is a Missing Index in SQL Server?

by

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.

Read more

What is a Columnstore Index?

by

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.

Read more

What is a Query Hint?

by

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.

Read more

What is an Index Scan?

by

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.

Read more

What is an Index Seek?

by

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.

Read more

2 Ways to Check if a Table has any Partial Indexes in SQLite

by

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.

Read more

How to Check a Table for Unique Indexes in SQLite

by

Being able to ensure uniqueness in database tables is critical for maintaining data integrity. SQLite allows us to create unique indexes in order to enforce uniqueness, and it provides us with tools to check the unique indexes associated with a table.

This article outlines how to use the SQLite PRAGMA index_list() command to check a table for unique indexes.

Read more

How to Create a Unique Index in SQLite

by

SQLite is a lightweight, serverless SQL database engine commonly used for developing mobile applications, web applications, and embedded systems. Among its various functionalities, SQLite allows developers to create indexes, including unique indexes, to enhance data retrieval speed and enforce constraints on data integrity.

A unique index ensures that all values in a specific column, or combination of columns, are distinct, preventing duplicate entries.

In this guide, we’ll walk through the basics of unique indexes in SQLite, explore when and why to use them, and provide examples of how to create them.

Read more

How to Create a Partial Index in SQLite

by

Partial indexing is a feature in many DBMSs, including SQLite, that allows developers to optimize database performance by creating indexes only for specific subsets of data. This can significantly reduce the index size and improve query speed, especially in cases where only a portion of the data is frequently queried.

In this article, we’ll look at how to create a partial index in SQLite, why it’s useful, and provide an example to illustrate its use.

Read more

Why the Primary Key Might Not Appear in PRAGMA index_list() in SQLite

by

In most relational database management systems (RDBMSs) the PRIMARY KEY is used to define the unique row identifier for a table. But in SQLite, not all primary keys are handled the same way when it comes to indexing.

Depending on how the primary key is defined in a table, it may or may not show up in the list of indexes returned by the PRAGMA index_list() command. In particular, when the primary key is an INTEGER PRIMARY KEY, SQLite doesn’t explicitly create a separate index for it.

This article will explain why this happens and provide examples with different types of primary key definitions.

Read more