| Lesson 10 | Creating tables in SQL-Server |
| Objective | Use Transact-SQL to create a basic table. |
Create a Table Using Transact-SQL in SQL Server 2025
The previous lesson covered the design decisions worth thinking through before a table actually exists: data types, keys, normalization, naming, indexing, permissions, and constraints. This lesson puts those decisions into practice with the statement that actually creates a table,
CREATE TABLE, working from the simplest possible version of the statement up to a complete table with constraints and a foreign key in place.
Basic Syntax
A simplified view ofCREATE TABLE, covering the pieces this lesson actually uses, looks like this:
CREATE TABLE table_name
( { <column_definition>
| column_name AS computed_column_expression
| <table_constraint>
} [, ...n]
)
[ON {filegroup | DEFAULT} ]
[TEXTIMAGE_ON {filegroup | DEFAULT} ]
table_nameis the name given to the new table.<column_definition>is a group of properties describing one column: its data type, whether it allowsNULL, whether it's anIDENTITYcolumn, and any constraint defined specifically on that column.column_name AS computed_column_expressiondefines a computed column, one whose value is derived from an expression involving other columns rather than stored directly.<table_constraint>is a constraint defined at the table level rather than tied to a single column, most often used for a primary key or unique constraint spanning more than one column, like the composite primary key covered in the previous lesson.ON {filegroup | DEFAULT}specifies which filegroup stores the table;ON DEFAULTplaces it on whichever filegroup is currently set as the default, the same default filegroup concept covered in the earlier lesson on filegroups.TEXTIMAGE_ON {filegroup | DEFAULT}specifies which filegroup stores the table's large object (LOB) data, columns such astext,ntext,image, or one of theMAX-length orxmltypes, separately from the rest of the table's data if desired.
column_definition and table_constraint each accept a further set of arguments, covered next.
What column_definition Accepts
{ column_name data_type }
[ NULL | NOT NULL ]
[ IDENTITY [(seed, increment )
[NOT FOR REPLICATION] ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ::=
[CONSTRAINT constraint_name]
{ { PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH [FILLFACTOR = fillfactor]
]
[ON {filegroup | DEFAULT} ]
| [FOREIGN KEY]
REFERENCES ref_table
[ ( ref_column ) ]
[NOT FOR REPLICATION]
| DEFAULT constant_expression
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}
] [ ...n]
NULL and NOT NULL in the lesson on allowing nulls, IDENTITY with its seed and increment in the lesson on identity columns, and ROWGUIDCOL in the lesson on that attribute flag. This is where they all come together as arguments to a single column definition inside an actual CREATE TABLE statement, rather than appearing one at a time in isolation.
Two pieces inside <column_constraint> are worth a closer look, since the syntax alone doesn't explain what they do. CLUSTERED and NONCLUSTERED decide what kind of index backs a PRIMARY KEY or UNIQUE constraint; a clustered index, the default for a primary key unless specified otherwise, determines the physical order the table's rows are stored in, while a nonclustered one is a separate structure pointing back at the data rather than ordering it directly, the same distinction the earlier lesson on indexing strategy touched on. FILLFACTOR controls how full each index page is left when the index is built, leaving some room for new rows to be inserted later without immediately forcing a page split; it's a tuning knob worth knowing about by name, though not something a basic table needs to set explicitly.
What table_constraint Accepts
[CONSTRAINT constraint_name]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED]
{ ( column[,...n] ) }
[ WITH [FILLFACTOR = fillfactor]
]
[ON {filegroup | DEFAULT} ]
]
| FOREIGN KEY
[(column[,...n])]
REFERENCES ref_table [(ref_column[,...n])]
[NOT FOR REPLICATION]
| CHECK [NOT FOR REPLICATION]
(search_conditions)
}
( column[,...n] ) list: a table-level constraint can name more than one column, which is exactly what a composite primary key requires. A constraint tied to a single column can be written inline as part of that column's own definition; a constraint spanning several columns has to be written at the table level instead, since no single column definition has visibility into the others.
Even a single-column constraint is sometimes better off written at the table level anyway. An inline constraint, written as part of the column definition, gets a name SQL Server generates automatically unless one is supplied right there; a table-level constraint, by contrast, makes it natural to give the constraint an explicit, meaningful name with the CONSTRAINT constraint_name clause shown above. That name is what shows up later in an error message if the constraint is ever violated, so a table of any real size tends to read more clearly with its constraints named deliberately at the table level rather than left to whatever name gets generated automatically.
A Table with One Column
At a minimum, a table needs one column and a data type for it. An employee table with nothing but a salary column could be created with:
CREATE TABLE employee (salary smallmoney)
This doesn't accomplish much on its own: a salary column exists, but nothing ties any given salary value to a specific employee, since there's no employee-identifying column to serve as a frame of reference. We'd have a list of salary figures with no way to know whose salary any one of them actually belongs to. In practice, a table almost always needs more than one column to be genuinely useful.
Adding More Columns
Building out a more complete employee table, with identifying information alongside the salary column, means adding the employee's name along with address and contact details:
CREATE TABLE employee
(
EmployeeID int,
DepartmentID int,
Salary smallmoney,
LastName varchar(30),
FirstName varchar(30),
AddressLine1 varchar(30),
AddressLine2 varchar(30),
City varchar(30),
State char(2),
Zip char(5),
HomePhone varchar(10),
HomeFax varchar(10)
)
CREATE TABLE statement like this one fails to run: SQL Server reads the two column names as though they were meant to be one, rather than as the start of a new column.
The order columns are listed in doesn't change how the table behaves; LastName listed before FirstName works identically to the reverse. It does change the default column order shown by tools like SSMS and the order columns come back in a query that uses SELECT *, so grouping related columns together, identity and key columns first, then the data columns, then address and contact details, the way this example already does, is worth doing for readability even though SQL Server itself doesn't require it.
Using the IDENTITY Column
Rather than supplying anEmployeeID value by hand for every new hire, that column can be made an IDENTITY column instead, so SQL Server assigns each new row a number automatically, incremented from the previous one. Suppose the numbering should start at 1001 rather than the usual 1:
CREATE TABLE employee
(
EmployeeID int IDENTITY(1001, 1),
DepartmentID int,
Salary smallmoney,
LastName varchar(30),
FirstName varchar(30),
AddressLine1 varchar(30),
AddressLine2 varchar(30),
City varchar(30),
State char(2),
Zip char(5),
HomePhone varchar(10),
HomeFax varchar(10)
)
1001 is the seed, the value the very first row inserted will receive; the 1 is the increment, the amount added to the previous value to generate each one after that. Once this table exists, every statement that inserts a new employee simply omits EmployeeID entirely, and SQL Server fills it in. The earlier lesson on identity columns covers this pair of arguments, and the behavior of IDENTITY more generally, including what happens if a value is skipped or a row is later deleted, in more depth than repeated here.
Bringing the Pieces Together
The examples so far have kept every column optional and left the table standing on its own, with nothing enforcing the design decisions the previous lesson actually walked through. A more complete version of the employee table puts those decisions directly into the statement itself, rather than leaving them as something to remember and enforce elsewhere. Alongside it, a smallDepartment table gives the employee table something real to reference:
CREATE TABLE Department
(
DepartmentID int IDENTITY(1,1) PRIMARY KEY,
DepartmentName varchar(50) NOT NULL
)
CREATE TABLE employee
(
EmployeeID int IDENTITY(1001, 1) PRIMARY KEY,
DepartmentID int NOT NULL,
Salary smallmoney NOT NULL DEFAULT 0,
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL,
AddressLine1 varchar(30),
AddressLine2 varchar(30),
City varchar(30),
State char(2),
Zip char(5),
HomePhone varchar(10),
HomeFax varchar(10),
CONSTRAINT FK_employee_department FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID),
CONSTRAINT CK_employee_salary CHECK (Salary >= 0 AND Salary <= 200000)
)
EmployeeID and DepartmentID on the Department table both use the shorthand inline form of PRIMARY KEY, a column-level constraint written directly after the column's data type rather than spelled out separately; SQL Server assigns it a generated name automatically when none is given. LastName, FirstName, and DepartmentID are marked NOT NULL, since a row genuinely missing any of those three values isn't a usable employee record. Salary picks up a DEFAULT of 0, so a row inserted without specifying one doesn't end up NULL by accident. The two named constraints at the bottom are table-level constraints, written using the table_constraint syntax covered earlier: FK_employee_department is the foreign key enforcing that every DepartmentID value in the employee table actually exists in the Department table, and CK_employee_salary is the same CHECK constraint from the previous lesson's salary discussion, now actually written into the table instead of just described.
Naming both constraints explicitly, rather than letting SQL Server generate a name, matters most once something goes wrong. An error raised by an unnamed constraint identifies it by its system-generated name, something like CK__employee__465962..., which says nothing useful about what was actually violated; an error raised by CK_employee_salary says exactly that, at a glance, without needing to go look anything up first.
Verifying What Was Created
After running statements like these, it's worth confirming the table actually looks the way it was intended to, rather than assuming the statement did exactly what was meant. Thesys.columns catalog view, in the same family covered in earlier lessons for filegroups and databases, lists every column on a table along with its type and nullability:
SELECT name, is_nullable, is_identity
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.employee')
ORDER BY column_id
NOT NULL while it's still a one-line fix, rather than after other objects or application code already depend on the table as it stands. sys.tables answers a slightly different question, whether the table exists at all:
SELECT name, create_date
FROM sys.tables
WHERE name = 'employee'
DROP DATABASE, covered earlier in this module, CREATE TABLE in SQL Server has no built-in IF NOT EXISTS clause to fall back on. A script meant to run safely more than once, without erroring out on a second run because the table it's trying to create is already there, checks for the table explicitly with OBJECT_ID first:
IF OBJECT_ID('dbo.employee', 'U') IS NULL
BEGIN
CREATE TABLE employee
(
EmployeeID int IDENTITY(1001, 1) PRIMARY KEY,
DepartmentID int NOT NULL,
Salary smallmoney NOT NULL DEFAULT 0,
LastName varchar(30) NOT NULL,
FirstName varchar(30) NOT NULL
)
END
'U' passed to OBJECT_ID narrows the check to user tables specifically, so the same name used by a view, a procedure, or some other kind of object elsewhere in the database doesn't produce a false positive. This pattern comes up often enough in setup and deployment scripts that it's worth recognizing on sight, even in a lesson that's otherwise about creating a table for the first time rather than guarding against creating it twice.
Notice that example qualifies the table name as dbo.employee rather than just employee. Every table belongs to a schema, dbo by default unless a database is set up with others, and the earlier lesson on permissions covered why that matters: creating a table requires ALTER permission on the specific schema it's being created in. Writing the schema name explicitly, even when it's just the default dbo, makes that association visible in the script itself rather than left implicit, and it avoids any ambiguity in a database that happens to have more than one schema with a table of the same name in each.
Create SQL-Server Table - Exercise
Click the exercise link below to practice creating tables directly.Create SQL-Server Table - Exercise
A table built from the handful of pieces covered in this lesson, a name, a set of columns with their data types, and whatever constraints and an
IDENTITY column it needs, is already a genuinely usable table. The employee and Department tables built up over the course of this lesson went from a single bare column, to a full set of identifying and contact information, to a pair of related tables with their primary keys, a foreign key, a default, and a check constraint all declared directly in the CREATE TABLE statement itself rather than left to be enforced somewhere else.
The next lesson covers how to modify an existing table's structure once it's already in place and, in most real situations, already holding data, a meaningfully different problem from designing a table that doesn't exist yet.