Lesson 1

In the previous module, you learned how to define attributes for your table columns: data types, length and scale, nullability, defaults, the IDENTITY property, the ROWGUIDCOL flag, and user-defined types. All of that knowledge describes individual columns, but a column doesn't exist on its own. This module turns to the two structures that actually hold those columns: the table a column lives in, and the database that table lives in. Before you can define a single column, you need somewhere to put the table that contains it, and that means creating a database first.

After completing this module, you will be able to:

1. Create a database
2. Modify a database
3. Delete a database
4. Create a table
5. Modify a table
6. Delete a table

A SQL Server database is the top-level container for everything else covered in this module: every table, along with its indexes, constraints, and the physical files that store all of that data on disk, belongs to exactly one database. Behind the scenes, a database is backed by at least two operating-system files: a primary data file, conventionally given an .mdf extension, that holds the actual tables and their data, and a transaction log file, conventionally given an .ldf extension, that records every change made to the database so SQL Server can recover correctly after a crash or roll back an incomplete transaction. A database can span additional secondary data files, typically given an .ndf extension, once a single file isn't convenient to manage anymore, but every database needs at least the one data file and one log file to exist at all.

A single SQL Server instance, meaning one running copy of the SQL Server service, routinely hosts dozens of separate databases side by side, each one isolated from the others: a permissions problem, a corrupted table, or a runaway query in one database doesn't reach across into another. That isolation is part of why "which database is this table in" is one of the first questions you learn to ask on any real SQL Server system, right alongside "which table."

It's easy to conflate "a SQL Server" with "a database" when you're just getting started, but they're two different layers. A SQL Server instance is one running copy of the database engine software, with its own logins, its own configuration, and its own set of system databases, covered next. A single physical server can run more than one instance of SQL Server at once, each completely independent of the others, and each instance in turn hosts however many individual databases its administrator creates on it. For the rest of this module, "the database" always means one specific database living on one specific instance; which instance you're connected to, and which database within it you're currently working in, matters just as much as the table name itself when you're troubleshooting a query that didn't behave the way you expected.

Before you create your first database, it's worth knowing that every SQL Server instance already comes with several databases of its own, created automatically during installation and used internally by the engine itself.

Database	Purpose
master	Records every other database that exists on the instance, along with instance-wide configuration and login information. If master is damaged, the instance itself may not start.
model	A template database. Whatever objects, permissions, or settings you place in model are automatically copied into every new database you create afterward.
msdb	Used by SQL Server Agent to store scheduled jobs, alerts, and backup and restore history.
tempdb	A shared workspace for temporary tables, table variables, and intermediate results SQL Server needs while executing a query. Recreated from scratch every time the instance restarts.

None of these four are where your own application tables belong. They exist to support the engine itself, and modifying them directly, beyond the documented, supported customizations to model, is a good way to destabilize an entire instance rather than just one database. The databases you create in this module, and populate with tables in the lessons that follow, are entirely separate from, and unaffected by, these four system databases.

Creating a database means more than choosing a name. The full CREATE DATABASE statement lets you specify exactly where its data and log files live on the file system, how large each one starts out, and how much each one grows once it fills up:

CREATE DATABASE TrainingDB
ON PRIMARY
( NAME = TrainingDB_Data, FILENAME = 'C:\SQLData\TrainingDB.mdf',
  SIZE = 100MB, FILEGROWTH = 25MB )
LOG ON
( NAME = TrainingDB_Log, FILENAME = 'C:\SQLData\TrainingDB.ldf',
  SIZE = 25MB, FILEGROWTH = 10MB );

A trailing WITH clause can also set database-level options at creation time, such as the default collation used for text comparisons. The next lesson in this module walks through this syntax in full, parameter by parameter, including what happens if you leave the file and sizing details out entirely and let SQL Server fall back to its own defaults. For now, the idea to take away is simpler: a database is the container you create once, before any of the tables that will depend on it can exist.

Creating a database isn't something every login is allowed to do by default. On a typical installation, only a login that's a member of the sysadmin or dbcreator server-level role, or one that's been explicitly granted the CREATE ANY DATABASE permission, can run CREATE DATABASE successfully; anyone else receives a permission-denied error instead. This is a deliberate safeguard: creating a database consumes disk space and server resources that an administrator usually wants to control on purpose, rather than leave open to every user with a login.

A database rarely stays exactly as it was defined on day one. You'll eventually need to add a second data file as the original one fills up, change how much a file grows each time it runs out of room, or adjust a database-level option such as its default collation or recovery model. ALTER DATABASE handles all of these changes without requiring you to drop and recreate the database from scratch:

ALTER DATABASE TrainingDB
MODIFY FILE
( NAME = TrainingDB_Data, MAXSIZE = 500MB );

A later lesson in this module covers the specific forms ALTER DATABASE takes, from adding files to changing options that affect how the whole database behaves.

Deleting a database, by contrast, is the one operation in this list that deserves real caution. DROP DATABASE removes the database and every object inside it, tables, data, indexes, everything, in one step that can't be undone short of restoring from a backup:

DROP DATABASE TrainingDB;

SQL Server won't let you drop a database that still has active connections to it without taking an extra step first, usually switching it to single-user mode or explicitly disconnecting every session, which is itself a small safeguard against dropping the wrong database by accident. A later lesson covers the exact syntax and the situations that call for that extra step, but it's worth internalizing now: this isn't an operation to run against a database you aren't certain you want to permanently lose. A current backup is the only real safety net here; once DROP DATABASE completes, restoring the database from anywhere other than a backup simply isn't an option.

It's worth seeing the complete sequence once, start to finish, before the lessons that follow break each statement down individually. Creating a database and then creating a table inside it are two separate statements, and a USE statement in between tells SQL Server which database the CREATE TABLE statement that follows should apply to:

CREATE DATABASE TrainingDB
ON PRIMARY
( NAME = TrainingDB_Data, FILENAME = 'C:\SQLData\TrainingDB.mdf',
  SIZE = 100MB, FILEGROWTH = 25MB )
LOG ON
( NAME = TrainingDB_Log, FILENAME = 'C:\SQLData\TrainingDB.ldf',
  SIZE = 25MB, FILEGROWTH = 10MB );
GO

USE TrainingDB;
GO

CREATE TABLE dbo.Employees (
    EmployeeID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FirstName    NVARCHAR(50) NOT NULL,
    HireDate     DATE NOT NULL DEFAULT (GETDATE())
);

The GO batch separator isn't strictly required between every statement here, but it's common practice right after CREATE DATABASE specifically, since some tools won't let a later statement in the same batch reference a database that the batch itself just created. Everything after that first GO, the USE statement and the CREATE TABLE statement that follows it, runs against TrainingDB specifically, not against whichever database your connection happened to default to beforehand. This three-statement sequence, create the database, switch into it, create a table, is the pattern every lesson in this module ultimately builds toward.

Once a database exists, a table is where every column attribute from the previous module actually gets put to use. A CREATE TABLE statement is, in practice, a list of column definitions, each one declaring the data type, length or precision, nullability, default, and any IDENTITY or ROWGUIDCOL flag that column needs, assembled together under one table name:

CREATE TABLE dbo.Employees (
    EmployeeID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FirstName    NVARCHAR(50) NOT NULL,
    HireDate     DATE NOT NULL DEFAULT (GETDATE()),
    ManagerID    INT NULL,
    Preferences  JSON NULL
);

Every piece of that statement is something the previous module already covered individually; this module's job is to show how those pieces come together into a complete, working table, inside a database you've already created, and how to create, alter, and drop one correctly. The Preferences column above is a reminder that a table isn't limited to the traditional numeric and character types: SQL Server 2025's native JSON type fits into a CREATE TABLE statement exactly like any other column type.

Modifying a table is one of the most frequent tasks in ongoing database work. Requirements change, and a table that was correct on day one usually needs to grow with them: adding a new column, widening a column that turned out too small, adding or dropping a constraint, or renaming an object. ALTER TABLE covers all of it:

ALTER TABLE dbo.Employees
ADD PhoneNumber VARCHAR(20) NULL;

Later lessons in this module walk through the specific forms ALTER TABLE takes, including the forms that only add something new to a table's definition and the forms that change something already there.

Deleting a table with DROP TABLE carries the same weight as deleting a database, just scoped to one table instead of the whole container:

DROP TABLE dbo.Employees;

The table and every row of data in it are gone as soon as the statement completes. Foreign keys referencing that table add another wrinkle worth knowing about going in: SQL Server generally won't let you drop a table that something else still depends on until that dependency is removed first.

It's worth drawing a distinction here between two operations that sound similar but do very different things. DROP TABLE removes the table itself, structure and all; it no longer exists afterward, and recreating it means running CREATE TABLE again from scratch. TRUNCATE TABLE, by contrast, empties every row out of a table while leaving the table's own definition, columns, constraints, and indexes completely intact, ready to accept new rows immediately. If the goal is to clear out all the data in a table you still want to keep using, TRUNCATE TABLE is almost always the better fit; if the goal is to remove the table from the database entirely, DROP TABLE is the one you want.

Because this module covers the same three operations, create, modify, and delete, at two different levels, the database level and the table level, it helps to see them lined up together before the lessons that follow go through each one in full:

Operation	Database Statement	Table Statement
Create	CREATE DATABASE	CREATE TABLE
Modify	ALTER DATABASE	ALTER TABLE
Delete	DROP DATABASE	DROP TABLE

The pattern repeats because the underlying idea repeats: whether you're working with the container or with what's inside it, SQL Server gives you one statement to bring the object into existence, one to change it afterward without starting over, and one to remove it permanently. Keeping that parallel in mind makes the individual lessons in this module easier to place as you work through them: each one is really just filling in the details of a pattern you've already seen here in outline.

Before moving into the create, modify, and delete syntax the rest of this module covers, it's worth knowing how to simply look at what already exists, since you'll use this constantly once you're not just building training databases but working against servers other people manage too.

In SQL Server Management Studio, Object Explorer lists every database on the connected instance under a top-level Databases node; expanding any one database down to its own Tables node lists every table inside it. That's usually the fastest way to confirm a database or table actually exists, and is spelled exactly the way you think it is, before writing a single line of T-SQL against it.

T-SQL offers the same information back as ordinary query results, which is often more convenient when you need to search, filter, or script against what comes back:

-- every database on this instance
SELECT name, create_date FROM sys.databases
ORDER BY name;

-- every table in the current database
SELECT SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName
FROM sys.tables
ORDER BY SchemaName, TableName;

sys.databases is instance-wide, so it lists every database regardless of which one your connection happens to be using at the moment; sys.tables, like most catalog views that describe tables and columns, is scoped to whichever database your connection is currently using, so switching databases with USE, or reconnecting to a different one, changes what it returns.

The next lesson introduces CREATE DATABASE in SQL Server 2025 in full: the syntax for specifying data and log files, sizing and growth settings, and the options available when a database is first created.