Understanding SQL Server Smallint for Devs

As a developer, understanding the different data types in SQL Server is crucial to designing a well-optimized database. One such data type is smallint. In this article, we will explore everything you need to know about SQL Server smallint.

What is Smallint?

Smallint is a numeric data type in SQL Server that occupies 2 bytes of storage. It can store integer values between -32,768 to 32,767. Smallint is a type of integer data type that is smaller than int but larger than tinyint. It is commonly used to store small integer values and helps in reducing storage space, as compared to int.

Storage Comparisons: Smallint vs. Int

As discussed earlier, smallint occupies 2 bytes of storage, whereas int occupies 4 bytes. This means that smallint can store smaller integer values, but int is more suitable for storing larger values. Here’s a comparison of the storage required for smallint and int:

Data Type	Storage Required
Smallint	2 bytes
Int	4 bytes

When to use Smallint?

Smallint is usually used for columns that store small integer values, such as an ID or a count. If the column is expected to store larger values, such as the age of a person, int should be used instead. However, if the column is expected to store even smaller values, like a boolean value or a day of the week, tinyint or bit should be used instead.

Working with Smallint

Declaring Smallint Variables

When declaring a variable in SQL Server, you must specify the data type of the variable. To declare a smallint variable, use the SMALLINT keyword. Here’s an example:

DECLARE @myVariable SMALLINT

Creating a Table with Smallint Column

To create a table in SQL Server with a smallint column, use the SMALLINT keyword. Here’s an example:

CREATE TABLE myTable(id SMALLINT PRIMARY KEY,name VARCHAR(50),age SMALLINT)

FAQs

Can Smallint store negative values?

Yes, smallint can store negative values. It can store integer values between -32,768 to 32,767.

What happens if I try to save a value that is larger than the maximum limit of smallint?

If you try to save a value that is larger than the maximum limit of smallint, an error will be thrown.

Can smallint be used as a primary key?

Yes, smallint can be used as a primary key.

What is the maximum size of a smallint column?

The maximum size of a smallint column is 2 bytes.

Conclusion

Smallint is a useful data type in SQL Server that is commonly used to store small integer values. Its smaller size compared to int makes it ideal for reducing storage space. As a developer, it is essential to know when to use smallint and when to use other data types, such as int or tinyint.