How to Declare Data Types in SQL Server

What are SQL Server data types?

Data types tell SQL Server how to store, compare, and retrieve values. Choosing the correct type preserves precision, saves space, and boosts query speed.

Which numeric data types should I use?

INT and BIGINT store whole numbers; DECIMAL(p,s) holds exact decimals—ideal for money; FLOAT and REAL allow imprecision; BIT stores 0/1 flags.

When do VARCHAR and NVARCHAR differ?

VARCHAR stores non-Unicode text using 1 byte/char. NVARCHAR stores Unicode using 2 bytes/char. Pick VARCHAR for ASCII-only data and NVARCHAR for multilingual data.

How do I store dates and times accurately?

Use DATETIME2 for high-precision timestamps, DATE for date-only, TIME for time-only. Avoid legacy DATETIME unless old code demands it.

How to declare data types in a CREATE TABLE?

CREATE TABLE Products (
id INT PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT DEFAULT 0
);

What are best practices for choosing data types?

Use the smallest type that fits future values, match precision to business rules, add CHECK constraints, and document every choice for maintainability.

Common mistakes and fixes

Oversizing character columns

NVARCHAR(MAX) for short strings wastes memory. Size columns realistically, e.g., NVARCHAR(100).

Storing money in FLOAT

FLOAT rounds values. Store currency in DECIMAL(19,4) for accuracy.

Can I change a column’s data type safely?

Run ALTER TABLE ... ALTER COLUMN inside a transaction after validating that existing data converts without truncation. Always back up first.

Further example

-- Exact totals with DECIMAL
CREATE TABLE Orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
total_amount DECIMAL(12,2) NOT NULL
);

FAQ

Is NVARCHAR slower than VARCHAR?

NVARCHAR uses more storage but shows similar query speed when indexed. Overhead is mainly in disk and cache usage.

Should I use UNIQUEIDENTIFIER as a primary key?

GUIDs ensure global uniqueness but fragment clustered indexes. Prefer INT/BIGINT keys or NEWSEQUENTIALID() to reduce fragmentation.