Radically simple guide to SQL Server data types

Today we are going to discuss the types of data in SQL Server and some good practices. One thing before we start, though. It is not possible to exhaust the topic of time representation in one article, much less so in one paragraph, so I’m not even going to try. I found a truly astonishing proof for that, but it, too, is too lengthy to fit in here.

Numerical data types

SQL Server supports 9 data types.

Integers

 BIGINT

The largest integer available in T-SQL. It is stored in 8 bytes and can represent values in range from -2^63 to 2^63-1 (a little bit less than 10^19). It’s just your everyday signed 64-bit integer, really.

Usage:
– When you expect to have billions of terabytes of data in your table. Using BIGINT as identity column is usually a code smell.
– When your data actually uses values up to trillions (10^18).
INT
Primary SQL Server numeric data type. It works just like your common 32-bit integer. It’s the perfect combination of size and memory use.

Usage:
– As IDENTITY for a reasonable data size.
– To store typical size numbers.
SMALLINT

Considerably smaller than INT. Since it’s a 16-bit integer, it is stored in 2 bytes and represents values from -32,768 to 32,767.

Usage:
– To save memory and disk space.
– Worth considering when the data range is used in everyday life.
TINYINT

The smallest type of data. It’s unsigned and stored in just one byte, and represents values from 0 to 255.

Usage:
– When the data range is small.
– To store binary flags.

Floating point numbers

FLOAT

Rounding up a standard for floating-point numbers. By default, it stores 53 bits of mantissa, but with FLOAT(n) syntax it is possible to set only two numbers: 24 and 53. Any value lesser than 24 is converted to 24, and values larger than 24 are converted to 53.
FLOAT is almost compliant with IEEE-754 standard. For it to actually be compliant, it would need to support three values that can show up as a result of the arithmetic operations. Those values are NaN, positive infinity and negative infinity.

Usage:
– Almost exclusively in a situation that meets the following two conditions:
a) application state depends on the implementation of the floating-point numbers,
b) you want to preserve that state as accurately as possible.
– It is extremely rare for databases to perform intensive number crunching. The efficiency of arbitrary precision numbers implementation should not be a problem.
REAL

A synonym for FLOAT(24)

Arbitrary precision numbers

MONEY, SMALLMONEY

In reality it’s INT64 i INT.

Usage:
– Never use it!
– MONEY types have some problems with precision. Operations such as “calculating the standard deviation of flour prices” or “how many rolls costing 7 cents each can I buy for a dollar” might produce distorted results.
DECIMAL, NUMERIC

DECIMAL and NUMERIC types are almost the same in SQL Server. Their implementations in principle are identical to each other. They differ only in the specification of the ANSI SQL standard.
The precision of NUMERIC(p,s) is equal to p, and scale is equal to s. The precision of DECIMAL(p,s) is equal to p, and scale is not less than s. The data type specified as DECIMAL is considered as DECIMAL(18,0). The maximal precision is decimal 38, which allows saving numbers from -10^38 to 10^38-1 using 17 bytes.

Usage:
– In situations where it’s necessary for calculations and storage to be accurate.
– When you have to rely on numbers of the order of 10^38 (maximum).
– In most cases when you want to save fractions.

Strings

CHAR, NCHAR

CHAR and NCHAR are fixed width data types. They contain a definite number of characters. In contrary to popular Internet opinion this type does not always use one byte for one character, as it depends on the sort used. The rule of thumb is to use CHAR for ASCII characters and NCHAR for others. You should never store UUID as a CHAR type.

Usage:
– With fixed length data (e.g. Social Security Number).
VARCHAR, NVARCHAR

VARCHAR and NVARCHAR, in contrast to CHAR and NCHAR, support varying string lengths. That’s about it.

Usage:
– Default data type for data storage.
UNIQUEIDENTIFIER

UNIQUEIDENTIFIER is a peculiar data type. It’s internally stored as a 128-bit integer (16 bytes), but for end-user and database connection driver it looks like a string. Larger memory usage and poor INSERT performance due to page breaks are this type’s flaws. It’s especially noticeable with NEWID() function or while generating cryptographically secure UUID in the application.

Usage:
– With master-master replication.
– When you don’t want the user guessing the next keys.
– When an application needs to generate a key regardless of the database connection.

Afterword

Proper use of the types supported by SQL Server means you can:
1. Take full advantage of data consistency checking offered by the database
2. Reduce memory footprint
3. Speed up query execution

As you can see, the performance improvement gained for the price of next to no time spent is what makes this optimization method great. Use it. Learn it. Love it.