Data Types

Data Types

What it means

Every SQL Server column has a data type that constrains what it can store and how much space it uses. Broad categories: exact numbers (INT, DECIMAL), approximate numbers (FLOAT), text (VARCHAR, NVARCHAR), dates/times (DATE, DATETIME2), and others (BIT for booleans, UNIQUEIDENTIFIER for GUIDs).

Examples

CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,       -- Unicode text, up to 100 characters
    Price DECIMAL(10,2) NOT NULL,      -- exact decimal, 10 total digits, 2 after the point
    InStock BIT NOT NULL DEFAULT 1,    -- boolean: 0 or 1
    CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME(),   -- modern, more precise date/time type
    Sku UNIQUEIDENTIFIER DEFAULT NEWID()             -- globally unique identifier
);

SELECT CAST('123' AS INT);           -- explicit conversion
SELECT CONVERT(DECIMAL(10,2), '19.99');

Common mistake

Using FLOAT for money — floating-point types store approximate values, which can introduce rounding errors that compound over many calculations, exactly the kind of surprise you don’t want with financial data.

DECLARE @price FLOAT = 19.99;
DECLARE @quantity FLOAT = 3;
SELECT @price * @quantity;   -- may not be EXACTLY 59.97 due to floating-point representation

-- Use DECIMAL for exact, predictable arithmetic with money
DECLARE @price DECIMAL(10,2) = 19.99;
DECLARE @quantity INT = 3;
SELECT @price * @quantity;   -- exactly 59.97

Quick practice

1. Why is DECIMAL preferred over FLOAT for storing monetary values?

   Answer

   DECIMAL stores exact values; FLOAT is an approximate, floating-point representation that can introduce small rounding errors, which is risky for financial calculations.

2. What’s the difference between VARCHAR and NVARCHAR?

   Answer

   NVARCHAR stores Unicode text (supporting virtually any language/character set) at roughly double the storage cost per character; VARCHAR stores single-byte characters only.

3. What does DECIMAL(10,2) specify?

   Answer

   A decimal number with up to 10 total digits, 2 of which are after the decimal point (so up to 8 digits before the point).