How to Create a Partial Index in SQLite

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.

Continue reading

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

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.

Continue reading

How to Create an Index in SQLite

In database management systems (DBMSs) such as SQLite, indexes can be used to optimize the performance of queries, especially for larger datasets where querying can become time-consuming. An index allows the database to locate rows more quickly without scanning every row in a table.

This article explains how to create an index in SQLite, along with a simple example.

Continue reading

Identify a Table’s Primary Key’s Index in SQL Server

There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.

For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).

The query below can help if you need to find the name of a primary key’s index for a given table.

Continue reading

Changing the Default Fill Factor Doesn’t Work? Check These Two Things.

If you’ve gone through the steps for changing the default fill factor in SQL Server, but it doesn’t seem to be taking effect on newly created indexes, it could be because you’ve missed one or two crucial steps.

This issue can also be seen by comparing the value and value_in_use columns when querying the sys.configuration view or using sp_configure to view the current setting.

Continue reading