Sign in to save your place.

MODULE 5 OF 12

Creating Tables & Data Types

Define tables with CREATE TABLE: column names, data types, PRIMARY KEY, and NOT NULL.

CREATE TABLE

Use CREATE TABLE to define a new table: give it a name and list each column with a name and data type.

Column names

Column names must be unique within the table. Use descriptive names (e.g. CustomerId, OrderDate). Avoid spaces; use PascalCase or snake_case.

Data types

TypeUse for
INTWhole numbers (e.g. IDs, counts)
VARCHAR(n)Variable-length text, max n characters
DATEDate only (no time)
DECIMAL(p,s)Exact numbers (p = total digits, s = decimal places)

PRIMARY KEY & NOT NULL

PRIMARY KEY identifies each row uniquely. Only one primary key per table; it implies NOT NULL and unique values. NOT NULL means the column cannot store NULL—every row must have a value.

Key Takeaway

Always define a primary key for your main tables. Use NOT NULL on columns that must always have a value.

Table structure

A table is like a spreadsheet: columns (name + type) and rows. PRIMARY KEY marks the column(s) that uniquely identify each row.

CREATE TABLE example

-- Table with 3 columns: Id (PK), Name, BirthDate
CREATE TABLE Customers (
    Id        INT PRIMARY KEY,
    Name      VARCHAR(100) NOT NULL,
    BirthDate DATE NULL
);

Practice

Create a table Products with columns: ProductId (INT, PRIMARY KEY), ProductName (VARCHAR(200), NOT NULL), and Price (DECIMAL(10,2)).

Quiz

1. What is PRIMARY KEY?

A column that can be empty
A column (or set) that uniquely identifies each row
A column that stores text
A backup of the table

Correct! PRIMARY KEY uniquely identifies each row.

PRIMARY KEY is the column(s) that uniquely identify each row in the table.

2. What is VARCHAR?

A variable-length character/string type
A number type
A date type
A primary key constraint

Correct! VARCHAR stores variable-length text.

VARCHAR is a data type for variable-length character strings.

October 2026: temporal tables are history, not a backup

A system-versioned temporal table keeps the current row and older versions of that row. Temporal tables are available on supported SQL Server editions, including 2022 and 2025. They answer "what did this row look like last Tuesday?" They do not replace a backup. A dropped database does not come back from a history table that lived in that database.

Choose types on purpose: int or bigint for counts, decimal for money, datetime2 for an instant, nvarchar for text. On SQL Server 2022, store a JSON document in nvarchar(max). Do not start a 2022 script with a native json column.

CREATE TABLE payments (
    id int IDENTITY(1, 1) PRIMARY KEY,
    amount decimal(10, 2) NOT NULL,
    paid_at datetime2 NOT NULL
);

Try this. Create a practice table with an identity key, a decimal(10, 2) amount, and a datetime2 column. Insert one row and read it back.

Knowledge check

What does a temporal table replace?