Tables, Rows & Columns

Every piece of data you store in SQL Server ultimately lives in a table. Tables are the fundamental structure of the relational model — the place where raw information becomes organised, queryable, and meaningful. But a table is not just a spreadsheet with a name. It is a precisely defined object with columns that enforce data types, constraints that reject invalid data, and a physical structure on disk that determines how fast queries can find what they are looking for. Understanding how SQL Server thinks about tables — what a row really is, how columns are stored, what constraints do and why they exist — gives you the foundation for every lesson that follows. This lesson builds and examines the DataplexaStore tables in depth, explaining every decision behind their design.

What is a Table

A table is a named collection of rows, where every row represents one instance of the thing being described and every column represents one attribute of that thing. In DataplexaStore the Users table has one row per registered customer and columns for the customer's name, email, city, country, membership tier, and join date. That definition sounds simple, but SQL Server enforces it strictly — you cannot store a number in a column defined as a date, you cannot leave a column blank if it was defined as NOT NULL, and you cannot insert a row that would violate a foreign key relationship with another table. All of this enforcement happens at the database engine level before any application code runs, which means bad data is stopped at the source rather than discovered later in a report or a customer complaint.

Why it exists: enforcing structure at the table level rather than in application code means the rules apply to every connection — every application, every script, every developer, every direct database session. A constraint defined on the table cannot be accidentally skipped by a new developer who does not know the business rule exists in the codebase.

Real-world use: every major business system — ERP, CRM, e-commerce, banking — is built on tables like these. The DataplexaStore schema of Users, Products, Orders, OrderItems, and Reviews is structurally identical to what you would find inside Shopify, WooCommerce, or any custom-built retail platform.

-- View all user-created tables in the DataplexaStore database
-- INFORMATION_SCHEMA is the ANSI-standard way to query table metadata

USE DataplexaStore;

SELECT
    TABLE_NAME,                     -- name of the table
    TABLE_TYPE                      -- BASE TABLE (real table) or VIEW
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'     -- exclude views
ORDER BY TABLE_NAME;

• INFORMATION_SCHEMA.TABLES is portable across SQL Server, MySQL, and PostgreSQL — it is the standard way to query schema metadata without using SQL Server-specific system tables
• BASE TABLE means a real, persistent table stored on disk — as opposed to a VIEW, which is a saved query that looks like a table but contains no data of its own
• Five tables form the entire DataplexaStore schema — this deliberately small footprint keeps the focus on SQL skills rather than navigating a complex schema

Columns — The Structure of a Row

Every column in a table has three mandatory properties: a name, a data type, and a nullability setting. The name identifies the attribute. The data type defines what kind of value the column can hold and how much space it occupies — an INT is always 4 bytes, a DATE is always 3 bytes, an NVARCHAR(100) can hold up to 100 Unicode characters. Nullability determines whether the column is allowed to be empty — NOT NULL means a value is required on every row, NULL means the column is optional. Choosing the right data type for each column is one of the most consequential design decisions you make. An oversized type wastes storage and slows down comparisons. An undersized type causes truncation errors or forces workarounds later. A type mismatch between a column and the values being compared against it causes SQL Server to silently convert one side before comparing, which prevents index use and makes queries dramatically slower.

-- Inspect every column in every DataplexaStore table
-- This reveals the full schema: data types, nullability, and defaults

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,                      -- int, nvarchar, decimal, date, etc.
    CHARACTER_MAXIMUM_LENGTH        AS max_length,   -- for string columns only
    NUMERIC_PRECISION               AS precision,    -- for numeric columns: total digits
    NUMERIC_SCALE                   AS scale,        -- for numeric columns: decimal places
    IS_NULLABLE,                    -- YES or NO
    COLUMN_DEFAULT                  -- default value if the column is not supplied on INSERT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('Users','Products','Orders','OrderItems','Reviews')
ORDER BY TABLE_NAME, ORDINAL_POSITION;  -- ORDINAL_POSITION preserves original column order

• Every column in DataplexaStore is NOT NULL — this is a deliberate design choice to ensure every row is complete and meaningful; NULL columns should only be used for genuinely optional attributes
• COLUMN_DEFAULT of (getdate()) means SQL Server automatically fills in the current timestamp on INSERT if the application does not supply a value — no application code required
• NVARCHAR stores Unicode text (supporting every language including Arabic, Chinese, Hindi) while VARCHAR stores only ASCII — the N prefix doubles the storage per character but is the correct choice for any column that might hold names or addresses from anywhere in the world

Primary Keys — Uniquely Identifying Every Row

A primary key is a column or combination of columns whose value is unique across every row in the table and is never NULL. SQL Server enforces this with a unique index that it creates automatically when you define the primary key. In DataplexaStore every table uses a single-column integer primary key with IDENTITY(1,1), which means SQL Server generates the value automatically — starting at 1 and incrementing by 1 for each new row. You never insert a value into an IDENTITY column; SQL Server owns that column entirely. The primary key does two things simultaneously: it uniquely identifies each row so you can reference it from other tables, and because SQL Server builds a clustered index on the primary key by default, it also determines the physical order in which rows are stored on disk. Rows with adjacent primary key values are stored on adjacent pages, which makes range scans along the primary key extremely efficient.

-- View all primary key constraints across the DataplexaStore schema

SELECT
    tc.TABLE_NAME,
    kcu.COLUMN_NAME                 AS primary_key_column,
    tc.CONSTRAINT_NAME              AS pk_constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
   AND tc.TABLE_NAME      = kcu.TABLE_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY tc.TABLE_NAME;

-- Also check IDENTITY settings — which columns are auto-generated?
SELECT
    OBJECT_NAME(object_id)          AS table_name,
    name                            AS column_name,
    seed_value,                     -- starting value (1 for IDENTITY(1,1))
    increment_value,                -- step size (1 for IDENTITY(1,1))
    last_value                      -- most recently generated value
FROM sys.identity_columns;

• last_value confirms how many rows have been inserted — UserID last_value of 10 means 10 users have been inserted since the table was created, and the next INSERT will receive UserID 11
• IDENTITY columns cannot be inserted into manually by default — attempting to do so produces an error unless you first run SET IDENTITY_INSERT tablename ON, which is typically only used during data migrations
• The convention of naming primary key constraints PK_TableName is not required but makes error messages and execution plan output far easier to read — always name your constraints explicitly

Foreign Keys — Enforcing Relationships Between Tables

A foreign key is a column in one table whose values must match a value that exists in the primary key column of another table. In DataplexaStore the Orders table has a UserID column that is a foreign key referencing Users.UserID. This means it is impossible to insert an order for a user who does not exist — SQL Server will reject the INSERT with a foreign key violation error. It is also impossible to delete a user who has orders, unless you delete the orders first or define a cascading delete rule. Foreign keys are the mechanism that makes the relational model work: they guarantee that every relationship between tables is valid, consistent, and navigable. Without them, orphaned rows accumulate silently — orders with no customer, order items with no order — and every query that joins tables has to defensively handle the possibility of missing related rows.

-- View all foreign key relationships in DataplexaStore
-- This maps the full dependency graph of the schema

SELECT
    fk.name                         AS foreign_key_name,
    OBJECT_NAME(fk.parent_object_id)
                                    AS child_table,   -- the table that contains the FK column
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
                                    AS fk_column,     -- the column holding the foreign key value
    OBJECT_NAME(fk.referenced_object_id)
                                    AS parent_table,  -- the table being referenced
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
                                    AS referenced_column, -- the primary key column being pointed to
    fk.delete_referential_action_desc AS on_delete,   -- NO_ACTION, CASCADE, SET_NULL, SET_DEFAULT
    fk.update_referential_action_desc AS on_update
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
ORDER BY child_table;

• NO_ACTION on delete means SQL Server raises an error and blocks the delete if child rows exist — the application must delete child rows first, which is the safest default for business data
• CASCADE on delete means deleting a parent row automatically deletes all child rows — convenient but dangerous if misapplied; cascading a delete on Users would silently destroy all orders and reviews for that customer
• The dependency order revealed here — Users and Products have no parents; Orders depends on Users; OrderItems depends on Orders and Products; Reviews depends on Products and Users — is the order you must follow when inserting data or dropping tables

CHECK and UNIQUE Constraints

Beyond primary and foreign keys, SQL Server supports two more constraint types that are essential for data quality. A CHECK constraint defines a boolean expression that every row must satisfy — for example, Price must be greater than zero, StockQty cannot be negative, and Rating must be between 1 and 5. SQL Server evaluates the CHECK expression on every INSERT and UPDATE and rejects any row that fails it. A UNIQUE constraint ensures no two rows in the table have the same value in the constrained column — for example, every user must have a distinct email address. Unlike a primary key, a UNIQUE constraint allows NULL values (though SQL Server treats each NULL as distinct, so multiple NULLs are allowed). Together, primary keys, foreign keys, CHECK constraints, and UNIQUE constraints form a complete data integrity system enforced entirely within the database.

-- View all CHECK and UNIQUE constraints in DataplexaStore

SELECT
    cc.TABLE_NAME,
    cc.CONSTRAINT_NAME,
    cc.CONSTRAINT_TYPE,             -- CHECK or UNIQUE
    cc.CHECK_CLAUSE                 -- the boolean expression (for CHECK constraints only)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS cc
LEFT JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS chk
    ON cc.CONSTRAINT_NAME = chk.CONSTRAINT_NAME
WHERE cc.CONSTRAINT_TYPE IN ('CHECK', 'UNIQUE')
ORDER BY cc.TABLE_NAME, cc.CONSTRAINT_TYPE;

-- Test a CHECK constraint violation live
-- This INSERT should fail because Price = -5 violates CHECK (Price > 0)
INSERT INTO Products (ProductName, Category, Price, StockQty)
VALUES ('Test Item', 'Electronics', -5.00, 10);

• Msg 547 is the standard foreign key and CHECK constraint violation error — the error message always names the specific constraint that was violated, making it immediately clear which rule the data broke
• The UNIQUE constraint on Users.Email (UQ_Users_Email) means attempting to register two accounts with the same email address will fail at the database level — no application-level duplicate check can be bypassed
• CHECK constraints evaluate to true or unknown — a NULL value in a checked column passes the constraint because NULL compared to anything returns unknown, not false; only explicit false rejects the row

Viewing a Complete Table Definition

As you work with SQL Server in production you will frequently need to understand an existing table's full definition quickly — all columns, all constraints, all defaults, all indexes. There are several ways to do this. In SSMS you can right-click a table and choose Script Table as CREATE TO. From T-SQL the most reliable approach is querying the system catalog views directly, which gives you programmatic access to the same information without relying on a GUI. The query below assembles a complete column-level picture of the Users table including every constraint attached to each column.

-- Complete column-level definition of the Users table
-- Combines column metadata with any constraint attached to each column

SELECT
    c.name                          AS column_name,
    tp.name                         AS data_type,
    c.max_length,                   -- bytes for varchar/nvarchar; -1 means MAX
    c.precision,
    c.scale,
    c.is_nullable,                  -- 1 = NULL allowed, 0 = NOT NULL
    c.is_identity,                  -- 1 = IDENTITY column (auto-generated)
    dc.definition                   AS default_value,   -- expression used as default
    cc.definition                   AS check_expression -- CHECK constraint if any
FROM sys.columns c
JOIN sys.types tp
    ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.default_constraints dc
    ON c.default_object_id = dc.object_id
LEFT JOIN sys.check_constraints cc
    ON cc.parent_object_id = c.object_id
   AND cc.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID('Users')  -- change table name to inspect any table
ORDER BY c.column_id;

• max_length for NVARCHAR columns is in bytes — NVARCHAR(100) stores up to 100 characters but each character is 2 bytes, so max_length shows 200; always divide by 2 to get the character limit
• is_identity = 1 on UserID confirms SQL Server controls that value — you will never see it in an INSERT column list for this table
• default_value of ('Standard') on MembershipTier means any INSERT that omits MembershipTier automatically receives the value Standard — a sensible default that removes the burden of specifying it for every new user

Summary Table

Practice Questions

Quiz

Next up - SQL Server Data Types - Picking the right type for every column keeps your data accurate and your queries fast.