SQLite provides several ways to determine how many rows are affected by SQL statements such as INSERT, UPDATE or DELETE.
One way of achieving this is with the changes() function. This function returns the number of rows modified by the most recent SQL statement executed in the current session.
Another way is with the .changes dot command.
In this article, we’ll look at an example that uses these options to get the number of rows changed by various SQL statements.
Continue readingOne feature that sets SQLite apart from most other RDBMSs is the concept of WITHOUT ROWID tables. This is an optimization feature designed to improve performance and reduce storage space for certain use cases.
This article explores what WITHOUT ROWID tables are, how they work, their benefits, and when to use them.
One of SQLite’s unique features is the WITHOUT ROWID table, which can be used to optimize performance and storage in specific scenarios.
While it’s easy enough to create a WITHOUT ROWID table (just add WITHOUT ROWID to the definition), how to identify a WITHOUT ROWID table might not be so obvious.
In this article, we’ll start by briefly revising what WITHOUT ROWID tables are and how they differ from ordinary tables. Then we’ll look at how to identify these tables by using SQLite’s PRAGMA commands.
The following table outlines the inbuilt mathematical functions available in SQLite.
Continue readingSQLite, one of the most widely used database engines, is known for its lightweight design, ease of use, and adherence to most aspects of the SQL standard. However, one notable deviation from the standard lies in its handling of PRIMARY KEY constraints. Unlike the SQL standard, SQLite allows NULL values in primary key columns in some cases.
Let’s look at the reasons behind this behavior, and explore the implications of NULL values in primary key columns. We’ll also examine SQLite’s treatment of NULL values as distinct for uniqueness constraints.
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.
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.
Continue readingThe TAN() function in SQLite calculates the tangent of an angle provided in radians. The tangent is the ratio of sine to cosine (or the ratio of the opposite side to the adjacent side in a right triangle).
SQLite is a lightweight, self-contained database engine renowned for its simplicity and flexibility. One of its unique features is the way it handles data types through type affinity, which determines how SQLite associates values with storage classes.
Unlike many database systems that enforce strict type constraints, SQLite’s type affinity system is more flexible, accommodating a broader range of data.
Continue readingIn SQLite, the pow() function calculates the result of raising one number to the power of another. In other words, it performs an exponentiation operation.
The pow() function is particularly useful for mathematical operations where powers or exponents are required.
It’s also available as power(). Both syntaxes do the same thing.