LuisLlamas Logo
tipos-de-datos-sql-server

Data Types in SQL Server

  • 5 min
  • Intermediate

In databases, a Data Type is a structural attribute that defines the nature of the information that a column, variable, or parameter can contain (such as integers, text, dates, or monetary values).

Choosing the wrong data types will have direct consequences on:

  1. Integrity: A data type is the first layer of validation (you cannot store “cucumber” in an INT field).
  2. Disk Space: We waste storage.
  3. RAM Memory: SQL Server loads data pages into memory.

In this article, we are going to see the most important types we will use in 99% of cases 👇.

Which type should I choose?

To make your life easier, here is a summary of default types:

Data to StoreRecommended TypeWhy
ID / Primary KeyINTFast and efficient.
Name, Address, EmailNVARCHAR(n)Supports accents and special characters.
Internal Codes (SKU)VARCHAR(n)They are usually ASCII and we save space.
Prices / MoneyDECIMAL(19, 4)Never FLOAT. We want exact precision.
Dates (without time)DATETakes up little space.
Registration Date (Log)DATETIME2Maximum precision.
Checkbox / FlagsBITIt’s SQL’s boolean.

Numeric Data Types

SQL Server offers a wide variety of ways to store numbers. The key is knowing the range we need and if we require decimals.

Integer Numbers

We use these types when we know there will be no decimals (counters, IDs, quantities).

Data TypeRangeStorage
TINYINT0 to 2551 Byte
SMALLINT-32,768 to 32,7672 Bytes
INT-2,147,483,648 to 2,147,483,6474 Bytes
BIGINT-9 trillion to 9 trillion (approx)8 Bytes

By default we use INT. We only use:

  • BIGINT if we expect to exceed 2 billion records (very common in large log or historical tables),
  • TINYINT for very small things like “Statuses” (1=Active, 2=Inactive).

Approximate Float or Real

They store the number in scientific notation. They are very efficient for huge or tiny numbers, but have rounding errors.

DECLARE @DistanciaTierraSol FLOAT;
SET @DistanciaTierraSol = 1.496E8; -- 149,600,000 km (Scientific notation)

DECLARE @MasaElectron REAL;
SET @MasaElectron = 9.109E-31; -- A tiny number
Copied!
  • Ideal for: Scientific calculations, physics, distances.
  • Never use for money.

Exact Decimal or Numeric

They store the exact number. They are defined as DECIMAL(p, s), where p is the total precision and s is the number of decimal places.

DECLARE @Precio DECIMAL(10, 2); -- 8 integer digits, 2 decimal places
SET @Precio = 19.99;
Copied!
  • Ideal for: Money, accounting.

Text Data Types

We have to decide between two factors: Fixed vs variable length and Unicode vs ASCII.

Fixed vs Variable (CHAR vs VARCHAR)

  • CHAR(n): Always reserves n bytes. If you store “Hello” in a CHAR(10), SQL will pad it with 6 spaces at the end. It’s only useful if all data has the same length (e.g., country codes ‘ES’, ‘FR’).
  • VARCHAR(n): It is variable. If you store “Hello” in a VARCHAR(10), it only uses the bytes for “Hello” plus 2 control bytes. It’s the standard for almost everything.

Unicode vs Non-Unicode (The letter ‘N’)

This is where the difference between VARCHAR and NVARCHAR comes in. The N stands for National (Unicode).

  • VARCHAR: Uses 1 byte per character. Only supports standard characters (English, basic Spanish). If you try to store Chinese characters or emojis, you will see ?.
  • NVARCHAR: Uses 2 bytes per character. Supports any character in the world (Japanese, Arabic, emojis 🚀).

Large Volumes (MAX)

In the past, there were types called TEXT and NTEXT. They are obsolete. Do not use them. If you need to store more than 4000/8000 characters (like a full blog article), use:

  • VARCHAR(MAX)
  • NVARCHAR(MAX)

They support up to 2GB of data per row.

Nowadays, disk space is cheap. When in doubt, use NVARCHAR to avoid problems with foreign names or special symbols in the future.

Date and Time Types

Until SQL Server 2008, we only had DATETIME. Now we have more precise options:

  • DATE: Date only (YYYY-MM-DD). Saves a lot of space (3 bytes).
  • TIME: Time only (hh:mm.nnnnnnn).
  • DATETIME2: The evolution of DATETIME. Has a greater range (from year 0001) and greater precision (100 nanoseconds).
  • DATETIME: The classic. Range from 1753. Precision of 3.33ms. Maintained for compatibility.
DECLARE @FechaNacimiento DATE = '1990-05-20';
DECLARE @MomentoExacto DATETIME2 = '2023-10-15 14:30:00.1234567';
Copied!

Other Common Types

BIT (Booleans)

SQL Server does not have a “BOOLEAN” type as such. It has BIT.

  • Values: 0, 1, or NULL.
  • SQL Server is very smart: if you have several BIT columns in a table, it groups them internally so they occupy only 1 byte for every 8 columns.

UNIQUEIDENTIFIER (GUID)

Stores globally unique identifiers (UUID/GUID). They are long strings like 6F9619FF-8B86-D011-B42D-00C04FC964FF.

  • Advantage: Unique worldwide
  • Disadvantage: They take up a lot (16 bytes) and are slow for indexes (fragmentation).

On this page