CREATE TABLE

CREATE TABLE

What it means

CREATE TABLE defines a new table — its columns, each column’s data type, and any constraints. It’s the foundational DDL (Data Definition Language) statement for setting up a database schema.

Examples

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY(1,1),   -- auto-incrementing primary key
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(255) UNIQUE,
    HireDate DATE NOT NULL DEFAULT GETDATE(),
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    DepartmentId INT,
    CONSTRAINT FK_Employees_Departments FOREIGN KEY (DepartmentId)
        REFERENCES Departments(Id)
);

Common mistake

Choosing VARCHAR for text that might contain non-English characters — VARCHAR only stores single-byte characters, so names, addresses, or content in most non-Latin scripts get corrupted or truncated. NVARCHAR (Unicode) is the safer default unless you have a specific, deliberate reason to use VARCHAR.

CREATE TABLE Users (
    Name VARCHAR(50)     -- 'José' or '田中' may not store correctly
);

-- Better default: NVARCHAR handles Unicode text correctly
CREATE TABLE Users (
    Name NVARCHAR(50)
);

Quick practice

1. What does IDENTITY(1,1) on a column mean?

   Answer

   The column auto-increments — starting at 1, incrementing by 1 for each new row — and doesn't need to be specified in INSERT statements.

2. What’s the risk of using VARCHAR instead of NVARCHAR for names or free text?

   Answer

   VARCHAR only supports single-byte characters, so non-Latin-script text (accented characters, Chinese, Arabic, etc.) can be corrupted or fail to store correctly; NVARCHAR stores Unicode properly.

3. What does DEFAULT GETDATE() on a column do?

   Answer

   Automatically fills that column with the current date/time if no explicit value is provided in the INSERT statement.