Lesson 9

This module turned a bare CREATE TABLE statement into a fully specified one, one attribute at a time. A column isn't just a name; it's a bundle of decisions, datatype, size, nullability, default, and sometimes a value-generation rule or a custom type of your own, and every one of those decisions shapes what the column can hold, how much space it consumes, and what the database engine will and won't let through at insert time. The eight lessons in this module walked through that full set of decisions in order, and this page pulls them back together into one coherent picture before the next module moves on.

None of these attributes work in isolation, either. A column's datatype decides what precision and scale arguments even apply to it; its nullability decides whether a default is doing double duty, filling a gap and guaranteeing a value always exists, or just a convenience; and whether it needs to generate its own values decides whether IDENTITY, ROWGUIDCOL, both, or neither belongs on it. Reviewing all eight lessons together, rather than as eight disconnected facts, is what turns "I remember covering nullability" into being able to look at an unfamiliar table and immediately see which of its columns were designed carefully and which weren't.

Lesson	Attribute	Key Idea
1	Column attributes, generally	A column's attributes form a contract that SQL Server enforces automatically at insert time
2	Datatype	Match each column to the narrowest type that genuinely fits every value it will hold
3	Length, precision, scale	Always state the size argument explicitly; a bare declaration falls back to a smaller default than expected
4	Nullability	State NULL/NOT NULL explicitly; prefer NULL over a sentinel value for genuinely unknown data
5	Default values	A DEFAULT fills in a missing value on INSERT, but never overrides an explicit NULL
6	IDENTITY	Generates sequential values automatically; still needs its own uniqueness constraint
7	ROWGUIDCOL	Marks a table's canonical GUID column for FILESTREAM, replication, and generic tooling
8	User-defined types	CREATE TYPE keeps a repeated column declaration consistent across many tables

The module's introduction made the case for treating column attributes as a subject of their own, not an afterthought to table-creation syntax, by contrasting a table with no attributes thought through against one where every column's type, nullability, and default were chosen deliberately. A table built from generic VARCHAR(50) columns with nothing else specified will accept a HireDate of "last Tuesday" without complaint; a table built with the right attributes rejects that kind of bad data the moment it's offered, rather than letting it surface as a mystery three reports downstream:

-- Everything is a string, so nothing stops bad data:
CREATE TABLE Employees (
    EmployeeID VARCHAR(50), EmployeeName VARCHAR(50),
    HireDate   VARCHAR(50), Salary       VARCHAR(50)
);

-- Attributes declared up front reject it instead:
CREATE TABLE Employees (
    EmployeeID   INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    HireDate     DATE NOT NULL DEFAULT (GETDATE())
);

That contrast is the thread every later lesson in this module pulled on.

Lesson 2 covered the full catalog SQL Server 2025 makes available: exact numerics (INT, DECIMAL) for values that must add up exactly, approximate numerics (FLOAT, REAL) for scientific data that can tolerate a tiny representational error, character strings in both non-Unicode and Unicode families, binary strings for raw byte data, and SQL Server 2025's newer AI and semi-structured types, native JSON and the embedding-oriented VECTOR(n) type, alongside the long-standing XML type. It also covered the specialized types worth knowing by name, UNIQUEIDENTIFIER, ROWVERSION, HIERARCHYID, GEOGRAPHY/GEOMETRY, and flagged TEXT, NTEXT, and IMAGE as deprecated types to replace with their MAX-length equivalents in any new design.

Lesson 3 covered the size argument that follows many datatype names, length for character and binary types, precision and scale for DECIMAL/NUMERIC, and fractional-second precision for DATETIME2/TIME, along with the two different ways an oversized value fails: character and binary overflow is rejected outright, while decimal scale overflow is rounded rather than truncated. It also covered a cost many people overlook, oversized key columns can push a composite index past SQL Server's 900-byte key-size limit, and the asymmetry between growing a column's size later, cheap, and shrinking one, which requires checking every existing value against the new limit first.

Lesson 4 covered nullability as its own deliberate attribute, controlled with explicit NULL/NOT NULL keywords rather than left to a session or database default. It made the case for NULL over a sentinel value like -1 or a placeholder date, since a sentinel is indistinguishable from a real value unless every query remembers to filter it out, while NULL has unambiguous, built-in support in IS NULL, aggregate functions, and joins. It also covered the two-step backfill pattern, update existing NULLs, then tighten the column, needed before ALTER COLUMN can enforce NOT NULL on a column that already has rows.

Lesson 5 covered the DEFAULT constraint, the value SQL Server substitutes when an INSERT leaves a column unspecified, from a literal constant to a system function call like SYSDATETIME() or JSON_OBJECT():

OrderStatus VARCHAR(20) NOT NULL
    CONSTRAINT DF_Orders_OrderStatus DEFAULT ('Pending')

It covered explicit constraint naming to keep a default easy to find and change later, the behavioral difference between adding a default to an existing column (future inserts only) versus a brand-new one (WITH VALUES to backfill existing rows), and the four specific circumstances under which a default actually fires, most notably that an explicit NULL in an INSERT ... SELECT bypasses the default rather than triggering it.

Lesson 6 covered IDENTITY(seed, increment), the standard way to generate a self-incrementing surrogate key without application code tracking the next available value. It covered the rules (one identity column per table, an integer or exact-numeric type, NOT NULL):

CREATE TABLE dbo.Orders (
    OrderID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
);

It also covered how to find existing identity columns with sys.identity_columns or the $IDENTITY pseudocolumn, the difference between SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT() for retrieving a just-generated value, and two points worth remembering together: IDENTITY only generates values, it doesn't by itself guarantee uniqueness, and gaps in the sequence are normal, expected behavior rather than a sign of corruption.

Lesson 7 covered ROWGUIDCOL, a marker on a single UNIQUEIDENTIFIER column that designates it as a table's canonical row identifier for SQL Server's own subsystems and generic scripts, mandatory for FILESTREAM storage and replication, recommended for schema-agnostic $ROWGUID queries, and otherwise optional:

StreamGuid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
    CONSTRAINT UQ_StreamGuid UNIQUE
    CONSTRAINT DF_StreamGuid DEFAULT (NEWSEQUENTIALID())

Like IDENTITY in the previous lesson, ROWGUIDCOL doesn't generate values or enforce uniqueness on its own; that still takes an explicit DEFAULT and a UNIQUE or PRIMARY KEY constraint. The lesson also weighed a GUID's real cost, 16 bytes per row and weaker clustered-index locality than a compact integer identity column, against its one genuine advantage: uniqueness that holds across independent databases, not just within one table.

Lesson 8 covered CREATE TYPE, which builds a named alias type from an existing base type, length, and nullability, so that every column meant to hold the same kind of value, an address, say, stays consistent across every table that uses it. It covered the lack of an ALTER TYPE statement, meaning a definition change requires dropping and recreating the type once nothing references it anymore, the distinct, non-interchangeable purpose of a table type for table-valued parameters, and the simpler CLR user-defined-type alternative for cases that genuinely need custom behavior rather than just a named alias.

Three ideas kept resurfacing across otherwise unrelated lessons, and it's worth naming them explicitly now that the whole module is in view.

State it explicitly; don't lean on a fallback. Lesson 3 showed that a bare varchar or decimal column silently falls back to a length of 1 or a scale of 0, not the maximum anyone would expect. Lesson 4 showed the same pattern for nullability: a column with neither NULL nor NOT NULL stated falls back to a session or database setting that's invisible at the point you're reading the CREATE TABLE statement. Lesson 5 extended this to defaults: a value left out of an INSERT only gets a sensible default if one was actually declared. In every case, the fix is the same habit: write down what you mean on the column itself, rather than trusting a setting that lives somewhere else and can differ from one environment to the next.

A value-generation feature doesn't automatically guarantee uniqueness. Lesson 6's IDENTITY property and Lesson 7's ROWGUIDCOL flag both generate values for you, and it's tempting to assume that generation implies no two rows can ever collide. Neither one actually enforces that on its own; a PRIMARY KEY or UNIQUE constraint is what does the actual checking, in both cases, on every single insert. Pairing a generation attribute with an explicit constraint, rather than assuming the generation attribute covers it, is the pattern that holds up in both lessons.

Every size and type choice trades storage against capability. Lesson 2's Unicode types cost twice the storage of their non-Unicode counterparts in exchange for the full range of international characters. Lesson 3's oversized columns waste space and can even block the indexing you'd planned. Lesson 7's UNIQUEIDENTIFIER costs 16 bytes per row, four times an INT identity column, in exchange for uniqueness that holds across independent databases rather than just one table. None of these trade-offs has a single universally correct answer; each one depends on what the column actually needs to do, which is exactly why this module spent a full lesson on each attribute rather than treating column design as one-size-fits-all.

Before writing a CREATE TABLE statement for a real table, this module's eight lessons boil down to a short set of questions worth running through for every single column:

• What's the narrowest datatype that genuinely fits every value this column will ever hold (Lesson 2)?
• What length, precision, scale, or fractional-second precision does that datatype actually need, stated explicitly rather than left to a default (Lesson 3)?
• Can this column's value ever genuinely be unknown or not applicable, and if so, is NULL the right way to represent that rather than a sentinel value (Lesson 4)?
• Does this column need a value supplied automatically when one isn't given, and if so, what should that value be (Lesson 5)?
• Does this column need to generate its own sequential value, and does it need a UNIQUE or PRIMARY KEY constraint alongside that generation (Lesson 6)?
• Does this table need a stable, globally unique row identifier for replication, FILESTREAM, or schema-agnostic tooling, beyond what an ordinary primary key already provides (Lesson 7)?
• Is this exact combination of base type, length, and nullability going to repeat across enough other columns or tables that a user-defined type is worth the consistency it buys (Lesson 8)?

Running through that list for every column, rather than reaching for a generic VARCHAR(50) out of habit, is the single biggest difference between a table that quietly accumulates bad data and one that rejects it at the door.

Every attribute this module covered shows up at once in a single, realistic table definition. The order the attributes appear below matches the order the lessons covered them:

CREATE TYPE address FROM VARCHAR(100) NOT NULL;  -- Lesson 8: user-defined type

CREATE TABLE dbo.Orders
(
    OrderID          INT IDENTITY(1, 1) NOT NULL  -- Lesson 6: IDENTITY
        CONSTRAINT PK_Orders_OrderID PRIMARY KEY CLUSTERED,

    OrderGuid        UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL  -- Lesson 7: ROWGUIDCOL
        CONSTRAINT UQ_Orders_OrderGuid UNIQUE NONCLUSTERED
        CONSTRAINT DF_Orders_OrderGuid DEFAULT (NEWSEQUENTIALID()),  -- Lesson 5: DEFAULT

    ShipToAddress    address NOT NULL,            -- Lesson 8: user-defined type; Lesson 4: NOT NULL

    DiscountPercent  DECIMAL(5, 2) NOT NULL        -- Lesson 2 & 3: datatype, precision, scale
        CONSTRAINT DF_Orders_DiscountPercent DEFAULT (0.00),

    OrderStatus      VARCHAR(20) NOT NULL          -- Lesson 2: datatype; Lesson 3: length
        CONSTRAINT DF_Orders_OrderStatus DEFAULT ('Pending'),

    CreatedAt        DATETIME2(3) NOT NULL         -- Lesson 2 & 3: datatype, fractional-second precision
        CONSTRAINT DF_Orders_CreatedAt DEFAULT (SYSDATETIME())
);

Every column here reflects a choice, not a default left unmade: an integer identity column for a compact, self-generating primary key; a ROWGUIDCOL column, carefully distinguished from that primary key, for a table that might eventually replicate; a user-defined type keeping every address column in the database consistent; precise, deliberately scaled decimal and date types; and every single column with its nullability and default stated explicitly rather than left to chance. None of that is incidental. It's the direct, visible result of working through this module's eight lessons one attribute at a time, and it's the standard this course will hold every table definition to from here forward.

Lesson 1 opened this module with a deliberately bare Employees table, four columns, all VARCHAR(50), nothing else specified, to show what a table looks like when none of this module's lessons have been applied yet. It's worth closing the module by rebuilding that same table with everything covered since then:

CREATE TYPE phoneNumber FROM VARCHAR(20) NULL;  -- Lesson 8

CREATE TABLE dbo.Employees
(
    EmployeeID   INT IDENTITY(1, 1) NOT NULL           -- Lesson 6
        CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY CLUSTERED,

    EmployeeGuid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL   -- Lesson 7
        CONSTRAINT UQ_Employees_EmployeeGuid UNIQUE NONCLUSTERED
        CONSTRAINT DF_Employees_EmployeeGuid DEFAULT (NEWSEQUENTIALID()),

    EmployeeName NVARCHAR(100) NOT NULL,                -- Lesson 2, 3, 4

    HireDate     DATE NOT NULL                          -- Lesson 2, 4
        CONSTRAINT DF_Employees_HireDate DEFAULT (GETDATE()),  -- Lesson 5

    Phone        phoneNumber NULL,                      -- Lesson 8, 4

    Salary       DECIMAL(9, 2) NOT NULL                 -- Lesson 2, 3
);

Every column in this version reflects a deliberate choice rather than an accident of having typed VARCHAR(50) four times in a row. That's the real payoff of this module: not memorizing eight separate pieces of syntax, but building the habit of asking, for every column in every table from here on, exactly what this page's self-check list asks.

With every column attribute covered, the next module turns from defining a table's columns to the broader work of creating, managing, and constraining the tables themselves in SQL Server 2025. Before moving on, it's worth revisiting this module's exercises and quiz, linked from their respective lessons, to make sure each attribute covered here feels natural rather than merely familiar.

Before moving on to the next module, click the Quiz link below on the left to check your knowledge of the material covered in this module.
SQL-Server Column Attributes - Quiz