Lesson 2 of 54 15 minBeginner

Tables, rows, columns and data types

Every column has a data type that decides what it can hold, how much space it takes and what goes wrong at the edges. Learn the types you will use every day and how to choose between them.

A table is a set of rows, and every row has the same columns. Each column has a name, a data type, and a rule about whether it may be left empty (NULL) or not. The type is a promise: SQL Server will never let a date column hold the word “soon”, or a tinyint hold 300.

Here is how the Products table you created is defined. You can ask SQL Server for this at any time:

Query.sql

1SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS MaxLength,2       NUMERIC_PRECISION AS [Precision], NUMERIC_SCALE AS Scale, IS_NULLABLE3FROM INFORMATION_SCHEMA.COLUMNS4WHERE TABLE_NAME = 'Products'5ORDER BY ORDINAL_POSITION;

Output

COLUMN_NAME  DATA_TYPE  MaxLength  Precision  Scale  IS_NULLABLE
-----------  ---------  ---------  ---------  -----  -----------
ProductId    int             NULL         10      0  NO
Name         nvarchar          60       NULL   NULL  NO
CategoryId   int             NULL         10      0  YES
Price        decimal         NULL         10      2  NO
Stock        int             NULL         10      0  NO
Unit         nvarchar          10       NULL   NULL  NO
AddedOn      date            NULL       NULL   NULL  NO

The types you will use most

Type	Holds	Size	Use it for
`int`	Whole numbers, about ±2.1 billion	4 bytes	Ids, counts, quantities: the default whole number
`bigint` / `smallint` / `tinyint`	Bigger or smaller whole numbers	8 / 2 / 1 bytes	Huge counters; small codes (tinyint is 0–255)
`decimal(p, s)`	Exact decimals: p digits in all, s after the point	5–17 bytes	Money, prices, measurements
`float`	Approximate binary numbers	8 bytes	Scientific and geometric values, never money
`bit`	0, 1 or NULL	1 byte per 8 columns	Yes/no flags
`nvarchar(n)`	Unicode text up to n characters	2 bytes per character	Names, descriptions: anything people type
`varchar(n)`	Text in one code page, up to n characters	1 byte per character	Codes and data you control, such as `Shipped`
`char(n)` / `nchar(n)`	Text of exactly n characters, padded with spaces	n or 2n bytes	Fixed codes such as country codes `GB`
`date`	A calendar date	3 bytes	Birthdays, order dates
`datetime2(p)`	Date and time, to 100 ns	6–8 bytes	Timestamps (prefer it to the older `datetime`)
`datetimeoffset`	Date, time and time-zone offset	10 bytes	Times that must be compared across time zones
`uniqueidentifier`	A 16-byte GUID	16 bytes	Ids generated outside the database

Pick a type, then a value, and see exactly what SQL Server stores, or the error it raises. Every result was produced by SQL Server 2022.

Text: varchar or nvarchar?

varchar stores one byte per character from a single code page (on most servers, Western European). Characters outside it are replaced, without any error. nvarchar stores Unicode, so it can hold any language. A text literal needs an N in front (N'…') to be Unicode too:

Query.sql

1SELECT 'Łódź' AS WithoutN, N'Łódź' AS WithN,2       LEN(N'Łódź') AS Characters, DATALENGTH(N'Łódź') AS Bytes;

Output

WithoutN  WithN  Characters  Bytes
--------  -----  ----------  -----
Lódz      Łódź            4      8

Without the N, the literal is varchar, and Ł and ź are swapped for the nearest Latin letters as soon as the literal is read, before it is used anywhere. LEN counts characters; DATALENGTH counts bytes, two per character for nvarchar.

Numbers: exact or approximate?

decimal stores numbers exactly, digit by digit. float stores the nearest binary fraction, which is fast and has a huge range but cannot hold 0.1 exactly, just as 1/3 cannot be written exactly in decimal.

Query.sql

1SELECT IIF(CAST(0.1 AS float) + CAST(0.2 AS float) = 0.3, 'equal', 'not equal') AS FloatTest,2       IIF(CAST(0.1 AS decimal(10,2)) + CAST(0.2 AS decimal(10,2)) = 0.3, 'equal', 'not equal') AS DecimalTest;

Output

FloatTest  DecimalTest
---------  -----------
not equal  equal

IIF(test, a, b) returns a when the test is true and b otherwise; you will meet it properly in the Functions module. With float, 0.1 + 0.2 comes out a hair above 0.3, so the test fails. That is why prices in BuildMart are decimal(10,2): up to 10 digits, 2 of them after the point.

Whole-number types have their own surprise. Converting a decimal to int cuts off the fraction instead of rounding, and dividing one int by another gives an int:

Query.sql

1SELECT CAST(12.7 AS int) AS CutOff,2       7 / 2 AS IntDivision,3       7 / 2.0 AS DecimalDivision;

Output

CutOff  IntDivision  DecimalDivision
------  -----------  ---------------
    12            3         3.500000

Dates

Store dates in date or datetime2 columns, never as text: only real date columns sort correctly, reject 30 February, and work with date functions. Write date literals as 'yyyy-mm-dd' or 'yyyymmdd'. Other formats such as '03/04/2024' mean different days depending on the server’s language settings.

Query.sql

1SELECT CAST('2024-02-29' AS date) AS LeapDay,2       CAST('20241231' AS date) AS NewYearsEve,3       CAST('2024-06-12 14:30:00' AS datetime2(0)) AS Delivery;

Output

LeapDay     NewYearsEve  Delivery
----------  -----------  -------------------
2024-02-29  2024-12-31   2024-06-12 14:30:00

Quick check

Which type should a product price column use?

Quick check

What does SELECT 9 / 4; return?

Quick check

A customer’s surname is inserted as N'Wołowski' into a varchar(40) column. What is stored?

Practice

Using INFORMATION_SCHEMA.COLUMNS, list each column of the Customers table with its name, data type, maximum length and whether it allows NULL, in table order.

Show a hint

Start from the Products query above. Change the table name in the WHERE clause and drop the precision and scale columns.

Show the solution

Solution.sql

1SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS MaxLength, IS_NULLABLE2FROM INFORMATION_SCHEMA.COLUMNS3WHERE TABLE_NAME = 'Customers'4ORDER BY ORDINAL_POSITION;

Output

COLUMN_NAME  DATA_TYPE  MaxLength  IS_NULLABLE
-----------  ---------  ---------  -----------
CustomerId   int             NULL  NO
FirstName    nvarchar          40  NO
LastName     nvarchar          40  NO
City         nvarchar          40  YES
Email        nvarchar         100  YES
Phone        varchar           20  YES
JoinedOn     date            NULL  NO

Remember

Every column has a name, a type and a NULL rule; the type decides what values are allowed.
Use int for whole numbers, decimal(p, s) for money and measurements, float only for approximate science or geometry.
Use nvarchar (and N'…' literals) for text people type; varchar silently changes characters outside its code page.
Converting to int cuts off the fraction, and int / int is an int: 7 / 2 = 3.
Store dates in date or datetime2, and write literals as 'yyyy-mm-dd'.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Tables, rows, columns and data types

The types you will use most

Will it fit? Values meet column types

Text: varchar or nvarchar?

Numbers: exact or approximate?

Dates

Remember