SQL Server SMALLINT Data Type

The SQL Server SMALLINT data type is used for storing integers and occupies 2 bytes of storage space.

Syntax

The syntax for the SQL Server SMALLINT data type is as follows:

SMALLINT [NOT NULL]

Usage

The SQL Server SMALLINT data type is typically used for storing small integer values. Its range is from -32,768 to 32,767.

Here are some example scenarios where the SMALLINT data type is suitable:

Storing years
Storing months
Storing days of the week
Storing hours of the day
Storing order quantities
Storing inventory quantities
Storing product quantities

Examples

Here are two examples of using the SQL Server SMALLINT data type.

Example 1

Create a table named Sales with two columns SaleID and SaleYear. SaleID is of integer type and SaleYear is of SMALLINT type. Insert some data into the table and perform some query operations.

CREATE TABLE Sales (
  SaleID INT PRIMARY KEY,
  SaleYear SMALLINT
);

INSERT INTO Sales VALUES (1, 2020);
INSERT INTO Sales VALUES (2, 2021);
INSERT INTO Sales VALUES (3, 2019);
INSERT INTO Sales VALUES (4, 2022);
INSERT INTO Sales VALUES (5, 2018);

SELECT * FROM Sales;

After running the above code, the following result will be obtained:

SaleID	SaleYear
1	2020
2	2021
3	2019
4	2022
5	2018

Example 2

Create a table named Inventory with two columns ProductID and UnitsInStock. ProductID is of integer type and UnitsInStock is of SMALLINT type. Insert some data into the table and perform some query operations.

CREATE TABLE Inventory (
  ProductID INT PRIMARY KEY,
  UnitsInStock SMALLINT
);

INSERT INTO Inventory VALUES (1, 100);
INSERT INTO Inventory VALUES (2, 50);
INSERT INTO Inventory VALUES (3, 25);
INSERT INTO Inventory VALUES (4, 75);
INSERT INTO Inventory VALUES (5, 10);

SELECT * FROM Inventory WHERE UnitsInStock > 50;