Data types: choosing the right column type
Every column has a data type that decides what it can hold, how much space it takes and how fast it is to search. This lesson shows the SQL Server types you will use every day, how each one maps to C#, and the common mistakes that waste space or lose data.
- Whole numbersint for ids and counts, bigint past 2 billion rows
- Money and exact valuesdecimal(18,2); never float or real
- Textnvarchar(n) for any language, varchar(n) for ASCII codes
- Dates and timesdate, datetime2, or datetimeoffset with a time zone
- Yes or nobit, maps to bool in C#
- Unique ids across systemsuniqueidentifier, maps to Guid
Choose the smallest type that safely holds every real value.
Why the type matters
A data type is a promise about a column: an int column can only hold whole numbers, a date column can only hold dates. SQL Server rejects values that break the promise, so the right type is your first line of data quality.
Types also decide size. A million rows of int take about 4 MB, the same rows as bigint about 8 MB, and as nvarchar(50) storing numbers as text much more. Smaller rows mean more rows per page, fewer reads and faster queries, which matters most on big tables.
The types you will use most
- int (4 bytes, up to about 2.1 billion) for ids and counts; bigint (8 bytes) when a table may pass 2 billion rows.
- decimal(p, s) for money and exact values, for example decimal(18,2). Never use float or real for money: they are approximate.
- nvarchar(n) for text in any language (2 bytes per character); varchar(n) only for plain ASCII codes. Use nvarchar(max) only for long text such as articles.
- date for a day, datetime2 for date and time, datetimeoffset when the time zone matters. Prefer datetime2 over the older datetime.
- bit for true/false, uniqueidentifier for GUIDs, varbinary(max) for files (or better, store files outside the database).
Example: a well-typed table
Each column uses the smallest type that safely fits the data, and lengths are realistic rather than max everywhere.
CREATE TABLE Products (
ProductId int IDENTITY(1,1) PRIMARY KEY,
Sku varchar(20) NOT NULL, -- ASCII code like "PEN-001"
Name nvarchar(200) NOT NULL, -- any language
Price decimal(18,2) NOT NULL, -- exact money
InStock bit NOT NULL DEFAULT 1,
CreatedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
ReleaseDate date NULL
);How the types map to C#
EF Core and ADO.NET map SQL types to .NET types automatically. Matching them avoids conversion errors and lost precision.
- int → int, bigint → long, bit → bool, decimal → decimal, uniqueidentifier → Guid.
- nvarchar/varchar → string, date → DateOnly (or DateTime), datetime2 → DateTime, datetimeoffset → DateTimeOffset.
- A column that allows NULL maps to a nullable type: int NULL → int?, date NULL → DateOnly?.
Common mistakes
- Storing dates or numbers as text: sorting and filtering break ('10' sorts before '9') and indexes help less.
- Using float for money: 0.1 + 0.2 is not exactly 0.3.
- Using nvarchar(max) for short values: it cannot be a normal index key and hides real size limits.
- Mixing varchar parameters with nvarchar columns (or the reverse) can force a conversion that stops an index from being used.
Interview question
Why should you not store prices in a float column?
float and real are approximate binary types, so many decimal values cannot be stored exactly and small rounding errors appear in totals. decimal(p, s) stores exact base-10 values with a fixed number of decimal places, which is what money needs, and it maps to the C# decimal type.
Want the full details? Read the official Microsoft documentation for this topic.