Lesson 1

Before you can create databases and the objects inside them, you need a working grasp of the relational model itself. Without it, you won't be able to design your tables well, or predict how SQL Server will enforce the rules you build into them. This module outlines the basic principles of relational databases, so you can implement your own databases effectively.

After completing this module, you will be able to:

1. Define a SQL Server table
2. Describe the elements of a basic table
3. Define data integrity and explain how to enforce it
4. Define data normalization and denormalization
5. Recognize candidates for data normalization and denormalization
6. Explain when to use primary and foreign keys
7. Define null values

A relational database is a collection of tables, formally called relations. Each table represents one kind of thing: a customer, an order, a product. A table is made up of rows and columns, where each column, an attribute, defines one property of that thing, and each row, a tuple, is a single instance of it: one specific customer, one specific order. In SQL Server, tables are grouped into schemas for organization, for example Sales.Orders or Production.Products, so that related tables can be managed and secured together.

A column's data type defines its domain: the set of values it's allowed to hold. Most columns still use the familiar types, INT, VARCHAR, DATETIME2, and so on, but SQL Server 2025 adds two native types worth knowing from the start. A native JSON type stores JSON documents directly, rather than as plain text in an NVARCHAR column, and a native VECTOR type holds the numeric embeddings used for AI similarity search, compared with functions such as VECTOR_DISTANCE(). Both sit alongside the ordinary relational types you'll use for most columns in this module.

Tables rarely stand alone. The relational model supports relationships between them, which is itself a key part of maintaining data integrity. There are three kinds of relationships you'll encounter:

1. One-to-One (1:1): a single row in Table A relates to a single row in Table B, and vice versa. This is the least common relationship type, usually reserved for splitting a very wide table into two, for example separating an employee's core record from a seldom-accessed set of optional details.
2. One-to-Many (1:M): a single row in Table A can relate to one or more rows in Table B, but a given row in Table B relates to only one row in Table A. A customer placing many orders is the classic example.
3. Many-to-Many (M:N): a row in Table A can relate to one or more rows in Table B, and vice versa. A student enrolled in many courses, each of which has many students, is a typical case. SQL Server has no direct way to represent this kind of relationship between just two tables, so it's implemented with a third, intermediary table, also called a junction or bridge table, which breaks the M:N relationship down into two ordinary 1:M relationships.

Which relationship type you're modeling determines how you'll use primary and foreign keys, covered next.

Data integrity means the data in your tables stays accurate and internally consistent, and SQL Server enforces it in three distinct ways.

Entity integrity guarantees that every row can be told apart from every other row in its table. A PRIMARY KEY enforces this directly: it's automatically NOT NULL, and no two rows may ever share the same key value.

Domain integrity restricts what values a column may hold, through its data type, CHECK constraints, DEFAULT definitions, and NOT NULL rules. SQL Server 2025 extends this with native T-SQL regular expression functions, REGEXP_LIKE among them, which let a CHECK constraint validate that a column matches a specific pattern, a product SKU format, for instance, rather than just checking that it's the right data type.

Referential integrity keeps the relationships between tables, described above, consistent over time. A FOREIGN KEY ties a column in a child table to the primary key of a parent table, and the storage engine rejects any INSERT, UPDATE, or DELETE statement that would leave an orphaned reference behind, an order pointing to a customer that no longer exists, for example.

Every table should have a primary key: one or more columns that uniquely identify each row. Creating one in SQL Server also creates a clustered index by default, which physically determines the order rows are stored in on disk, so the choice of primary key affects performance as well as correctness, not just how rows are told apart. When more than one column is needed to guarantee uniqueness, the result is a composite key. Other columns that could have served as the primary key, but weren't chosen, are candidate keys, usually enforced instead with a UNIQUE constraint, which allows at most one NULL as an exception depending on the column's nullability.

A foreign key is how you implement the one-to-many relationships described earlier: a column in the child table (Orders, say) that points back to the primary key of the parent table (Customers). Use a foreign key anywhere one row logically belongs to, or depends on, another row elsewhere, rather than duplicating that parent row's data into every child row that references it. For the many-to-many case, the junction table carries two foreign keys, one to each of the tables it connects.

NULL represents a value that is unknown or missing, not zero, not an empty string, and not any other placeholder value standing in for "nothing." A column that allows NULL simply has no value recorded for a given row; a column defined NOT NULL can't be left that way. This distinction matters beyond storage: comparisons involving NULL don't evaluate to true or false the way ordinary comparisons do, they evaluate to unknown. That's why WHERE Column = NULL never matches a single row, no matter how many rows actually have no value in that column, and why IS NULL and IS NOT NULL exist as separate operators rather than reusing = and <>. Deciding which columns should allow NULL is itself a data-integrity decision made at design time, not an afterthought left to whoever happens to be inserting data later.

Normalization is the process of eliminating redundant data by decomposing a table into smaller, related tables, connected by the keys described above, so that each fact is stored in exactly one place. It proceeds through a series of normal forms, First (1NF), Second (2NF), Third (3NF), and beyond, each one resolving a specific category of redundancy; a table in 1NF has no repeating groups or multi-valued columns, for instance, while 3NF additionally removes columns that depend on something other than the whole primary key. A table is a good candidate for normalization when the same fact, a customer's address, say, is repeated across many rows, risking inconsistency if it's updated in some rows but missed in others.

Denormalization is the deliberate reverse: reintroducing some redundancy, usually by combining tables or storing a precomputed value, to reduce the number of joins a query needs and speed up reads. It's a reasonable trade in reporting or analytics workloads, where data is read far more often than it's written and query speed outweighs the small risk of inconsistency. It's a poor trade in transactional workloads with frequent updates, where that inconsistency risk is exactly what normalization exists to prevent in the first place. Recognizing which situation you're in, write-heavy and transactional versus read-heavy and analytical, is what tells you which direction to move a given design.

A small example makes the trade concrete. Imagine a single Orders table that stores the customer's name and address directly on every order row:

OrderID   CustomerName   CustomerAddress        OrderDate    Total
1001      Jane Pham      44 Birch St, Reno NV   2026-01-04   129.00
1002      Jane Pham      44 Birch St, Reno NV   2026-02-11   58.50
1003      Marco Reyes    9 Elm Ct, Boise ID      2026-02-14   212.75

Jane Pham's address appears twice, and if she moves, both rows (or a hundred rows, for a repeat customer) need to be updated, or they'll disagree with each other. Normalizing splits this into two tables, a Customers table holding each customer's details exactly once, and an Orders table that references it with a foreign key:

Customers
CustomerID   CustomerName   CustomerAddress
1            Jane Pham      44 Birch St, Reno NV
2            Marco Reyes    9 Elm Ct, Boise ID

Orders
OrderID   CustomerID   OrderDate    Total
1001      1            2026-01-04   129.00
1002      1            2026-02-11   58.50
1003      2            2026-02-14   212.75

Now Jane Pham's address lives in exactly one row, and a query that needs her order history joins Orders back to Customers on CustomerID rather than trusting that every order row was kept in sync by hand. A reporting system built on top of these two tables might still denormalize back to something closer to the first shape, trading that duplication back in deliberately, so that a dashboard query doesn't have to join millions of order rows to customer details on every refresh.

Underneath all of this, SQL Server guarantees that every modification follows the ACID properties: Atomicity, Consistency, Isolation, and Durability. Atomicity means a transaction's statements succeed or fail as a single unit, never leaving the database partway through a change. Consistency means a transaction can only move the database from one valid state to another, respecting every integrity rule covered above. Isolation means concurrent transactions don't see each other's uncommitted changes. Durability means that once a transaction commits, it survives a crash, which SQL Server guarantees through write-ahead logging: before any data page is modified on disk, the change is first recorded in the transaction log.

Enforcing isolation used to come at a real cost under heavy concurrent writes: updating millions of rows could mean holding millions of individual row locks, or escalating to a full table lock that blocks other users outright. SQL Server 2025's Optimized Locking addresses this with Transaction ID (TID) locking and Lock After Qualification (LAQ): rows are labeled with the ID of the last transaction that modified them, letting the engine hold a single lock on that ID rather than thousands of individual row locks, while evaluating predicates before acquiring locks at all under read-committed snapshot isolation. The ACID guarantee itself hasn't changed; this is a more efficient way of honoring it.

The relational model itself dates back to Edgar F. Codd's original work decades ago, but SQL Server 2025 builds on it rather than replacing it. The table below previews a few of the engine's current capabilities you'll see referenced through this module, each one grounded in the same relational fundamentals covered above:

Relational Concept	Classic SQL Server Implementation	SQL Server 2025 Addition
Relation (Table)	Rowstore, columnstore, or memory-optimized tables	Fabric Mirroring can replicate OLTP tables to Microsoft Fabric for analytics, without a separate ETL pipeline
Tuple (Row)	A record stored in 8 KB data pages or columnar segments	Optimized Locking (TID locking and Lock After Qualification) reduces the lock overhead of large updates
Attribute (Column)	Strongly typed fields such as INT, DATETIME2, and VARCHAR	Native VECTOR and JSON types, described above
Query execution	Nested Loops, Hash Match, and Merge Join operators	Optional Parameter Plan Optimization, part of Intelligent Query Processing, helps avoid bad plans caused by parameter sniffing

A single CREATE TABLE statement can combine the classic rules from this lesson with these newer types:

CREATE TABLE Production.Products (
    ProductID INT IDENTITY(1,1) CONSTRAINT PK_Products PRIMARY KEY,
    SKU VARCHAR(20) NOT NULL CONSTRAINT UQ_Products_SKU UNIQUE,
    CategoryID INT NOT NULL CONSTRAINT FK_Products_Categories
        FOREIGN KEY REFERENCES Production.Categories(CategoryID),
    Specs JSON NOT NULL,                  -- native JSON type
    DescriptionVector VECTOR(1536) NULL,  -- native VECTOR type, for AI similarity search
    CONSTRAINT CK_Products_SKU_Format CHECK (REGEXP_LIKE(SKU, '^[A-Z]{3}-[0-9]{4}$'))
);

Every line traces back to a concept from this lesson: a primary key for entity integrity, a foreign key for referential integrity, a CHECK constraint (here using a native regex function) for domain integrity, and ordinary and new native data types, each one defining its column's domain.

The lessons ahead in this module take each of these ideas further: how a SQL Server table is actually structured, how to enforce the integrity rules introduced here, and how to recognize when a design needs normalizing, or denormalizing.