SQL Server / Basic / Lesson 2

Schemas, data types, and keys

Model a small order system using explicit types, Unicode text, and stable identifiers. Understand which guarantees come from constraints and which must be designed separately.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Choose precise SQL Server data types.
Distinguish schema names, keys, and identity generation.
Explain NULL and Unicode storage decisions.

Name objects and define row meaning

A schema is a namespace and permission boundary inside a database. sales.OrderHeader and archive.OrderHeader can coexist because their schema names differ. Use two-part names in database code to make ownership and intent clear. A schema is not a separate database or a guarantee of physical storage separation. Before choosing columns, write one sentence defining what a row represents.

For a training shop, one customer row represents one customer account, one order row represents one order event, and one order-line row represents one product occurrence within that order. Repeating customer names on every line invites inconsistent edits. Separate entities and reference them with keys. Normalization starts with facts and dependencies, not with splitting every long table arbitrarily.

Choose types from domain rules

Use int for identifiers when its range meets the lifecycle requirement, decimal(p,s) for exact amounts, date for calendar dates, and datetime2 for timestamps requiring fractional precision. float represents approximate values and can surprise equality comparisons on prices. Choose a decimal scale from currency and calculation rules; rounding at an agreed boundary is different from silently losing precision in storage.

The example uses a table variable so the shape and data are visible together. Its one row has an exact amount and an Arabic name. datetime2 stores a date and time without a time-zone offset. If the system standardizes on UTC, document that convention; use datetimeoffset when retaining an offset is part of the requirement. Neither type automatically knows a customer's geographic time zone.

sql A precisely typed customer row

DECLARE @Customer TABLE (
 CustomerId int PRIMARY KEY,
 DisplayName nvarchar(80) NOT NULL,
 CreditLimit decimal(10,2) NOT NULL,
 RegisteredOn date NOT NULL
);
INSERT @Customer VALUES (1, N'ليلى', 125.50, '20260115');
SELECT CustomerId, DisplayName, CreditLimit FROM @Customer;

Expected output

CustomerId DisplayName CreditLimit
1          ليلى        125.50

The literal date uses an unambiguous compact form. The Unicode literal and column preserve the Arabic text.

Preserve multilingual text deliberately

nvarchar is the course default for multilingual names; prefix Unicode string literals with N. Its declared n measures UTF-16 byte-pairs, not an unconditional count of visible characters: supplementary characters can need two pairs. Always specify lengths in declarations and conversions. A narrow intermediate variable can discard text before an INSERT ever reaches a wider destination.

SQL Server 2019 also supports Unicode in varchar under suitable UTF-8 collations, but that is an explicit collation and storage decision. Do not generalize that every varchar column stores all scripts safely. Collation additionally influences comparisons and sorting, including case and accent sensitivity. Validate expected names and search behavior using representative Arabic and Latin text, rather than testing only ASCII identifiers.

Separate identity generation from uniqueness

A primary key requires unique, non-null key values; a foreign key prevents references to missing parent values. IDENTITY generates numeric values but is not itself a uniqueness constraint or a promise of gap-free numbering. Failed inserts, caching, and deletions can leave gaps. Business invoice numbering may need a separate, explicitly designed process.

The second example demonstrates a composite key: the pair identifies a line, although each OrderId repeats. A foreign key in a permanent design would connect OrderId to the order header. Define one declared primary key per table and additional UNIQUE constraints for independent business identifiers such as an external order reference. A foreign key does not automatically create an index on its referencing columns in SQL Server.

sql A composite line identifier

DECLARE @Line TABLE (
 OrderId int NOT NULL, LineNo int NOT NULL,
 Quantity int NOT NULL,
 PRIMARY KEY (OrderId, LineNo)
);
INSERT @Line VALUES (10,1,2),(10,2,1),(11,1,3);
SELECT OrderId, LineNo, Quantity FROM @Line
ORDER BY OrderId, LineNo;

Expected output

OrderId LineNo Quantity
10      1      2
10      2      1
11      1      3

Repeating OrderId is valid because the combined key remains unique. Repeating the entire pair would violate the primary key.

Represent missing information honestly

NULL means missing or unknown information, not zero, an empty string, or a special date. Decide whether each attribute can be unknown. An optional delivery date can be NULL before scheduling; a required quantity should be NOT NULL with an appropriate positive-value rule. Database defaults supply omitted values but do not explain the business meaning of an explicit NULL.

Document units and valid ranges next to the schema: quantity in items, amount in a stated currency, and time in a stated convention. A type alone cannot enforce all these meanings. Constraints protect stable row rules; application validation provides helpful feedback. Both layers should agree so another import or service cannot bypass the essential integrity rule.

Common errors and fixes

String or binary data would be truncated.

Why it happens: A value exceeds a declared destination length.
How to fix it: Check input, parameter, intermediate variable, and column sizes; enlarge only where the domain requires it.
Prevent it next time: Specify lengths and test multilingual boundary cases.

Violation of PRIMARY KEY constraint.

Why it happens: The complete key repeats an existing row.
How to fix it: Correct the identifier or reject the duplicate according to business rules.
Prevent it next time: Design stable keys and test duplicate submissions.

Cannot insert the value NULL into column.

Why it happens: A required value was omitted or explicitly supplied as NULL.
How to fix it: Supply a valid value or revisit nullability based on actual requirements.
Prevent it next time: Distinguish optional facts from required facts in the model.

Check your understanding

1. Would IDENTITY alone protect an external order reference from duplicates?

Show worked solution

No. IDENTITY generates an internal number. Put a UNIQUE constraint on the external reference, define whether it is required, and decide how a duplicate request is reported or reused.

2. Model an unknown shipping date and a price of 19.95.