Lesson 1

The previous module covered relational database concepts in the abstract: entities, data integrity, primary and foreign keys, normalization. This module turns from theory to practice, building the actual tables a SQL Server 2025 database runs on. Every one of those tables is made up of columns, and every column is defined by a set of attributes that together decide what can go into it, how it's stored, and how the database engine treats it automatically. Before you write your first CREATE TABLE statement, it helps to know what those attributes are and why each one matters.

After completing this module, you will be able to:

1. Identify the correct data types to use for your columns
2. Specify length, precision, and scale for your columns
3. State when to allow null values
4. Specify default values for your columns
5. Use the IDENTITY property and ROWGUIDCOL flag
6. Create and use your own data types

A column attribute is a property you declare when you define a column, as part of a CREATE TABLE or ALTER TABLE statement. Some attributes are required for every column, a column always has a name and a data type, while others are optional, and you only specify them when the column needs that particular behavior. Taken together, a column's attributes form a contract: they tell SQL Server, and anyone reading the table definition later, exactly what kind of values belong in that column and what the engine should do with them automatically.

It's easy to underestimate how much weight these small declarations carry until you see a table that got them wrong. Imagine an Employees table built without much thought to column attributes:

CREATE TABLE Employees (
    EmployeeID   VARCHAR(50),
    EmployeeName VARCHAR(50),
    HireDate     VARCHAR(50),
    Salary       VARCHAR(50)
);

Every column here is the same generic string type, nothing is marked NOT NULL, nothing generates its own values, and nothing has a default. In practice, that table will let you insert a row with no name, a HireDate of "last Tuesday", and a Salary of "a lot", and SQL Server won't object to any of it, because nothing in the column definitions says it should. Every query, every report, and every application built on top of that table then has to defend itself against bad data that the table itself should have rejected in the first place.

Now compare it to a version built with the right attributes declared up front:

CREATE TABLE Employees (
    EmployeeID   INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    HireDate     DATE NOT NULL DEFAULT (GETDATE()),
    Department   VARCHAR(50) NULL
);

Four columns, four sets of attributes. EmployeeID is an integer that generates its own values. EmployeeName is a variable-length string that can't be left blank. HireDate is a date that defaults to today if nothing else is supplied. Department is a string that's allowed to be unknown. None of that behavior is incidental: it's the direct result of the attributes declared on each column. The database engine does the validation work for you here; an attempt to insert text into HireDate, or to leave EmployeeName blank, fails immediately, at the moment the bad data is offered, rather than surfacing as a mysterious bug three reports downstream. That's the payoff of column attributes done well, and it's why this module treats them as a subject in their own right rather than an afterthought to table creation syntax.

The single most important attribute on any column is its data type, since it determines the domain of values the column can legally hold. SQL Server 2025 groups its built-in types into a few broad families you'll use constantly:

• Exact numerics: INT, BIGINT, SMALLINT, TINYINT, DECIMAL/NUMERIC
• Character strings: VARCHAR, CHAR, and their Unicode counterparts NVARCHAR, NCHAR
• Date and time: DATE, TIME, DATETIME2, DATETIMEOFFSET
• Other common types: BIT (Boolean-style flags), UNIQUEIDENTIFIER (GUIDs), VARBINARY (raw binary data)

Picking a type isn't just a formality. A Price column typed as INT can't store cents; typed as DECIMAL, it can. A Notes column typed as CHAR(500) pads every row out to 500 characters even when a note is three words long; typed as VARCHAR(500), it only stores what's actually there. Choosing the correct type for each column is what the rest of this module, and the lessons that follow this one, walk through in detail.

SQL Server 2025 also adds a few data types worth knowing about going in, since you'll see them referenced later in this module:

• A native VECTOR(n) type stores a fixed-length array of floating-point numbers, the kind of numeric embedding used by AI and similarity-search workloads. Columns of this type are typically queried with the VECTOR_DISTANCE() function, which measures how close two embeddings are to each other, rather than with ordinary equality or range comparisons.
• A native JSON type stores JSON documents in a pre-parsed binary form, rather than as plain text in an NVARCHAR column, and validates that every value written to it is well-formed JSON.
• A CHECK constraint can call the native REGEXP_LIKE function to enforce a pattern, an email address format or a product SKU, directly at the column level, instead of relying on looser LIKE wildcards.

None of these replace the core types above for everyday table design, but they're part of the current data type landscape and worth recognizing when you encounter them. A later lesson in this module covers the full range of available data types, built-in and new, in depth.

Many data types take a size argument alongside the type name itself, and getting it right matters as much as picking the type. For character and binary types, that argument is length: VARCHAR(50) holds up to 50 characters, while VARCHAR(MAX) holds up to 2 GB of text. For numeric types like DECIMAL, the size argument splits into precision and scale: precision is the total number of digits a value can hold, and scale is how many of those digits sit to the right of the decimal point. DECIMAL(9,2) can store a value like 1234567.89, nine digits total, two of them after the decimal, but it has no room for a tenth digit or a third decimal place; SQL Server rejects or rounds a value that doesn't fit.

The same idea applies to time-based types: DATETIME2 and TIME both accept a scale argument, DATETIME2(3), for instance, that controls how many digits of fractional-second precision the column keeps. Choosing these values too small clips off data you actually need; choosing them far larger than necessary wastes storage across every row in the table. A later lesson in this module covers how to size each type appropriately for the data it will actually hold.

Every column also carries a nullability attribute: NULL, meaning the column may hold an unknown or missing value, or NOT NULL, meaning every row must supply a real value for that column. This isn't a minor detail; it directly affects data integrity. A NOT NULL constraint on EmployeeName guarantees you'll never end up with an employee record with no name, while a NULL setting on Department acknowledges that a newly hired employee might not be assigned to one yet. Deciding which columns should allow NULL, and which shouldn't, is a modeling decision, not just a syntax choice, and it's one you'll make for every column in every table you design.

A DEFAULT constraint tells SQL Server what value to use for a column when an INSERT statement doesn't supply one explicitly. Defaults can be a literal, DEFAULT 0 or DEFAULT 'Pending', or a function call evaluated at insert time, as with HireDate DATE NOT NULL DEFAULT (GETDATE()) in the Employees example above, which automatically stamps today's date onto any new row that doesn't specify one. Defaults are especially useful paired with NOT NULL columns: they let you enforce that a value is always present, without requiring every application or user writing an INSERT statement to remember to supply it themselves.

Some columns are meant to generate their own values rather than accept values supplied by the application. The IDENTITY property, seen on EmployeeID above, turns an integer column into an auto-incrementing counter: IDENTITY(1,1) starts at 1 and increases by 1 with every new row, and SQL Server assigns the next value automatically on every INSERT. This is the standard way to generate a simple surrogate primary key.

ROWGUIDCOL serves a related but distinct purpose. Rather than marking an integer column as self-generating, it designates a single UNIQUEIDENTIFIER column on the table as that row's globally unique identifier, typically paired with a default of NEWID() or NEWSEQUENTIALID() so a fresh GUID is generated for every new row. ROWGUIDCOL is most often used in replication and distributed-systems scenarios, where a value guaranteed unique across multiple databases, not just within one table, matters more than a compact integer key.

Beyond the built-in types, SQL Server also lets you define your own. The most common approach is an alias type, created with CREATE TYPE, which wraps a built-in type with a specific size and nullability under a name of your own choosing:

CREATE TYPE PhoneNumber FROM VARCHAR(20) NOT NULL;

Once defined, PhoneNumber can be used as a column's data type just like VARCHAR or INT, and every column that uses it is guaranteed to share the same underlying size and nullability. The payoff is consistency: if every table in your database uses a shared PhoneNumber type instead of each one independently choosing VARCHAR(15), VARCHAR(20), or CHAR(12), you avoid the kind of silent mismatches that cause truncated data or failed joins later on. This module's later lessons cover how to create and use your own data types in more depth.

Not every attribute decision is about what a column holds; some are about how the table itself is stored and indexed, and those choices are made at the table level with a WITH clause rather than on an individual column. SQL Server's In-Memory OLTP engine is the clearest example: adding WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) to a CREATE TABLE statement keeps that table's rows resident in memory rather than on disk-based pages, which changes how the table can be indexed and queried.

CREATE TABLE Sales.OrderLines (
    OrderLineID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    OrderID      INT NOT NULL,
    ProductID    INT NOT NULL,
    Quantity     INT NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

A memory-optimized table supports two kinds of indexes built for in-memory access patterns: a hash index, organized into buckets for fast equality lookups, and a nonclustered index built as a Bw-tree, which supports range scans and ordered access the way a traditional B-tree does on disk.

For analytics against that same memory-optimized table, SQL Server can add an updatable nonclustered columnstore index (CCI), which compresses the table's data into column-oriented segments optimized for scanning and aggregation rather than row-by-row lookups. Rows that have changed too recently to be compressed yet are kept in an in-memory hot tail, which a background task periodically migrates into the compressed columnstore; this tail serves a role similar to the delta store used by on-disk columnstore indexes, but it is itself memory-resident rather than a disk-based structure. A separate deleted rows table (DRT) tracks which columnstore rows have been deleted, since the compressed column segments themselves aren't rewritten on every delete.

This combination, memory-optimized storage for fast transactional writes, paired with a columnstore index for real-time analytics against the same table, is what SQL Server calls operational analytics: reporting queries run directly against live transactional data, without a separate extract-transform-load step copying it into a reporting table first. It's an advanced, table-level attribute choice rather than one you'll reach for on every table, but it's worth recognizing as part of the full range of decisions column and table attributes let you make.

Together, these attributes, data type, length/precision/scale, nullability, defaults, IDENTITY/ROWGUIDCOL, custom data types, and table-level storage options like memory optimization, are the building blocks of every table you'll design in SQL Server. In the next lesson, you will learn about the different data types available to you in more depth.