SQL Server Tables

Tables are the fundamental building blocks of a SQL Server database. This lesson defines what a SQL Server 2025 table actually is: not just rows and columns, but a specific physical storage architecture, a set of column types that now reach well beyond ordinary relational data, and a family of specialized table behaviors you'll draw on throughout this course.

You can think of a table as a spreadsheet containing rows and columns. Columns are sometimes referred to as fields; rows are sometimes referred to as records. A column defines a specific attribute, and a row supplies the data for that attribute. For example, a table devoted to employees would have columns defining the specific attributes of an employee, such as employee number, name, and address. However, this doesn't necessarily mean the company has employees: it might employ only contractors, whose data is stored in another table. A table, then, will always have at least one column, but it won't always have rows. The diagram below illustrates the relationship between rows and columns in a table.

SQL Server adheres to much of the ANSI SQL standard (most recently, SQL:2023), but Transact-SQL (T-SQL) adds proprietary extensions that go beyond it. T-SQL introduces data types such as HIERARCHYID, purpose-built for representing tree-structured data like an org chart or a bill of materials, and indexing strategies, filtered indexes, columnstore indexes, and spatial indexes, that aren't part of the ANSI specification. SQL Server also extends beyond the standard in security and recovery: Transparent Data Encryption (TDE) and Always Encrypted protect data at rest and in use, and point-in-time recovery, built on the transaction log, lets you restore a database to any specific moment rather than only to the last full backup. None of this is a departure from the relational model; it's SQL Server's own implementation built on top of it.

Underneath its logical row-and-column definition, every SQL Server table is physically organized into partitions, at least one by default, and up to three allocation units, depending on the data types it contains:

• IN_ROW_DATA stores standard fixed- and variable-length columns within 8 KB data pages, grouped into 64 KB extents. A single row's in-row portion can't exceed 8,060 bytes.
• ROW_OVERFLOW_DATA automatically stores variable-length columns (VARCHAR, NVARCHAR, VARBINARY) when a row's combined columns push it past that 8,060-byte threshold, leaving a 24-byte pointer behind on the main data page.
• LOB_DATA (Large Object) stores data that exceeds 8 KB outright: VARCHAR(MAX), VARBINARY(MAX), XML, and oversized JSON or VECTOR payloads, in dedicated pages linked back to the base row.

A table's rows are also organized one of four ways. A clustered table stores rows in physical key order based on the clustered index, typically the primary key. A heap is an unindexed table with no particular row order, where each row is referenced by an 8-byte Row Identifier (FileID:PageID:SlotNumber). A clustered columnstore pivots and compresses data by column into rowgroups of up to roughly one million rows each, built for analytical workloads rather than single-row lookups. And a memory-optimized table resides entirely in RAM, using lock-free data structures, for the highest-throughput OLTP workloads.

A SQL Server 2025 table can hold relational, document, and AI vector data side by side in a single schema. A standard table supports up to 1,024 columns; a wide table, using SPARSE columns and a column set, can hold up to 30,000.

Two native types extend what a column can store. A native VECTOR column holds a high-dimensional float array, up to 1,998 dimensions at the default precision, stored in an optimized binary format for AI embeddings, and can be indexed with CREATE VECTOR INDEX, built on the DiskANN algorithm, for fast approximate nearest-neighbor similarity search. A native JSON column stores JSON documents in a parsed binary format rather than as plain NVARCHAR(MAX) text, letting you read and modify values directly without re-parsing the whole document on every operation; it can be indexed with CREATE JSON INDEX, though as of this writing that index type is still a preview feature.

Domain integrity keeps pace with these new types. A CHECK constraint can invoke native regular-expression functions like REGEXP_LIKE directly, and SQL Server 2025 also ships fuzzy string-matching functions, EDIT_DISTANCE and JARO_WINKLER_SIMILARITY among them, for comparing how similar two strings are rather than whether they match exactly. Both function families need to be turned on explicitly; the fuzzy-matching functions specifically require enabling the PREVIEW_FEATURES database-scoped configuration. A computed column, a virtual column derived from an expression such as Cost * Quantity, can be marked PERSISTED to physically store and index its result rather than recalculating it on every query:

CREATE TABLE Sales.OrderLines (
    OrderLineID INT IDENTITY(1,1) PRIMARY KEY,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    LineTotal AS (Quantity * UnitPrice) PERSISTED
);

Beyond ordinary user tables, SQL Server provides several specialized table behaviors:

• System-versioned temporal tables automatically track the full history of changes to a row, maintaining hidden ValidFrom/ValidTo period columns and moving prior versions into a paired history table on every update or delete, which enables point-in-time queries with FOR SYSTEM_TIME AS OF.
• Ledger tables provide cryptographic tamper-evidence inside the relational engine itself: every modification generates a hash recorded in a database ledger, so you can prove to an auditor that historical records haven't been altered outside the database.
• Graph tables (NODE and EDGE) model many-to-many and hierarchical relationships natively, using internal $node_id, $from_id, and $to_id pseudo-columns queried with the T-SQL MATCH() clause, rather than a hand-built junction table for every relationship.
• Partitioned tables horizontally divide one logical table across multiple filegroups using a partition function and scheme, which enables near-instant sliding-window archival with ALTER TABLE ... SWITCH PARTITION.
• Temporary tables (#Local, ##Global, @TableVariable) live in tempdb. SQL Server 2025 extends Accelerated Database Recovery, instantaneous transaction rollback, to tempdb itself, and Resource Governor's tempdb space controls, which prevent a runaway workload from filling tempdb, are now available in Standard Edition with the same functionality previously limited to Enterprise Edition.

A temporal table query looks like ordinary Transact-SQL, just with a time dimension added:

SELECT EmployeeID, JobTitle, Salary
FROM HR.Employees
FOR SYSTEM_TIME AS OF '2026-01-01'
WHERE EmployeeID = 1003;

How a table handles concurrent readers and writers changed meaningfully in SQL Server 2025. Every row now carries hidden metadata tracking the Transaction ID (TID) that last modified it. Instead of holding thousands of individual row or page locks in memory during a large update, which used to risk escalating to a full table lock, the engine holds a single lock on the TID and releases individual row locks as soon as each row is updated. Under Read Committed Snapshot Isolation, Lock After Qualification goes a step further, evaluating a statement's filter predicates against the latest committed row version before acquiring any lock at all, so only the rows that actually qualify for modification get locked.

A table in SQL Server 2025 isn't only a place data rests; it can actively feed other systems. Fabric Mirroring can replicate an operational table's changes to Microsoft Fabric continuously, offloading heavy analytical queries from the primary OLTP table without a separate ETL pipeline to build and maintain. Change Event Streaming goes further, streaming row-level INSERT, UPDATE, and DELETE events directly from the transaction log to Azure Event Hubs in near real time, without the staging-table overhead that traditional Change Data Capture requires.

When creating a table, there are a series of elements you can customize, depending on the table's purpose:

1. Column definitions, up to the 1,024-column limit covered above
2. Indexes, both clustered and non-clustered
3. The filegroup where the table is stored
4. Constraints such as primary key, foreign key, and check constraints
5. Default values

You'll learn how and why to define each of these elements as you proceed through this course. To create, modify, or delete a table, you'll also need the appropriate permissions.

Action	Required Permission
Create	System administrator (SA), or a user with create-table permission in that specific database
Modify	Table owner, DBO, SA, or a member of the sysadmin, db_owner, or db_ddladmin roles
Delete	Table owner, DBO, SA, or a member of the sysadmin, db_owner, or db_ddladmin roles

Think of the word schema as meaning a complete description of a database in a formal language supported by the database management system. A schema includes a formal description of every table in the database, their relationships and constraints, and everything else needed to create or reconstruct it, but it says nothing about the actual data contained in it. The term is worth introducing here because, in the context of SQL Server modeling specifically, it's often used more or less synonymously with the word model. Schema is the more database-native term; model is the more modeling-native one. The two stay closely linked throughout the rest of this course.

In the next lesson, you'll learn about data integrity.