Data Types
A practical tour of SQL Server's core data types — INT, DECIMAL, VARCHAR/NVARCHAR, DATE/DATETIME2, and BIT — and when to use each.
Introduction
Every column in every table needs a data type, and choosing the right one affects storage size, accuracy, and performance for the life of your application. SQL Server offers dozens of data types, but in practice, a small handful cover the vast majority of real tables. This lesson focuses on those.
Numeric Types
For whole numbers, INT is the default workhorse, comfortably covering values from roughly -2.1 billion to 2.1 billion. For money and other values needing exact decimal precision, use DECIMAL (or its synonym NUMERIC) rather than floating-point types — this matters enormously for financial data, where floating-point rounding errors are unacceptable.
| Type | Storage | Use Case |
|---|---|---|
| TINYINT | 1 byte | Small whole numbers, 0 to 255 (e.g., age, small counters) |
| SMALLINT | 2 bytes | Whole numbers, -32,768 to 32,767 |
| INT | 4 bytes | Default choice for whole numbers, IDs, counts |
| BIGINT | 8 bytes | Very large whole numbers, e.g. high-volume surrogate keys |
| DECIMAL(p, s) | Varies | Exact decimal values — money, quantities, measurements |
| FLOAT | 4 or 8 bytes | Approximate values for scientific/statistical calculations only |
DECLARE @price DECIMAL(10, 2) = 199.999;SELECT @price AS RoundedPrice;Click Run to see what this code prints.
FLOAT stores approximate binary values, which can produce tiny rounding errors that compound over many calculations. Always use DECIMAL or NUMERIC for currency, prices, and anything requiring exact arithmetic.
String Types
SQL Server offers both non-Unicode (CHAR/VARCHAR) and Unicode (NCHAR/NVARCHAR) string types. The 'N' prefix means 'National' — Unicode — storage, which can represent any language's characters, at roughly double the storage cost per character compared to non-Unicode types.
| Type | Description |
|---|---|
| CHAR(n) | Fixed-length, non-Unicode. Always stores exactly n characters, padding with spaces. |
| VARCHAR(n) | Variable-length, non-Unicode. Stores up to n characters, using only the space needed. |
| NVARCHAR(n) | Variable-length, Unicode. Stores up to n characters in any language/script. |
| VARCHAR(MAX) / NVARCHAR(MAX) | Variable-length, up to ~2 GB. For very large text. |
DECLARE @city NVARCHAR(50) = N'Bengaluru';SELECT @city AS City, LEN(@city) AS Length;Click Run to see what this code prints.
Unless you're certain a column will only ever contain plain ASCII text (like a fixed internal code), prefer NVARCHAR over VARCHAR. It costs a little more storage but avoids painful migrations later if you ever need to store names, addresses, or text in other languages.
Date and Time Types
SQL Server offers several date/time types, but for modern development, DATE (date only) and DATETIME2 (date and time, with configurable precision) are the recommended choices over the older DATETIME type.
| Type | Stores | Notes |
|---|---|---|
| DATE | Date only (YYYY-MM-DD) | 3 bytes storage |
| TIME | Time only | Configurable fractional-second precision |
| DATETIME2 | Date and time | Recommended over legacy DATETIME — wider range, more precision |
| DATETIME | Date and time | Legacy type; lower precision, rounds to nearest .000/.003/.007 second |
SELECT GETDATE() AS CurrentDateTime, CAST(GETDATE() AS DATE) AS CurrentDateOnly;Click Run to see what this code prints.
Boolean: BIT
SQL Server has no true boolean type; instead, BIT stores 0, 1, or NULL, and is conventionally used to represent false, true, and unknown.
DECLARE @isActive BIT = 1;SELECT @isActive AS IsActive;Click Run to see what this code prints.
Choosing the Right Type
- Whole numbers, IDs, counts → INT (or BIGINT only if you truly expect to exceed ~2 billion rows).
- Money, prices, exact quantities → DECIMAL(p, s), never FLOAT.
- Names, addresses, general text → NVARCHAR(n).
- Dates without time → DATE. Timestamps → DATETIME2.
- True/false flags → BIT.
Common Mistakes
- Using FLOAT or REAL for currency values, introducing subtle rounding errors over time.
- Choosing VARCHAR for names or free text that may one day include non-English characters, forcing a painful later migration to NVARCHAR.
- Using the legacy DATETIME type in new tables instead of the more precise, more range-friendly DATETIME2.
- Oversizing every column to VARCHAR(MAX) or NVARCHAR(MAX) 'just in case', which can hurt performance and hides the real shape of your data.
Best Practices
- Default to INT for keys and counts, NVARCHAR for text, DECIMAL for money, and DATETIME2 for timestamps.
- Size VARCHAR/NVARCHAR columns realistically (e.g., NVARCHAR(100) for a name) rather than always reaching for MAX.
- Always specify precision and scale explicitly for DECIMAL, e.g. DECIMAL(10, 2) for currency with two decimal places.
- Store true/false flags as BIT rather than as VARCHAR('Y'/'N') or INT(0/1) shortcuts.
Frequently Asked Questions
CHAR(n) always stores exactly n characters, padding shorter values with spaces, while VARCHAR(n) stores only as many characters as you actually give it, up to n. CHAR is occasionally used for fixed-width codes; VARCHAR is the default for most text.
For most application data — names, descriptions, free text — yes, since it safely supports any language. For short, guaranteed-ASCII values like internal status codes, VARCHAR can save some storage, but the difference is rarely significant at typical table sizes.
DATETIME2 has a larger date range, finer fractional-second precision, and is Microsoft's recommended type for new development. DATETIME is kept mainly for backward compatibility with older systems.
Yes. DECIMAL(p, s) stores signed values by default — no separate unsigned/signed variant exists in SQL Server, unlike some other database systems.
Key Takeaways
- INT is the default for whole numbers; DECIMAL(p, s) is the correct choice for money and exact values.
- NVARCHAR is the safe default for text; VARCHAR is fine only for guaranteed-ASCII data.
- DATE and DATETIME2 are the modern date/time types; avoid the legacy DATETIME in new designs.
- BIT is SQL Server's boolean-equivalent, storing 0, 1, or NULL.
Summary
You now know which data types to reach for when designing a table. In the next lesson, you'll put that knowledge to work by writing your first CREATE TABLE statements.