SQLite SMALLINT

The SQLite SMALLINT data type is used to store small integer values, typically with a size of 2 bytes (16 bits). It can hold whole numbers within a limited range, making it suitable for storing small integers like counters, flags, or other numeric values with a small range of possible values.

Syntax

Here is the syntax for defining a column with the SMALLINT data type in a SQLite table:

CREATE TABLE table_name (
    column_name SMALLINT
);

You can also specify additional constraints, such as PRIMARY KEY, NOT NULL, or DEFAULT, as needed when defining the column.

Example

Here’s an example of creating a SQLite table with a SMALLINT column and inserting some data:

-- Create a table to store employee information
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age SMALLINT,
    department TEXT
);

-- Insert data into the table
INSERT INTO employees (first_name, last_name, age, department)
VALUES
    ('John', 'Doe', 30, 'HR'),
    ('Jane', 'Smith', 28, 'IT'),
    ('Bob', 'Johnson', 25, 'Sales'),
    ('Alice', 'Williams', 27, 'Marketing');

In this example, we’ve created a table named employees with a SMALLINT column called age. The age column is used to store the ages of the employees as small integer values.

You can perform various operations on SMALLINT columns, such as selecting, updating, and filtering data based on the values stored in the column, just like with other data types in SQLite.

Keep in mind that the specific range of values that can be stored in a SMALLINT column may vary depending on the SQLite version and configuration, but it is typically designed to handle small integers comfortably within a 16-bit range.