SQL Server Data Types: Choosing the Right Column Types

Why Data Type Choice Matters

In SQL Server, a column’s data type controls what values are allowed, how values are stored, how comparisons and calculations behave, and what conversions may fail at runtime. Choosing the “closest correct” type improves data quality (fewer invalid values), reduces storage and I/O, and avoids subtle bugs like rounding, truncation, or time zone confusion.

This chapter groups common SQL Server data types and focuses on correctness, storage, and behavior. You will also run mini-labs to see rounding and conversion errors in action.

1) Numeric Types

INT and BIGINT (whole numbers)

Use INT for whole numbers in the range -2,147,483,648 to 2,147,483,647. Use BIGINT when you may exceed that range (up to about 9.22e18). Prefer the smallest type that safely fits your domain to reduce storage and index size.

• Good fits: counts, identifiers (when not using GUIDs), quantities that are always whole.
• Pitfall: choosing INT for values that can grow beyond 2.1 billion (e.g., event logs over years).

-- Range check examples (conceptual; do not run as a single batch if you want to see each error separately)  SELECT CAST(2147483647 AS INT) AS MaxIntOK;  -- next line would overflow: SELECT CAST(2147483648 AS INT);

DECIMAL/NUMERIC (exact fixed-point)

DECIMAL(p,s) (synonym: NUMERIC) stores exact fixed-point numbers. It is ideal for money-like values where you must avoid binary floating-point rounding. Precision p is the total number of digits; scale s is digits to the right of the decimal point.

• Example: DECIMAL(10,2) allows up to 10 digits total, 2 after the decimal (e.g., 12345678.90).
• Behavior: inserting a value with more than s decimal places rounds (not truncates) to the defined scale.
• Pitfall: choosing too-small precision causes overflow errors during inserts or calculations.

-- Precision/scale examples  SELECT CAST(123.4567 AS DECIMAL(10,2)) AS RoundedTo2;  -- 123.46  SELECT CAST(99999999.99 AS DECIMAL(10,2)) AS Fits;       -- OK  -- next would overflow because it needs 11 digits total:  -- SELECT CAST(100000000.00 AS DECIMAL(10,2));

BIT (true/false)

BIT stores 0, 1, or NULL. Use it for boolean flags like IsActive, IsDeleted, HasOptedIn. Remember that NULL is “unknown,” which can complicate logic if you really mean “false.”

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

-- BIT behavior  SELECT CAST(1 AS BIT) AS TrueValue, CAST(0 AS BIT) AS FalseValue, CAST(NULL AS BIT) AS UnknownValue;

Mini-lab: Observe rounding and overflow with DECIMAL

Goal: create a table with a price column, insert values that round, and trigger an overflow error.

-- 1) Create a lab table  DROP TABLE IF EXISTS dbo.LabNumeric;  CREATE TABLE dbo.LabNumeric (      ItemId INT IDENTITY(1,1) PRIMARY KEY,      Price DECIMAL(6,2) NOT NULL,      IsTaxable BIT NOT NULL  );  -- 2) Insert values that will round to scale=2  INSERT INTO dbo.LabNumeric (Price, IsTaxable) VALUES (12.345, 1), (12.344, 0);  SELECT ItemId, Price, IsTaxable FROM dbo.LabNumeric;  -- 3) Try an overflow (DECIMAL(6,2) max is 9999.99)  -- Run this statement alone to see the error:  -- INSERT INTO dbo.LabNumeric (Price, IsTaxable) VALUES (10000.00, 1);

What to notice: values with more than 2 decimal places are rounded; values exceeding the precision cause an error rather than silently clipping.

2) Character Types

CHAR/VARCHAR (non-Unicode)

CHAR(n) is fixed-length and pads with spaces to length n. VARCHAR(n) is variable-length up to n. Use CHAR for truly fixed-size codes (e.g., 2-letter state codes) and VARCHAR for most text.

• Storage: CHAR(n) always uses n bytes (plus overhead), while VARCHAR(n) uses actual length (plus overhead).
• Pitfall: fixed-length padding can affect comparisons and display (though many comparisons ignore trailing spaces).
• Length choice: pick a realistic maximum; avoid defaulting to VARCHAR(255) without reason.

NCHAR/NVARCHAR (Unicode)

NCHAR/NVARCHAR store Unicode (UTF-16) and are required when you must reliably store multilingual text (e.g., names, addresses, product descriptions in multiple languages). They typically use 2 bytes per character (some characters use surrogate pairs).

• When to use Unicode: user-entered names, international content, any system with multiple languages.
• Tradeoff: more storage than non-Unicode, but correct representation of characters.
• Pitfall: storing Unicode text in VARCHAR can lead to lost characters or replacement characters depending on collation/code page.

-- Demonstrate that NVARCHAR can store multilingual characters  DROP TABLE IF EXISTS dbo.LabText;  CREATE TABLE dbo.LabText (      PersonId INT IDENTITY(1,1) PRIMARY KEY,      NameVarchar VARCHAR(20) NULL,      NameNvarchar NVARCHAR(20) NULL  );  INSERT INTO dbo.LabText (NameVarchar, NameNvarchar)  VALUES ('Jose', N'José'), (NULL, N'李雷');  SELECT PersonId, NameVarchar, NameNvarchar FROM dbo.LabText;

Note: the N prefix on Unicode string literals (e.g., N'李雷') is important; without it, SQL Server may treat the literal as non-Unicode and convert it before storage.

Mini-lab: Alter a column from VARCHAR to NVARCHAR

Goal: change a column to support Unicode and observe what happens when lengths are too small.

-- 1) Create a table with a too-small VARCHAR column  DROP TABLE IF EXISTS dbo.LabAlterText;  CREATE TABLE dbo.LabAlterText (      CustomerId INT IDENTITY(1,1) PRIMARY KEY,      DisplayName VARCHAR(5) NOT NULL  );  -- 2) Insert a value that fits  INSERT INTO dbo.LabAlterText (DisplayName) VALUES ('Maria');  SELECT * FROM dbo.LabAlterText;  -- 3) Attempt to insert a longer value (will fail)  -- Run alone to see the error:  -- INSERT INTO dbo.LabAlterText (DisplayName) VALUES ('Mariana');  -- 4) Alter the column to NVARCHAR(20) to support Unicode and longer names  ALTER TABLE dbo.LabAlterText ALTER COLUMN DisplayName NVARCHAR(20) NOT NULL;  -- 5) Insert Unicode  INSERT INTO dbo.LabAlterText (DisplayName) VALUES (N'李雷'), (N'Zoë');  SELECT * FROM dbo.LabAlterText;

What to notice: SQL Server prevents truncation by raising an error when the value exceeds the defined length. Altering the type/length is a schema change that should be planned (indexes, constraints, and application expectations may be affected).

3) Date/Time Types

DATE (date only)

DATE stores only the calendar date (year-month-day). Use it for birthdays, due dates, and any value where time-of-day is irrelevant.

• Pitfall: using DATETIME/DATETIME2 for date-only values can lead to accidental time portions and confusing comparisons.

TIME (time of day only)

TIME(p) stores time-of-day with fractional seconds precision p (0–7). Use it for store opening hours, shift start times, or recurring daily times.

DATETIME2 (date and time, recommended)

DATETIME2(p) is generally the preferred “date + time” type in modern SQL Server designs. It has a larger date range and configurable fractional seconds precision. It avoids some quirks of the older DATETIME type.

• Typical use: event timestamps, created/updated times.
• Precision choice: DATETIME2(0) for seconds, DATETIME2(3) for milliseconds, DATETIME2(7) for maximum precision.

DATETIMEOFFSET (date/time with time zone offset)

DATETIMEOFFSET(p) stores a date/time plus a UTC offset (e.g., 2026-01-16 10:00:00 -05:00). Use it when you must preserve the original offset at the time of capture (e.g., user actions across time zones).

• Pitfall: an offset is not the same as a time zone (it does not encode DST rules or a region name). If you need full time zone rules, store a separate time zone identifier (e.g., 'America/New_York') alongside a UTC timestamp.

-- Date/time examples  SELECT      CAST('2026-01-16' AS DATE) AS OnlyDate,      CAST('10:15:30.123' AS TIME(3)) AS OnlyTime,      CAST('2026-01-16T10:15:30.1234567' AS DATETIME2(7)) AS DateTime2Value,      CAST('2026-01-16T10:15:30.1234567-05:00' AS DATETIMEOFFSET(7)) AS DateTimeOffsetValue;

Mini-lab: Conversion errors and precision behavior

Goal: insert valid and invalid date/time strings and see conversion failures; compare precision choices.

DROP TABLE IF EXISTS dbo.LabDateTime;  CREATE TABLE dbo.LabDateTime (      RowId INT IDENTITY(1,1) PRIMARY KEY,      EventDate DATE NOT NULL,      EventTime TIME(0) NOT NULL,      CapturedAt DATETIME2(3) NOT NULL,      CapturedAtOffset DATETIMEOFFSET(0) NULL  );  -- Valid inserts  INSERT INTO dbo.LabDateTime (EventDate, EventTime, CapturedAt, CapturedAtOffset)  VALUES ('2026-01-16', '10:15:30', '2026-01-16T10:15:30.987', '2026-01-16T10:15:30-05:00');  SELECT * FROM dbo.LabDateTime;  -- Invalid date (will fail). Run alone to see the error:  -- INSERT INTO dbo.LabDateTime (EventDate, EventTime, CapturedAt)  -- VALUES ('2026-02-30', '10:00:00', SYSDATETIME());  -- Precision observation: DATETIME2(3) rounds to milliseconds  INSERT INTO dbo.LabDateTime (EventDate, EventTime, CapturedAt, CapturedAtOffset)  VALUES ('2026-01-16', '10:15:30', '2026-01-16T10:15:30.9876', NULL);  SELECT RowId, CapturedAt FROM dbo.LabDateTime ORDER BY RowId;

What to notice: invalid dates are rejected; fractional seconds may be rounded to the column’s defined precision.

4) Special Types

UNIQUEIDENTIFIER (GUID)

UNIQUEIDENTIFIER stores a GUID. Use it when you need globally unique keys across systems or offline generation. It is common for integration scenarios and distributed systems.

• Behavior: values can be generated with NEWID() (random) or NEWSEQUENTIALID() (more index-friendly, but only as a default constraint).
• Tradeoff: larger than integer keys and can fragment clustered indexes if random GUIDs are used as the clustering key.

DROP TABLE IF EXISTS dbo.LabGuid;  CREATE TABLE dbo.LabGuid (      Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,      Payload VARCHAR(20) NOT NULL  );  INSERT INTO dbo.LabGuid (Payload) VALUES ('A'), ('B');  SELECT * FROM dbo.LabGuid;

VARBINARY (bytes)

VARBINARY(n) stores variable-length binary data up to n bytes. Use it for hashes, encrypted blobs, small files, or binary tokens. For very large binary objects, you may see VARBINARY(MAX), but be deliberate because large values can affect memory grants, I/O, and query performance.

• Good fits: SHA-256 hashes (32 bytes), encrypted keys/tokens, compact binary identifiers.
• Pitfall: storing “binary-looking text” (like hex strings) in character columns wastes space and complicates validation.

DROP TABLE IF EXISTS dbo.LabBinary;  CREATE TABLE dbo.LabBinary (      RowId INT IDENTITY(1,1) PRIMARY KEY,      Hash VARBINARY(32) NOT NULL  );  -- Insert a 32-byte value from a hex literal  INSERT INTO dbo.LabBinary (Hash)  VALUES (0x0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF);  SELECT RowId, DATALENGTH(Hash) AS BytesStored FROM dbo.LabBinary;

NULL vs NOT NULL: Data Quality and Query Logic

NULL means “missing/unknown,” not “empty” or “zero.” Deciding between NULL and NOT NULL is a data quality decision that also affects query logic and aggregates.

• Use NOT NULL when a value must exist for the row to be valid (e.g., order date, quantity, primary identifiers). This prevents incomplete data at the database boundary.
• Use NULL when the value is genuinely optional or unknown at insert time (e.g., ShippedAt before shipment, MiddleName when not provided).
• Query behavior: comparisons with NULL yield unknown, so WHERE Column = NULL never matches; use IS NULL / IS NOT NULL.
• Aggregates: COUNT(Column) ignores NULL, while COUNT(*) counts rows. SUM/AVG ignore NULL values.

-- NULL comparison and counting  DROP TABLE IF EXISTS dbo.LabNulls;  CREATE TABLE dbo.LabNulls (      RowId INT IDENTITY(1,1) PRIMARY KEY,      Score INT NULL  );  INSERT INTO dbo.LabNulls (Score) VALUES (10), (NULL), (30);  SELECT      COUNT(*) AS RowsTotal,      COUNT(Score) AS RowsWithScore,      AVG(Score) AS AvgIgnoringNulls  FROM dbo.LabNulls;  -- Correct NULL filtering  SELECT * FROM dbo.LabNulls WHERE Score IS NULL;

Mini-lab: Alter NULLability and handle existing data

Goal: change a nullable column to NOT NULL and see why existing NULL values must be addressed first.

DROP TABLE IF EXISTS dbo.LabAlterNull;  CREATE TABLE dbo.LabAlterNull (      RowId INT IDENTITY(1,1) PRIMARY KEY,      IsActive BIT NULL  );  INSERT INTO dbo.LabAlterNull (IsActive) VALUES (1), (NULL), (0);  SELECT * FROM dbo.LabAlterNull;  -- Attempt to enforce NOT NULL (will fail because NULL exists). Run alone to see the error:  -- ALTER TABLE dbo.LabAlterNull ALTER COLUMN IsActive BIT NOT NULL;  -- Fix existing data, then enforce NOT NULL  UPDATE dbo.LabAlterNull SET IsActive = 0 WHERE IsActive IS NULL;  ALTER TABLE dbo.LabAlterNull ALTER COLUMN IsActive BIT NOT NULL;  SELECT * FROM dbo.LabAlterNull;

What to notice: schema rules apply to existing rows too. Converting a column to NOT NULL is a strong quality guarantee, but you must decide what to do with missing values (default them, backfill from other sources, or reject the change).