In most DBMSs (including SQLite), we can create indexes to improve query performance by allowing faster access to data. However, you might occasionally need to rename an index for whatever reason, be it for clarity, consistency, organizational purposes, or some other reason.
Continue readingTag: indexes
How to Delete an Index in SQLite
If you’ve got an index in a SQLite database that you no longer need, you should probably get rid of it. Keeping unused indexes can be a drag on database performance, especially if your data is being regularly updated.
This article shows you how to delete an index in SQLite.
Continue readingHow 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 readingIdentify 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 reading5 Ways to List Indexes in PostgreSQL
There are several ways that we can list all indexes in PostgreSQL. These include utilizing various system catalog views and psql commands. Below are five options for returning a list of indexes in PostgreSQL, along with examples of how to filter the results by index name, table name, and other criteria.
Return All Indexes on a Table in SQL Server
Sometimes when working with SQL Server, we need to get a quick list of indexes on a given table.
Here are three queries that return all indexes on a given table in SQL Server.
Continue readingChanging 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.
PostgreSQL \di Command Explained
The \di command can be used to list indexes in the current database when using psql (PostgreSQL’s command-line tool). This meta-command can display all indexes, or just indexes that match certain patterns. Here’s an in-depth explanation of how \di works, its options, and examples of its various uses.
Quick Query to Identify Unused Indexes in SQL Server
Below is a query that we can use to quickly identify unused indexes in SQL Server. More specifically, it returns indexes that are updated, but not used.
Continue reading3 Ways to Find a Table’s Primary Key Constraint Name in SQL Server
Primary keys are fundamental to relational database design, ensuring each row in a table can be uniquely identified. They help to maintain data integrity in our databases.
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 constraint itself.
Below are three examples of how we can do this.
Continue reading