Home / SQL / T-SQL / Understanding SQL Server Tables, Data Types, and Constraints

Understanding SQL Server Tables, Data Types, and Constraints

On

When you're learning SQL Server development, understanding tables, data types, and constraints forms the foundation of everything you'll build. These three concepts work together to define how data is structured, stored, and protected in your databases. In this comprehensive guide, I'll walk you through each concept using practical examples from the WideWorldImporters sample database, helping you understand not just what these elements are, but why they matter and how to use them effectively.

As a SQL developer, you'll spend significant time designing tables, choosing appropriate data types, and implementing constraints. Getting these fundamentals right from the start prevents data quality issues, improves performance, and makes your databases easier to maintain. Let's dive into each concept with real-world examples you can practice immediately.

What Are Database Tables?

A table is the fundamental structure for storing data in SQL Server. Think of a table as a spreadsheet where each column represents a specific piece of information (like a customer's name or phone number), and each row represents a complete record (like one customer's complete information).

In the WideWorldImporters database, the Sales.Customers table stores customer information. Let's examine its structure:

-- View the structure of the Customers table
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Sales' 
    AND TABLE_NAME = 'Customers'
ORDER BY ORDINAL_POSITION;

This query reveals how the table is organized: each column has a name, a data type, and rules about whether it can be empty (nullable) or not.

Developer Insight: I always start database exploration by examining table structures. Understanding what columns exist, their data types, and their constraints tells you immediately what kind of data you're working with and what rules govern it.

Tables Organize Related Data

Tables are organized into schemas (like Sales, Warehouse, or Purchasing), which group related tables together. This organization makes databases easier to navigate:

-- View some key WideWorldImporters tables
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA IN ('Sales', 'Warehouse', 'Purchasing')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Understanding SQL Server Data Types

Data types define what kind of information a column can store. Choosing the right data type is crucial—it affects storage space, query performance, and data accuracy. SQL Server offers many data types, but let's focus on the most commonly used ones.

Numeric Data Types

INT: Stores whole numbers from -2,147,483,648 to 2,147,483,647. Perfect for IDs, quantities, and counts.

-- Example: OrderID and CustomerID are INT types
SELECT 
    OrderID,        -- INT
    CustomerID,     -- INT
    OrderDate
FROM Sales.Orders
WHERE OrderID = 1;

BIGINT: For larger whole numbers beyond INT's range. Used when you expect very large values.

DECIMAL(p,s): Stores exact numeric values with specified precision. Essential for money and measurements where accuracy matters.

-- Example: Prices use DECIMAL for exact values
SELECT 
    StockItemID,
    StockItemName,
    UnitPrice,              -- DECIMAL(18,2)
    RecommendedRetailPrice  -- DECIMAL(18,2)
FROM Warehouse.StockItems
WHERE StockItemID = 1;

Developer Insight: Always use DECIMAL for monetary values, never FLOAT or REAL. Floating-point types cause rounding errors in financial calculations. I learned this the hard way early in my career when cents mysteriously disappeared from invoices!

Text Data Types

VARCHAR(n): Variable-length character data up to n characters. Only uses space for actual characters stored.

NVARCHAR(n): Like VARCHAR but supports Unicode characters (international characters, emojis). The "N" stands for "National."

-- Example: Text fields in Customers table
SELECT 
    CustomerName,           -- NVARCHAR(100)
    PhoneNumber,            -- NVARCHAR(20)
    WebsiteURL,             -- NVARCHAR(256)
    DeliveryAddressLine1    -- NVARCHAR(60)
FROM Sales.Customers
WHERE CustomerID = 1;

CHAR(n): Fixed-length character data. Always uses n characters of storage, padding with spaces if needed.

Developer Insight: Use VARCHAR/NVARCHAR for most text columns. Use CHAR only for fixed-length codes like US state abbreviations (always 2 characters). VARCHAR saves storage space when data length varies, which is almost always the case.

Date and Time Data Types

DATE: Stores just the date (year, month, day) without time.

DATETIME2: Stores both date and time with high precision. This is the preferred type for modern SQL Server development.

DATETIME: Legacy date/time type. Still widely used but DATETIME2 is better for new development.

-- Example: Date fields in Orders table
SELECT 
    OrderID,
    OrderDate,              -- DATE
    ExpectedDeliveryDate,   -- DATE
    LastEditedWhen          -- DATETIME2(7)
FROM Sales.Orders
WHERE CustomerID = 1
ORDER BY OrderDate DESC;

Binary and Special Types

BIT: Stores true/false values (1 or 0). Perfect for flags and boolean logic.

-- Example: Boolean flags
SELECT 
    StockItemID,
    StockItemName,
    IsChillerStock,     -- BIT (0 or 1)
    IsSaleUnit          -- BIT (0 or 1)
FROM Warehouse.StockItems
WHERE IsChillerStock = 1;

UNIQUEIDENTIFIER: Stores globally unique identifiers (GUIDs). Used when you need guaranteed uniqueness across databases.

Creating Tables with Proper Data Types

Let's create a simple example table to understand syntax. While we can't modify WideWorldImporters, I'll show you how tables are created:

-- Example: Creating a simple product review table
CREATE TABLE Sales.ProductReviews
(
    ReviewID INT NOT NULL,
    StockItemID INT NOT NULL,
    CustomerID INT NOT NULL,
    ReviewDate DATETIME2 NOT NULL,
    Rating TINYINT NOT NULL,
    ReviewText NVARCHAR(1000) NULL,
    IsVerifiedPurchase BIT NOT NULL,
    HelpfulCount INT NOT NULL
);

This creates a table where each column has:

  • A name (ReviewID, StockItemID, etc.)
  • A data type (INT, DATETIME2, NVARCHAR, etc.)
  • A nullability rule (NOT NULL means required, NULL means optional)

Understanding Constraints

Constraints are rules that enforce data integrity. They prevent invalid data from entering your tables, maintaining accuracy and consistency. SQL Server supports several types of constraints, each serving a specific purpose.

PRIMARY KEY Constraint

The PRIMARY KEY uniquely identifies each row in a table. Every table should have a primary key.

-- View primary key for Customers table
SELECT 
    CustomerID,
    CustomerName
FROM Sales.Customers
WHERE CustomerID IN (1, 2, 3);

In this table, CustomerID is the primary key. Each customer has a unique ID, and no two customers can share the same ID.

Rules for Primary Keys:

  • Must contain unique values
  • Cannot contain NULL values
  • Each table should have exactly one primary key
  • Often uses an INT with IDENTITY for auto-incrementing values

Developer Insight: Primary keys are non-negotiable. Every table needs one. I typically use an INT IDENTITY column named TableNameID (like CustomerID, OrderID) as the primary key. It's simple, efficient, and follows SQL Server conventions.

FOREIGN KEY Constraint

Foreign keys create relationships between tables. They ensure referential integrity—meaning child records must reference valid parent records.

-- Orders table has a foreign key to Customers
SELECT 
    o.OrderID,
    o.CustomerID,      -- Foreign key
    c.CustomerName,
    o.OrderDate
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID <= 5;

The CustomerID in the Orders table is a foreign key that references the CustomerID in the Customers table. You cannot create an order for a customer that doesn't exist.

-- OrderLines references both Orders and StockItems
SELECT 
    ol.OrderLineID,
    ol.OrderID,         -- Foreign key to Sales.Orders
    ol.StockItemID,     -- Foreign key to Warehouse.StockItems
    si.StockItemName,
    ol.Quantity,
    ol.UnitPrice
FROM Sales.OrderLines ol
INNER JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
WHERE ol.OrderID = 1;

Developer Insight: Foreign keys are your database's safety net. They prevent orphaned records—like an order line pointing to a deleted product. Always define foreign keys between related tables. The short-term inconvenience of occasional constraint violations is far better than long-term data corruption.

UNIQUE Constraint

The UNIQUE constraint ensures all values in a column (or combination of columns) are different.

-- CustomerName might have a unique constraint to prevent duplicates
-- (Note: In WideWorldImporters it doesn't, but this shows the concept)
SELECT 
    CustomerID,
    CustomerName,
    PhoneNumber
FROM Sales.Customers
WHERE CustomerName LIKE 'Tailspin Toys%';

NOT NULL Constraint

NOT NULL ensures a column must always have a value. It prevents missing critical data.

-- Critical columns like CustomerName are NOT NULL
-- This query will show all customers have names (no NULLs)
SELECT 
    CustomerID,
    CustomerName,       -- NOT NULL
    PhoneNumber,        -- NOT NULL
    WebsiteURL          -- NULL allowed
FROM Sales.Customers
WHERE CustomerID <= 5;

Developer Insight: Make columns NOT NULL by default unless they genuinely can be empty. Ask yourself: "Does this piece of information make sense without a value?" For example, every customer needs a name (NOT NULL), but not every customer needs a fax number (NULL allowed).

CHECK Constraint

CHECK constraints validate data against specific conditions. They're like business rules enforced at the database level.

-- Example: Quantity must be positive
SELECT 
    OrderLineID,
    OrderID,
    Quantity,       -- Likely has CHECK (Quantity > 0)
    UnitPrice       -- Likely has CHECK (UnitPrice >= 0)
FROM Sales.OrderLines
WHERE OrderID = 1;

Common CHECK constraint examples:

  • CHECK (Quantity > 0) - Quantity must be positive
  • CHECK (UnitPrice >= 0) - Price cannot be negative
  • CHECK (Rating BETWEEN 1 AND 5) - Rating must be 1-5
  • CHECK (EndDate > StartDate) - End must be after start

DEFAULT Constraint

DEFAULT constraints automatically provide values when none is specified during insertion.

-- OrderDate likely has a DEFAULT of GETDATE()
-- LastEditedWhen likely has a DEFAULT of SYSDATETIME()
SELECT 
    OrderID,
    OrderDate,          -- DEFAULT GETDATE()
    LastEditedWhen      -- DEFAULT SYSDATETIME()
FROM Sales.Orders
WHERE OrderID = 1;

Viewing Table Constraints

You can query SQL Server's system views to see all constraints on a table:

-- View all constraints on the Customers table
SELECT 
    tc.CONSTRAINT_NAME,
    tc.CONSTRAINT_TYPE,
    kcu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.TABLE_SCHEMA = 'Sales'
    AND tc.TABLE_NAME = 'Customers'
ORDER BY tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME;

This shows primary keys, foreign keys, unique constraints, and other rules protecting data in the Customers table.

Practical Example: Analyzing Warehouse Tables

Let's combine everything we've learned to analyze the Warehouse schema:

-- Examine StockItems table structure and data
SELECT 
    StockItemID,            -- INT, PRIMARY KEY
    StockItemName,          -- NVARCHAR(100), NOT NULL
    SupplierID,             -- INT, FOREIGN KEY to Purchasing.Suppliers
    ColorID,                -- INT, FOREIGN KEY to Warehouse.Colors
    UnitPrice,              -- DECIMAL(18,2), NOT NULL
    RecommendedRetailPrice, -- DECIMAL(18,2), NOT NULL
    TypicalWeightPerUnit,   -- DECIMAL(18,3), NOT NULL
    IsChillerStock,         -- BIT, NOT NULL, DEFAULT 0
    LeadTimeDays            -- INT, NOT NULL
FROM Warehouse.StockItems
WHERE SupplierID = 4
ORDER BY StockItemName;

-- Check inventory levels with constraints in mind
SELECT 
    si.StockItemName,
    sih.QuantityOnHand,     -- INT, NOT NULL
    sih.BinLocation,        -- NVARCHAR(20), NOT NULL
    sih.LastStocktakeQuantity, -- INT, NOT NULL
    sih.ReorderLevel        -- INT, NOT NULL
FROM Warehouse.StockItems si
INNER JOIN Warehouse.StockItemHoldings sih 
    ON si.StockItemID = sih.StockItemID
WHERE sih.QuantityOnHand < sih.ReorderLevel
ORDER BY sih.QuantityOnHand;

Common Mistakes to Avoid

Mistake 1: Using Wrong Data Types

Problem: Using VARCHAR for numbers or FLOAT for money.

-- WRONG: Storing prices as VARCHAR
-- PhoneNumber VARCHAR(20)  -- Correct - it's text with special chars
-- UnitPrice VARCHAR(20)    -- WRONG - should be DECIMAL

-- CORRECT:
-- UnitPrice DECIMAL(18,2)

Developer Insight: Data types aren't just about storage—they affect how data can be used. Storing numbers as text prevents mathematical operations and leads to sorting issues where "10" comes before "2" alphabetically.

Mistake 2: Making Everything Nullable

Problem: Allowing NULL everywhere makes data unreliable.

-- BAD: Everything nullable
CREATE TABLE BadExample
(
    CustomerID INT NULL,        -- Should be NOT NULL (primary key)
    CustomerName VARCHAR(100) NULL,  -- Should be NOT NULL (required)
    OrderDate DATETIME NULL     -- Should be NOT NULL (required)
);

-- GOOD: Appropriate nullability
CREATE TABLE GoodExample
(
    CustomerID INT NOT NULL PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    MiddleName VARCHAR(50) NULL,    -- Truly optional
    OrderDate DATETIME NOT NULL
);

Mistake 3: Skipping Foreign Keys

Problem: Not defining foreign keys allows invalid relationships.

Without foreign keys, you could have:

  • Orders for customers that don't exist
  • Order lines for products that were deleted
  • Purchase orders for invalid suppliers

Always define foreign keys between related tables. The constraint validation overhead is minimal compared to the data integrity benefits.

Mistake 4: Choosing Wrong String Lengths

Problem: VARCHAR(MAX) for everything or too-short limits.

-- WASTEFUL: Using MAX unnecessarily
-- EmailAddress VARCHAR(MAX)  -- Overkill, use VARCHAR(256)

-- TOO RESTRICTIVE: Not enough space
-- CustomerName VARCHAR(20)   -- Too short, use VARCHAR(100)

-- APPROPRIATE:
-- EmailAddress VARCHAR(256)   -- Reasonable maximum
-- CustomerName VARCHAR(100)   -- Accommodates most names
-- Description VARCHAR(MAX)    -- Large text truly needs it

Mistake 5: Ignoring Check Constraints

Problem: Not validating data at the database level.

Add CHECK constraints for business rules:

  • Quantities must be positive
  • Percentages must be 0-100
  • Dates must be logical (end after start)
  • Ratings must be within valid range

Developer Insight: I implement validation at multiple levels—database constraints, application logic, and UI validation. Database constraints are your last line of defense against bad data. They protect data integrity even when application code has bugs.

Best Practices for Tables, Data Types, and Constraints

Choose Appropriate Data Types

  • Use INT for most ID columns and whole numbers
  • Use DECIMAL for money and precise calculations
  • Use VARCHAR for variable-length text (most common)
  • Use NVARCHAR when you need Unicode support
  • Use BIT for true/false flags
  • Use DATETIME2 for modern date/time needs

Design Tables Thoughtfully

  • Every table needs a primary key
  • Use meaningful, descriptive names
  • Group related columns together
  • Keep tables normalized (avoid redundant data)

Implement Constraints Consistently

  • Define primary keys on every table
  • Create foreign keys for all relationships
  • Use NOT NULL for required columns
  • Add CHECK constraints for business rules
  • Use UNIQUE where duplicates aren't allowed

Document Your Design

Add extended properties to document tables and columns:

-- Add descriptions to help future developers
EXEC sp_addextendedproperty 
    @name = N'Description',
    @value = N'Stores customer information including delivery details',
    @level0type = N'SCHEMA', @level0name = 'Sales',
    @level1type = N'TABLE', @level1name = 'Customers';

Summary and Key Takeaways

Understanding tables, data types, and constraints is fundamental to SQL Server development. Here's what you need to remember:

Tables are the structures that hold your data, organized into rows and columns. Each column has a specific data type and set of rules.

Data Types define what kind of information each column stores:

  • Use INT for whole numbers and IDs
  • Use DECIMAL for precise numeric values like money
  • Use VARCHAR/NVARCHAR for text
  • Use DATETIME2 for dates and times
  • Use BIT for true/false values

Constraints enforce data integrity:

  • PRIMARY KEY uniquely identifies each row
  • FOREIGN KEY maintains relationships between tables
  • NOT NULL prevents missing critical data
  • UNIQUE prevents duplicate values
  • CHECK validates data against business rules
  • DEFAULT provides automatic values

Developer Insight: These three concepts work together to create reliable databases. The WideWorldImporters database demonstrates professional database design—study its structure to see these principles in action. Every table has a primary key, relationships use foreign keys, critical columns are NOT NULL, and data types match their purposes.

Next Steps: Practice examining tables in WideWorldImporters. Use the INFORMATION_SCHEMA views to explore table structures, constraints, and data types. Try creating your own test tables with appropriate data types and constraints. The more you work with these concepts, the more natural good database design becomes.

Frequently Asked Questions

Q: When should I use VARCHAR versus NVARCHAR?

A: Use NVARCHAR when you need to store international characters, emojis, or any Unicode data. NVARCHAR uses twice the storage of VARCHAR (2 bytes per character vs 1 byte), so use VARCHAR for English-only text to save space. In the WideWorldImporters database, most text columns use NVARCHAR because businesses often need international character support. If unsure, use NVARCHAR—storage is cheap, but fixing encoding issues later is expensive.

Q: Can a table have multiple primary keys?

A: No, each table can have only ONE primary key. However, that primary key can be composite (made up of multiple columns). For example, the OrderLines table uses both OrderID and OrderLineID together as a composite primary key. In practice, I recommend using a single-column surrogate key (like an INT IDENTITY) as the primary key for simplicity, even if you have natural composite keys.

Q: What happens if I try to insert data that violates a constraint?

A: SQL Server rejects the insert and returns an error message explaining which constraint was violated. For example, if you try to insert a duplicate primary key, an order for a non-existent customer (foreign key), or a negative price when there's a CHECK constraint, the operation fails and no data is changed. This is exactly what you want—constraints prevent bad data from corrupting your database.

Tags: