Data types, nullability, and business-rule enforcement

Overview

Data types, nullability, and business-rule enforcement define what kind of data a relational database will accept. They are not just storage details. They are part of the contract between the database, application code, reporting queries, integrations, and future maintainers.

Choosing the right data type affects correctness, storage, indexing, sorting, comparisons, rounding, date handling, string behavior, and performance. Choosing nullability defines whether a value is required, optional, unknown, not applicable, or not yet captured. Enforcing business rules with constraints protects the database when data is written by APIs, background jobs, imports, scripts, and multiple applications.

This topic matters in SQL interviews because it tests whether a candidate thinks beyond SELECT syntax. A strong candidate can explain why DECIMAL is safer than FLOAT for money, why NOT NULL matters, why CHECK constraints are useful, why nullable columns affect filtering, and which rules belong in the database versus application code.

The practical goal is to model the domain accurately, reject invalid data early, and keep the database trustworthy even when application code has bugs.

Core Concepts

Data Types Are a Contract

A data type defines the shape of values a column can store.

CREATE TABLE Products
(
    ProductId BIGINT NOT NULL,
    Sku NVARCHAR(50) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(19, 4) NOT NULL,
    IsActive BIT NOT NULL,
    CreatedAtUtc DATETIME2(3) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT UQ_Products_Sku UNIQUE (Sku),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0)
);

This schema says more than "there is a products table." It says:

• Product IDs are required.
• SKUs are Unicode strings with a bounded length.
• Price is exact numeric data.
• Active state is required.
• Creation time is stored with a clear type and precision.
• Negative prices are invalid.

Good schema design uses types to make invalid data hard to store.

Exact vs Approximate Numerics

Use exact numeric types for values where exactness matters:

• Counts.
• Quantities.
• Money.
• Tax rates.
• Percentages that must round predictably.
• Inventory levels.

Price DECIMAL(19, 4) NOT NULL
TaxRate DECIMAL(9, 6) NOT NULL
Quantity INT NOT NULL

Avoid approximate types such as FLOAT and REAL for money or business totals. They are useful for scientific measurements or approximate calculations, but they can produce surprising rounding behavior.

Bad:

Price FLOAT NOT NULL

Better:

Price DECIMAL(19, 4) NOT NULL

Precision and Scale

DECIMAL(p, s) and NUMERIC(p, s) store exact numbers. Precision is the total number of digits. Scale is the number of digits after the decimal point.

Amount DECIMAL(19, 4) NOT NULL

This supports 19 total digits with 4 digits after the decimal point.

Common mistakes:

• Choosing too little precision and causing overflow.
• Choosing too little scale and losing fractional values.
• Choosing excessive precision everywhere and increasing storage or index size.
• Using money-like values without a clear rounding strategy.

The data type should match both the domain and the calculations.

Date and Time Types

Use date and time types that match the meaning of the value:

• DATE for calendar dates without time.
• TIME for time of day.
• DATETIME2 for precise date and time values.
• DATETIMEOFFSET when the offset is part of the stored value.

Example:

CREATE TABLE Events
(
    EventId BIGINT NOT NULL PRIMARY KEY,
    EventDate DATE NOT NULL,
    StartsAtUtc DATETIME2(3) NOT NULL,
    SourceOffset DATETIMEOFFSET(3) NULL
);

Store instants consistently, often in UTC, and use naming that makes the convention visible, such as CreatedAtUtc.

Avoid storing dates as strings:

CreatedDate NVARCHAR(30) NOT NULL

String dates are harder to validate, sort, filter, index, and compare correctly.

String Types and Lengths

In SQL Server, common string choices include:

• VARCHAR(n) for non-Unicode text where the character set is known.
• NVARCHAR(n) for Unicode text.
• CHAR(n) or NCHAR(n) for fixed-length values.
• VARCHAR(MAX) or NVARCHAR(MAX) for large text when truly needed.

Use explicit lengths:

Email NVARCHAR(320) NOT NULL
Name NVARCHAR(200) NOT NULL
Status NVARCHAR(30) NOT NULL

Avoid NVARCHAR(MAX) for every string. It weakens the domain model, can increase memory grants or storage complexity, and makes indexing harder.

For codes and statuses, consider whether a lookup table, check constraint, or application enum plus database constraint is appropriate.

Boolean and Status Values

SQL Server uses BIT for Boolean-like values.

IsActive BIT NOT NULL

Be careful with nullable Boolean columns. NULL, 0, and 1 can create three states. Sometimes that is correct, such as "not answered yet." Often it is accidental complexity.

Bad:

IsDeleted BIT NULL

Better:

IsDeleted BIT NOT NULL
    CONSTRAINT DF_Users_IsDeleted DEFAULT 0

For multi-state workflow values, a status column with a check constraint or reference table is usually clearer.

Status NVARCHAR(20) NOT NULL
    CONSTRAINT CK_Orders_Status
    CHECK (Status IN (N'Draft', N'Placed', N'Cancelled', N'Shipped'))

NULL Meaning

NULL means the database does not have a value. It can represent:

• Unknown.
• Not applicable.
• Not collected yet.
• Optional value.
• Future value.

These meanings are different. A good schema should make the intended meaning clear.

Examples:

• MiddleName NULL can mean the person has no middle name or it is unknown.
• ShippedAtUtc NULL can mean the order has not shipped yet.
• CancelledAtUtc NULL can mean the order was not cancelled.

For important business logic, a separate status can be clearer than relying only on null checks.

NOT NULL

Use NOT NULL for required values.

CREATE TABLE Customers
(
    CustomerId BIGINT NOT NULL PRIMARY KEY,
    Email NVARCHAR(320) NOT NULL,
    CreatedAtUtc DATETIME2(3) NOT NULL
);

Benefits:

• Prevents incomplete rows.
• Simplifies queries.
• Helps the optimizer reason about data.
• Documents required fields.
• Avoids accidental three-valued logic.

Be explicit about nullability. Do not rely on database defaults or session settings to communicate whether a column is required.

NULL and Three-Valued Logic

Comparisons involving NULL often evaluate to unknown, not true or false.

Bad:

SELECT *
FROM Orders
WHERE ShippedAtUtc = NULL;

Correct:

SELECT *
FROM Orders
WHERE ShippedAtUtc IS NULL;

For non-null:

SELECT *
FROM Orders
WHERE ShippedAtUtc IS NOT NULL;

Nullable columns can affect joins, filters, unique rules, aggregates, and check constraints. Interview answers should mention that NULL is not just an empty string or zero.

Defaults

Defaults provide a value when an insert omits a column.

CreatedAtUtc DATETIME2(3) NOT NULL
    CONSTRAINT DF_Orders_CreatedAtUtc DEFAULT SYSUTCDATETIME()

Defaults are useful for:

• Creation timestamps.
• Required flags with normal defaults.
• Status initial values.
• System-generated values.

Be careful: if a nullable column is explicitly inserted as NULL, a default may not apply. Also, defaults are not a replacement for business validation.

Check Constraints

CHECK constraints enforce rules about allowed values in a row.

CREATE TABLE OrderLines
(
    OrderId BIGINT NOT NULL,
    LineNumber INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(19, 4) NOT NULL,
    DiscountAmount DECIMAL(19, 4) NOT NULL
        CONSTRAINT DF_OrderLines_DiscountAmount DEFAULT 0,

    CONSTRAINT PK_OrderLines PRIMARY KEY (OrderId, LineNumber),
    CONSTRAINT CK_OrderLines_Quantity CHECK (Quantity > 0),
    CONSTRAINT CK_OrderLines_UnitPrice CHECK (UnitPrice >= 0),
    CONSTRAINT CK_OrderLines_Discount CHECK (DiscountAmount >= 0),
    CONSTRAINT CK_OrderLines_DiscountNotGreaterThanPrice
        CHECK (DiscountAmount <= UnitPrice)
);

Use check constraints for domain rules that must always be true for a row:

• Quantity must be positive.
• End date must be after start date.
• Status must be one of known values.
• Discount must be within a valid range.
• Percent must be between 0 and 100.

Unique Constraints and Unique Indexes

Use unique constraints or unique indexes to enforce candidate keys and business uniqueness.

CREATE TABLE Customers
(
    CustomerId BIGINT NOT NULL PRIMARY KEY,
    Email NVARCHAR(320) NOT NULL,
    CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);

For optional values, a filtered unique index can enforce uniqueness only when the value exists.

CREATE UNIQUE INDEX UX_Customers_Phone
ON Customers(PhoneNumber)
WHERE PhoneNumber IS NOT NULL;

This is useful when phone number is optional but no two customers should share the same non-null phone number.

Foreign Keys

Foreign keys enforce relationships between tables.

CREATE TABLE Orders
(
    OrderId BIGINT NOT NULL PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

Foreign keys prevent orphan records and help express the domain. If Orders.CustomerId is required, make it NOT NULL. If an order can exist without a customer, make the business meaning explicit and consider whether a nullable foreign key is really correct.

Business Rule Enforcement Layers

Business rules can be enforced in multiple places:

• UI validation for user feedback.
• API validation for request contracts.
• Domain/application logic for workflows.
• Database constraints for durable invariants.
• Triggers for rare cross-row or side-effect rules.
• Jobs or audits for reconciliation.

Rules that must never be violated should usually have database enforcement when possible.

Examples:

• Email must be unique.
• Quantity must be positive.
• Order status must be valid.
• Order line must reference an existing order.
• A user cannot have two active primary email rows.

The application should still validate early for good user experience, but the database should protect the data.

Rules That Need More Than a Check Constraint

Some rules cannot be expressed cleanly with a row-level check constraint:

• "Only one active subscription per customer."
• "Order total must equal the sum of its lines."
• "A booking must not overlap another booking."
• "A manager cannot approve their own expense."
• "A status transition must follow a workflow."

Possible tools:

• Unique filtered indexes.
• Foreign keys.
• Transactions and appropriate isolation.
• Stored procedures or application services.
• Triggers, used carefully.
• Periodic reconciliation queries.

Example filtered unique index:

CREATE TABLE CustomerEmails
(
    CustomerEmailId BIGINT NOT NULL PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    IsPrimary BIT NOT NULL,
    CONSTRAINT FK_CustomerEmails_Customers
        FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

CREATE UNIQUE INDEX UX_CustomerEmails_OnePrimary
ON CustomerEmails(CustomerId)
WHERE IsPrimary = 1;

This enforces at most one primary email per customer.

Sentinel Values

Avoid sentinel values when NULL or a proper status is clearer.

Bad:

ShippedAtUtc DATETIME2 NOT NULL
    CONSTRAINT DF_Orders_ShippedAtUtc DEFAULT '19000101'

Better:

ShippedAtUtc DATETIME2 NULL

Or:

Status NVARCHAR(20) NOT NULL
ShippedAtUtc DATETIME2 NULL

Sentinel values pollute queries and can be mistaken for real data. If a value is unknown or not applicable, model that truth directly.

Schema Evolution

Changing data types or nullability in production can be risky:

• Existing data may violate the new rule.
• Long-running table changes may lock data.
• Application versions may disagree during deployment.
• Backfills may need batching.
• Constraints may need to be added with validation.

Safer migration flow:

• Add nullable column or permissive rule.
• Backfill data.
• Deploy application changes.
• Validate data.
• Add NOT NULL, CHECK, or UNIQUE constraint.
• Remove old column or old rule after compatibility period.

Business-rule enforcement must consider deployment safety, not only final schema correctness.

Common Mistakes

Common mistakes include:

• Using NVARCHAR(MAX) for every string.
• Storing dates as strings.
• Using FLOAT for money.
• Making required columns nullable.
• Using NULL to mean several different things.
• Using sentinel values such as 1900-01-01.
• Forgetting unique constraints for business keys.
• Relying only on application validation.
• Writing check constraints that do not handle nullability intentionally.
• Letting status columns accept any string.
• Skipping database constraints because tests currently pass.

Best Practices

Best practices include:

• Choose the narrowest type that accurately fits the domain.
• Use exact numerics for money and counts.
• Use clear date/time types and naming conventions.
• Specify string lengths intentionally.
• Make required fields NOT NULL.
• Use CHECK constraints for row-level invariants.
• Use UNIQUE constraints or indexes for candidate keys.
• Use foreign keys for relationships.
• Use filtered indexes for conditional uniqueness.
• Keep UI validation and database constraints aligned.
• Name constraints clearly.
• Test migrations against realistic data.