Data Types

Choosing the right column type matters: it affects storage, accuracy, indexing, and how implicit conversions cascade through your query plans. T-SQL has a richer type system than ANSI SQL — money, GUID, sql_variant, table-valued types, and more.

Numeric Types

Type	Bytes	Range / Notes
`TINYINT`	1	0 – 255 (unsigned)
`SMALLINT`	2	-32,768 – 32,767
`INT`	4	±2.1 billion
`BIGINT`	8	±9.2 quintillion
`DECIMAL(p,s)` / `NUMERIC(p,s)`	5–17	Exact, p ≤ 38 — use for money/finance
`MONEY`	8	Fixed 4 decimal places, ±922 trillion
`SMALLMONEY`	4	Fixed 4 decimal places, ±214,748
`FLOAT(n)`	4 or 8	Approximate; n=53 is double-precision
`REAL`	4	Approximate, single-precision

CREATE TABLE products (
    product_id   INT             IDENTITY(1,1) PRIMARY KEY,
    list_price   DECIMAL(10, 2)  NOT NULL,    -- exact, recommended for money
    weight_kg    REAL            NULL,
    sales_ytd    MONEY           NOT NULL DEFAULT 0
);

⚠

Avoid FLOAT/REAL for currency — they cannot represent 0.10 exactly. Use DECIMAL(p,s) for any value where rounding errors would be unacceptable. MONEY is safe but only has 4 decimal places and quirky multiplication semantics.

Character Types

Type	Encoding	Max length
`CHAR(n)`	1 byte/char (collation-dependent)	8000 — fixed length, padded
`VARCHAR(n)`	1 byte/char	8000 — variable length
`VARCHAR(MAX)`	1 byte/char	~2 GB
`NCHAR(n)`	2 bytes/char (UTF-16)	4000 — Unicode, fixed
`NVARCHAR(n)`	2 bytes/char	4000 — Unicode, variable
`NVARCHAR(MAX)`	2 bytes/char	~2 GB

Prefix Unicode literals with N: N'café'. SQL Server 2019+ supports UTF-8 collations on VARCHAR columns, which can cut storage versus NVARCHAR for Latin-script text.

Date and Time Types

Type	Bytes	Range / Resolution
`DATE`	3	0001-01-01 – 9999-12-31, no time
`TIME(p)`	3–5	00:00:00 – 23:59:59.9999999
`SMALLDATETIME`	4	1900–2079, minute resolution
`DATETIME`	8	1753–9999, ~3 ms resolution
`DATETIME2(p)`	6–8	0001–9999, 100 ns resolution — preferred
`DATETIMEOFFSET(p)`	8–10	DATETIME2 + timezone offset

DECLARE
    @hire_date    DATE           = '2024-03-15',
    @check_in     DATETIME2(0)   = '2024-03-15 09:00:00',
    @event_utc    DATETIMEOFFSET = '2024-03-15 09:00:00 +05:30';

SELECT GETDATE(), SYSDATETIME(), SYSDATETIMEOFFSET();

Always prefer DATETIME2 over the legacy DATETIME for new tables: smaller, more accurate, and ANSI-compliant.

Other Important Types

Type	Purpose
`BIT`	Boolean — 0, 1, or NULL. Up to 8 bits packed into one byte.
`UNIQUEIDENTIFIER`	16-byte GUID. Generate with `NEWID()` or `NEWSEQUENTIALID()`.
`SQL_VARIANT`	Stores values of multiple base types. Niche use only.
`XML`	Structured XML with .nodes()/.value() methods.
`VARBINARY(MAX)`	Binary blob; pair with `FILESTREAM` for large files.
`GEOGRAPHY` / `GEOMETRY`	Spatial types.

DECLARE @is_active BIT = 1;
DECLARE @row_id    UNIQUEIDENTIFIER = NEWID();
DECLARE @misc      SQL_VARIANT = 42;

SELECT @row_id, SQL_VARIANT_PROPERTY(@misc, 'BaseType') AS base_type;

Table-Valued Types

User-defined table types let you pass entire rowsets to procedures — a clean alternative to comma-delimited strings or temp tables:

CREATE TYPE dbo.IntList AS TABLE (id INT PRIMARY KEY);
GO

CREATE PROCEDURE dbo.GetEmployees @ids dbo.IntList READONLY
AS
    SELECT e.* FROM employees e JOIN @ids i ON i.id = e.employee_id;
GO

Conversion: CAST, CONVERT, TRY_CAST, PARSE

-- Standard ANSI cast
SELECT CAST('2024-03-15' AS DATE);

-- CONVERT supports a style code (great for dates)
SELECT CONVERT(VARCHAR(10), GETDATE(), 23);  -- 2024-03-15 (ISO)
SELECT CONVERT(VARCHAR(10), GETDATE(), 103); -- 15/03/2024 (British)

-- TRY_CAST returns NULL on failure instead of erroring
SELECT TRY_CAST('not-a-date' AS DATE);       -- NULL
SELECT TRY_CONVERT(INT, 'abc');               -- NULL

-- PARSE uses .NET formatters (slower; culture-aware)
SELECT PARSE('15 March 2024' AS DATE USING 'en-GB');

Best Practices

Use the smallest numeric type that fits your range — narrower rows mean more rows per page.
Prefer NVARCHAR over NCHAR unless every value is genuinely fixed length.
Default to DATETIME2(3) or DATETIME2(7) for timestamps.
Use DECIMAL for money; reserve MONEY for legacy compatibility.
Use TRY_CAST/TRY_CONVERT whenever input may be malformed — avoid uncaught conversion errors.

Summary

Pick exact types (DECIMAL, INT) for accuracy; approximate types (FLOAT) only for scientific data.
NVARCHAR/NCHAR are Unicode (2 bytes/char); VARCHAR/CHAR are 1 byte unless using UTF-8 collation.
DATETIME2 is the modern replacement for DATETIME.
TRY_CAST and TRY_CONVERT are NULL-on-failure variants — use them for parsing user input.
Table-valued types (READONLY) are the cleanest way to pass rowsets to stored procedures.