Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 39 Β· Getting Started

SQL Server Data Types

Choose the right SQL Server data type: integers, DECIMAL vs FLOAT, VARCHAR vs NVARCHAR, DATETIME2, BIT, UNIQUEIDENTIFIER and implicit conversion rules.

Every column, variable and parameter has a data type. The type decides what values are allowed, how many bytes each value uses, how it sorts, and how fast queries run. Choosing types carefully is one of the cheapest ways to get a better database: smaller types mean more rows per 8 KB page, smaller indexes and faster scans.

This lesson tours the types you will use daily, explains the classic traps (FLOAT for money, VARCHAR for Unicode text, DATETIME rounding) and shows the implicit conversion rules that can silently slow a query.

Whole numbers

Type Storage Range
TINYINT 1 byte 0 to 255
SMALLINT 2 bytes -32,768 to 32,767
INT 4 bytes about -2.1 billion to 2.1 billion
BIGINT 8 bytes about -9.2 quintillion to 9.2 quintillion

Pick the smallest type that will always fit. Use INT for most keys and BIGINT for tables that may pass two billion rows (events, logs). BIT stores 0, 1 or NULL and is the type for yes/no flags; SQL Server has no true BOOLEAN, so WHERE is_active = 1 is the norm.

Exact and approximate decimals

  • DECIMAL(p, s) (also NUMERIC) is exact. p is the total digits (1 to 38) and s the digits after the point. DECIMAL(10,2) holds up to 99,999,999.99.
  • MONEY and SMALLMONEY are fixed at four decimals. They work, but DECIMAL is more predictable in division and is generally preferred.
  • FLOAT and REAL are approximate binary floating point numbers. They are fine for measurements and science, but never use them for money, because 0.1 cannot be stored exactly.

The editor shows the effect with SQLite's floating point arithmetic; the same rounding error occurs with FLOAT in SQL Server.

SQL Why FLOAT is wrong for money

Press Run (or Ctrl + Enter) to see the result here.

Text types

Type Meaning
CHAR(n) Fixed length, padded with spaces; good for codes such as country CHAR(2)
VARCHAR(n) Variable length, 1 byte per character in the collation's code page
VARCHAR(MAX) Up to 2 GB
NCHAR(n), NVARCHAR(n) Unicode (UTF-16), 2 bytes per character
NVARCHAR(MAX) Unicode up to 2 GB

Use NVARCHAR when you must store text in several languages, and write string literals with an N prefix: N'ZaΕΌΓ³Ε‚Δ‡'. Without the prefix the literal is converted through the database code page and unsupported characters become ?. With a UTF-8 collation (SQL Server 2019+), VARCHAR can also hold Unicode, which can save space for mostly-ASCII data.

Avoid the old TEXT, NTEXT and IMAGE types; they are deprecated in favor of the MAX types. Always give VARCHAR a length: CAST('hello' AS VARCHAR) without a length quietly truncates to 30 characters.

Dates and times

Type Detail
DATE Date only, 3 bytes, 0001-01-01 to 9999-12-31
TIME(n) Time of day with n fractional-second digits
DATETIME2(n) Date and time, precision up to 100 ns; the modern default
DATETIMEOFFSET(n) DATETIME2 plus a time zone offset
SMALLDATETIME Minute precision, legacy
DATETIME Legacy, rounds to .000, .003 or .007 seconds

Prefer DATE when you do not need the time and DATETIME2 when you do. DATETIME rounds milliseconds in surprising ways, so '2025-03-01 23:59:59.999' becomes the next midnight. Date and time functions shows how to work with them.

Other useful types

  • UNIQUEIDENTIFIER: a 16-byte GUID. Generate with NEWID() (random) or NEWSEQUENTIALID() (ordered, as a column default, friendlier to indexes).
  • VARBINARY(n|MAX): raw bytes, for files and hashes.
  • XML: typed XML with query methods.
  • JSON is not a separate type in most versions: you store JSON text in NVARCHAR(MAX) and use JSON functions.
  • SQL_VARIANT: holds values of different types; rarely a good idea.
  • ROWVERSION: an automatically changing 8-byte value used for optimistic concurrency.
  • GEOGRAPHY, GEOMETRY, HIERARCHYID: spatial and tree types.

Implicit conversion

When you compare or combine two different types, SQL Server converts one to the other using data type precedence. The type with higher precedence wins: date and time types outrank numbers, numbers outrank strings, and NVARCHAR outranks VARCHAR. Consequences:

SQL
SELECT 1 + '2';        -- 3, the string is converted to INT
SELECT '1' + '2';      -- '12', both are strings, so + concatenates
SELECT 'a' + 1;        -- error: conversion failed (a is not a number)

Implicit conversion also affects performance. If a column is VARCHAR and you compare it with an NVARCHAR parameter (for example from an application that sends Unicode strings), SQL Server converts the column to NVARCHAR for every row and cannot use an index seek. The fix is to match the parameter type to the column type. Look for CONVERT_IMPLICIT warnings in execution plans.

The editor shows SQLite's looser rules for comparison; in T-SQL the right-hand text would be converted by precedence.

SQL Types in a real table Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Practical choices

Data Good type
Primary key INT IDENTITY or BIGINT IDENTITY
Price, balance DECIMAL(12,2)
Name, email NVARCHAR(100) / NVARCHAR(320)
Country code CHAR(2)
Created timestamp DATETIME2(3) or DATETIME2(0)
Flag BIT NOT NULL DEFAULT 0
Percentage DECIMAL(5,2)

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. Which type should you use for money amounts?

    DECIMAL is exact. FLOAT and REAL are approximate binary types, so sums of cents can drift.
  2. 2. Which prefix marks a Unicode string literal?

    N'text' is an NVARCHAR literal. Without N, characters outside the code page may be replaced by question marks.
  3. 3. What does SELECT '1' + '2' return in T-SQL?

    Both operands are strings, so + concatenates them. Numeric addition happens only if one operand is a number.
  4. 4. Why can comparing a VARCHAR column with an NVARCHAR parameter be slow?

    Data type precedence converts the lower-precedence column to NVARCHAR, which can make the predicate non-sargable.
  5. 5. Which date type is the modern default when you need date and time?

    DATETIME2 has a wider range, better precision and no odd rounding. TIMESTAMP in SQL Server is a row version, not a date.

Summary

  • Pick the smallest type that fits; INT for most keys, BIGINT when counts may exceed two billion.
  • Use DECIMAL for exact numbers and never FLOAT for money.
  • Use NVARCHAR with N'...' literals for multilingual text, and always give VARCHAR a length.
  • Prefer DATE and DATETIME2 over legacy DATETIME.
  • Match parameter types to column types to avoid implicit conversions.

Next: SELECT, TOP and Selecting Data