Database Tables  «Prev  Next»

Lesson 9 Creating SQL-Server Tables
Objective Identify key considerations for creating a table

Key Considerations for Creating a Table in SQL Server 2025

Before writing an actual CREATE TABLE statement, a handful of design decisions are worth thinking through first. What data types belong on each column, how the table will be uniquely identified, whether it's properly normalized, how it relates to other tables, what it should be named, how it will be indexed, who can create it, and what rules should keep bad data out of it from the start. Getting these right up front is a lot cheaper than correcting them after a table is already in production with real rows in it, foreign keys pointing at it, and reports built on top of it. This lesson surveys those considerations one at a time; the next lesson walks through the Transact-SQL syntax that actually puts them into practice.

Choosing the Right Data Type

Picking a data type isn't just a matter of what technically fits the data; it's about matching the type to the real-world domain a column represents, without reaching for more storage or more range than that data will ever plausibly need. A concrete example makes this easier to see. Suppose a table needs to store an employee's annual salary, a value that never exceeds $200,000 and needs exact decimal precision; a salary of $75,500.50 stored and later retrieved shouldn't come back as $75,500.4999 because of a rounding quirk in the underlying type. A few things are worth working through before picking a type for a column like this:
  • Precision and scale. The column has to handle decimal values cleanly, not just whole dollars.
  • Range of values. The largest value this column will ever hold, in this case $200,000, sets a ceiling that rules out types that are either too small or needlessly large.
  • Storage efficiency. A type should be sufficient for the data without being excessive; extra bytes spent on a type with far more range than the column will ever use add up across a table with millions of rows.

MONEY, SMALLMONEY, or DECIMAL?

At first glance, SQL Server's MONEY type looks like the obvious fit for a column that stores currency. It's worth checking its actual footprint before settling on it, though. MONEY takes 8 bytes of storage per value and covers a range of

-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Unless the column is tracking national deficit figures, it doesn't need anywhere near that much range, and it doesn't need to spend 8 bytes per row to get it. SMALLMONEY, by contrast, takes only 4 bytes of storage and covers a range of

-214,748.3648 to 214,748.3647
which comfortably covers the $200,000 ceiling this column needs, at half the storage cost. Between the two, SMALLMONEY is the more sensible choice for this specific column, and the pattern behind that choice is worth carrying forward to other columns too: pick the narrowest type that comfortably covers the actual range of values a column will hold, not whichever type sounds most appropriate by name. DECIMAL(9,2) is the other strong candidate here, and it's favored for a different reason: it guarantees exact precision for monetary values. FLOAT and REAL, by contrast, are approximate types. They can introduce small rounding errors that are perfectly tolerable in a scientific calculation but genuinely problematic in a financial one, where a few cents of drift across millions of transactions is not an acceptable outcome. The lesson on data types earlier in this course already covered precision, scale, and length in more depth; this salary column is exactly where that groundwork pays off in a concrete decision. Either of these two approaches works for a SQL Server table storing this kind of value:

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Salary SMALLMONEY CHECK (Salary <= 200000)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Salary DECIMAL(9,2) CHECK (Salary <= 200000)
);
The choice between them comes down to whether exact decimal precision (DECIMAL) or a smaller, currency-specific type with a known, fixed range (SMALLMONEY) matters more for the column in question; either is a defensible answer for this particular salary column, and both are a better fit than reaching for MONEY out of habit or FLOAT out of unfamiliarity with the alternatives.

The Same Habit Applies to Text Columns

The numeric example above isn't a special case; the same discipline applies just as directly to string columns, which is where it's actually more commonly skipped. NVARCHAR(MAX) is a tempting default for a text column because it simply never runs out of room, but that convenience comes at a real cost: it can't be included in certain index key definitions, and SQL Server has to treat a column defined that way differently from an ordinary bounded string when deciding how to store and optimize it. A column holding a last name, a product code, or a US state abbreviation has a realistic maximum length that's usually easy to state with confidence, and a type sized to that real maximum, VARCHAR(50) rather than NVARCHAR(MAX) for a last name, say, keeps the column indexable without constraints and keeps its storage proportional to what it actually holds. The lesson on specifying length, precision, and scale earlier in this course covers the mechanics of choosing that bound; the point here is simply that the habit of sizing a type to the data, not to the largest type available, applies across every column in the table, not only the numeric ones.

Defining a Primary Key

Every table needs a stable, unique way to identify each row. Where a natural key, something already present in the data itself, isn't reliably stable or guaranteed unique, a surrogate key generated with IDENTITY or a SEQUENCE, both covered earlier in this course, is the more durable choice. A good primary key stays narrow and immutable: narrow, because it's copied into every foreign key column in every table that references it, and immutable, because changing a primary key value after the fact cascades into every one of those referencing rows.

Composite Primary Keys

Some tables need more than one column working together to uniquely identify a row; that combination is called a composite primary key. Defining one uses a CONSTRAINT clause placed after the last column definition, which names the constraint and lists every column it comprises:

CONSTRAINT pkey PRIMARY KEY (Column1, Column2, Column3)
The constraint needs a name that's unique within the table, and every column listed inside the parentheses becomes part of the key together; no single one of them has to be unique on its own, only the combination of all of them does. The next lesson shows this clause in the context of a complete, working CREATE TABLE statement. A composite primary key shows up most naturally in a junction table that resolves a many-to-many relationship. An OrderDetails table connecting orders to the products on each order, for instance, typically has no single column that's meaningfully unique on its own; OrderID repeats across every product on the same order, and ProductID repeats across every order that includes that product. The combination of the two together, though, is unique: one order can't list the same product twice as two separate rows. That's exactly the situation a composite primary key on (OrderID, ProductID) is built for, and it's a pattern worth recognizing on sight, since junction tables of this shape come up constantly in relational design.

Normalizing First, Denormalizing Deliberately

Normalization, covered in depth earlier in this course, reduces redundancy and keeps the same fact from being stored, and potentially contradicted, in more than one place in the database. The general design order worth following is to normalize a table structure first, and only denormalize specific, deliberate spots afterward where a measured performance problem actually justifies the tradeoff. Denormalizing by default, in order to save a join that hasn't actually been shown to be a bottleneck, tends to quietly recreate the exact redundancy normalization was meant to remove in the first place, just earlier in the design process than it should have appeared.

Foreign Keys for Relationships

Foreign keys, also covered earlier in this course, enforce referential integrity between related tables and prevent orphaned rows, meaning a row in a child table that points at a parent row that no longer exists. Deciding which tables relate to which, and which side of each relationship owns it, is a design question worth settling at this stage, before the tables exist, rather than retrofitted onto a schema that's already in use. A foreign key added after the fact to a table already full of data that violates the relationship it's supposed to enforce will simply fail to create until that data is cleaned up first. The junction table from the previous section is a good illustration of this in practice: OrderDetails.OrderID would carry a foreign key back to Orders.OrderID, and OrderDetails.ProductID a separate foreign key back to Products.ProductID. Both relationships get enforced automatically from the moment the table exists, rather than depending on every piece of application code that ever inserts a row to remember to check both tables first.

Naming Conventions

Consistent naming reduces ambiguity across a schema with dozens, or eventually hundreds, of tables. One common convention favors singular table names, Employee rather than Employees, on the reasoning that a table describes one instance of a thing and a row is one occurrence of that thing. This isn't a universal rule, though; plenty of well-regarded schemas, including some of Microsoft's own sample databases, use plural or mixed naming instead without any real harm coming of it. What matters more than which specific convention gets chosen is applying it consistently across every table in the same database, so that naming never becomes a guessing game for whoever is writing queries against the schema months or years later.

Planning an Indexing Strategy Early

Indexes have an outsized effect on query performance, and it's considerably easier to plan them alongside the table design itself than to retrofit them onto a table that's already live and already slow. A clustered index, often built on the primary key, determines the physical order rows are actually stored in on disk; nonclustered indexes added on top of that should target the specific columns real queries filter or join on, not every column that might conceivably be searched by someone, someday. Over-indexing carries a real cost of its own that's easy to underestimate: every additional index has to be maintained on every insert, update, and delete issued against the table, so each one needs to earn its place rather than being added defensively just in case. Once a table has been in use for a while, the sys.indexes catalog view, in the same family of catalog views covered in the earlier lesson on filegroups, lists every index actually defined on it:

SELECT name, type_desc, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Employees')
Checking this periodically against what the table's actual query patterns look like is a reasonable way to catch an index that was added early on and never actually earned its keep, as well as a query pattern that's run constantly but has no supporting index at all.

Permissions Required to Create a Table

Creating a table requires the CREATE TABLE permission in the database, together with the ALTER permission on the schema the table is being created in. Two narrower cases add their own requirement on top of that: a column defined with a CLR user-defined type needs either ownership of that type or REFERENCES permission on it, and a column with an associated XML schema collection needs the same with respect to that collection. Neither of those two narrower cases applies to an ordinary table built entirely from standard SQL Server data types like the ones discussed above, which covers the overwhelming majority of tables most databases actually contain.

Constraints and Validation Rules

Constraints enforce data quality at the database level itself, rather than relying entirely on application code to catch every bad value before it gets written. A CHECK constraint restricts the values a column will accept, the way the salary examples above use CHECK (Salary <= 200000) to enforce that specific business rule directly in the schema, so it holds regardless of which application or script happens to be the one inserting the row. A UNIQUE constraint prevents duplicate values in a column that isn't the primary key but still needs to stay distinct, an employee's badge number, say, alongside an IDENTITY-based primary key that has nothing to do with it. A DEFAULT constraint supplies a value automatically whenever one isn't explicitly provided on insert. Each of these catches an entire category of bad data before it ever reaches a row, rather than discovering it later during a report or an audit. All three are cheap to add at the point a table is created and considerably more disruptive to retrofit afterward, since adding a CHECK or UNIQUE constraint to a table that already contains rows violating it will fail outright until those rows are found and corrected first.

Planning for Future Growth

A table designed comfortably for today's data volume can start to strain under tomorrow's, especially once row counts climb into the millions and keep climbing. Worth thinking about at design time, not retrofitted under pressure later: the row volume actually expected over the table's realistic lifetime, whether a partitioning strategy will eventually be needed to keep a very large table manageable, and whether the indexing strategy chosen earlier will still hold up once the table is substantially larger than it is on the day it's first created. None of this has to be solved perfectly up front, but at least having thought about it once avoids being caught completely by surprise when the table outgrows its original assumptions. A transaction log table expected to hold a few thousand rows a month behaves very differently once it's holding tens of millions of rows a year, and a design that never considered that possibility tends to need far more invasive surgery later than one that at least left room for a partitioning scheme or an archiving strategy to be added without a full redesign.
Data types, keys, normalization, relationships, naming, indexing, permissions, and constraints all feed into the same end goal: a table that's efficient to store, correct to query, and reasonably maintainable once it's carrying real data and other objects depend on it. None of these considerations require a perfect answer before moving forward, only a deliberate one. The next lesson walks through the Transact-SQL CREATE TABLE statement itself, including how a composite primary key constraint like the one shown above fits into a complete table definition.

SEMrush Software 9 SEMrush Banner 9