Lesson 13

This module set out from a single starting point: before a single column can hold data, something has to exist to hold the table that contains it, and something has to exist to hold that database in turn. Twelve lessons later, that starting point has grown into a complete, working vocabulary for the two layers everything else in SQL Server sits on top of: the database as a container, and the table as the structure inside it that actually organizes data into columns and rows. This closing lesson doesn't introduce anything new. It walks back through what the module covered, lesson by lesson, and draws out the pattern that was quietly repeating the entire time: create, modify, and delete, applied first to a database, then to a table, with permissions, tools, and a habit of verifying your own work running underneath both. It's worth noticing, too, how much of the module turned out to be about restraint rather than raw capability. SQL Server will let a database or a table be built in almost any shape imaginable, oversized data types, unnamed constraints, no filegroup planning at all, and it will run that way without complaint for quite a while. The actual skill this module was building toward is knowing which of those shortcuts to decline up front, because every one of them eventually turns into either wasted storage or a harder conversation later, usually both.

The Database Lifecycle

The first half of this module worked through a database's full lifecycle, from nothing to something and, when appropriate, back to nothing again. Creating one started with the simplest possible form, CREATE DATABASE employee;, and built up from there to a fully specified statement naming exact data and log files, starting sizes, maximum sizes, and growth increments, the kind of statement behind the SalesDB database that recurred throughout the rest of the module. Beyond a plain create, that lesson also laid out the three real starting points a database can come from: created fresh, restored from a backup, or attached from an existing pair of data and log files with FOR ATTACH, a decision worth making deliberately rather than defaulting to CREATE DATABASE out of habit. A database left with no file details specified at all doesn't fail; it quietly inherits its starting size and growth settings from the model system database, typically 8MB per file, which is exactly the kind of default worth knowing about before it surprises you on a database that fills up faster than expected. Modifying a database turned out to cover far more ground than changing a setting here or there. ALTER DATABASE handled growing a file, shrinking one back down with DBCC SHRINKFILE, changing the recovery model between FULL and SIMPLE, switching a database into single-user mode with SET SINGLE_USER WITH ROLLBACK IMMEDIATE to force out other connections before a sensitive change, and taking a database offline and back online again. The maintenance pattern that tied all of this together, isolate the database, make the change, reopen it, then verify the result against a catalog view like sys.master_files, is worth carrying forward well beyond this module; it's the same shape nearly every real maintenance task against a live database ends up taking. Organizing a database's storage introduced filegroups: the primary filegroup every database starts with, a default filegroup that catches anything not explicitly placed elsewhere, and user-defined filegroups like SalesArchive, built specifically to separate a database's actively changing data from data that's settled and largely read-only. Two special-purpose filegroup types rounded this out, one for FILESTREAM data stored as ordinary files outside the database, and one for memory-optimized tables; SQL Server 2025 specifically lifted a long-standing restriction here, finally allowing the last MEMORY_OPTIMIZED_DATA filegroup on a database to be removed once every object depending on it has been dropped first, something earlier versions simply didn't permit without dropping the whole database. Deleting a database closed out this first half with DROP DATABASE, the one operation in the entire module that genuinely deserves to be treated with more caution than the others. The permanence is real: a dropped database's catalog entry and its underlying data and log files are gone the moment the statement completes, recoverable only from a backup taken beforehand. SQL Server builds in a few protections around this all the same. A database can't be dropped while another session, including the one issuing the drop itself, is still using it; its own four system databases, master, model, msdb, and tempdb, can never be dropped at all; and a narrow edge case around Azure Blob Storage file-snapshot backups aside, an ordinary DROP DATABASE against a database backed up the usual way leaves nothing extra behind to clean up.

Permissions and Tools: The Threads Running Underneath

Two topics didn't fit neatly into the create-modify-delete sequence on their own, because they apply to all three stages at once rather than to any single one of them. Permissions turned out to be more layered than a single "do you have access" check. Creating a database runs through CREATE ANY DATABASE, the modern server-level permission, or the older CREATE DATABASE permission that can only be granted from master, or membership in the dbcreator or sysadmin fixed server roles. Modifying one generally needs the ALTER permission on that specific database, or db_owner membership within it, with renaming pulled out as a special case that also touches the instance-wide database list in master. Dropping one needs ALTER ANY DATABASE, CONTROL on the database itself, or again one of those same two server roles. The same layering reappeared at the table level later in the module: creating a table needs CREATE TABLE together with ALTER on its schema, altering one needs ALTER on the table itself, and dropping one needs ALTER on its schema, CONTROL on the table, or db_ddladmin membership. None of these permissions exist in isolation from each other; sysadmin membership alone satisfies every single one of them, which is exactly why handing it out casually is worth resisting, and functions like IS_SRVROLEMEMBER() and HAS_PERMS_BY_NAME() exist specifically so a login's actual access can be checked directly rather than inferred from a failed statement. Tools followed a parallel lesson of their own: no single interface is the one correct way to do any of this. SSMS remains the default for interactive, day-to-day work, its New Database dialog and table designer ultimately generating the same statements covered throughout this module behind a graphical front end, complete with a Script option that turns a dialog-built change back into the T-SQL that produced it. Beyond SSMS, sqlcmd and PowerShell's Invoke-Sqlcmd cover scripted, unattended work; SQL Server Data Tools and the SQL Database Projects extension treat an entire schema as a versioned project rather than a live server's current state; SqlPackage and DACPAC deployment automate publishing that project to a target server as part of a build pipeline; and the MSSQL extension for Visual Studio Code gives a lighter-weight option for a developer who'd rather not leave their editor. Each one, underneath, is still just a more convenient way of arriving at a CREATE DATABASE, ALTER TABLE, or DROP TABLE statement, and none of them bypass the permission checks covered above; a tool can make a statement easier to issue, never easier to be allowed to issue.

What a Database Actually Holds

Between covering how to create a database and how to put a table inside one, this module paused to catalog everything else a database can actually contain: schemas that group and qualify object names, heaps and clustered and nonclustered indexes, ordinary and indexed views, stored procedures, functions, triggers, the full set of constraint types, temporal tables that automatically track a row's history, synonyms, and specialized features like Service Broker, Full-Text Search, and CLR integration. SQL Server 2025 added two genuinely new entries to that list covered specifically as 2025 additions: a native JSON data type and a VECTOR type, both available as ordinary column types right alongside the more familiar numeric and character types this module worked with throughout. The Timesheets database, with its four tables, Employees, Clients, Tasks, and Timesheets, each paired with its own index, served as the concrete illustration of this entire hierarchy in miniature: a database containing tables, each table containing the index built to speed up queries against it.

The Table Lifecycle

The second half of this module walked through exactly the same arc, one level further in: designing, creating, altering, and dropping a table, the thing that actually lives inside the database the first half covered. Before a single CREATE TABLE statement, this module paused on the design decisions worth making deliberately rather than by default: choosing a data type sized to what a column will actually hold, illustrated by working through a salary column that needed to stay under $200,000 with exact decimal precision, where MONEY's 8 bytes and enormous range turned out to be considerably more than necessary against SMALLMONEY's 4 bytes and tighter, still more than adequate range, and where DECIMAL(9,2) stood out as the alternative that guarantees exact precision against FLOAT's rounding risk; the same sizing discipline carried over to text columns, where NVARCHAR(MAX)'s unlimited convenience comes at the cost of not being usable in certain index keys. Alongside data types, this stage covered primary keys, including composite ones spanning more than one column, illustrated with an OrderDetails junction table keyed on (OrderID, ProductID) together rather than either column alone; normalizing a design before denormalizing any part of it deliberately; foreign keys enforcing which tables relate to which; naming conventions applied consistently rather than argued over; an indexing strategy planned alongside the table rather than bolted on afterward; and the constraint types, CHECK, UNIQUE, and DEFAULT, that enforce data quality at the database level itself. Creating a table put all of that into an actual CREATE TABLE statement, built up the same way the database lesson built up its own syntax: starting from a single bare column, CREATE TABLE employee (salary smallmoney), and growing from there into the full employee table with an EmployeeID column seeded at 1001 through IDENTITY(1001, 1), a related Department table, and named constraints tying them together, FK_employee_department as the foreign key and CK_employee_salary as the check constraint enforcing the same salary ceiling the design-stage lesson had worked through conceptually. Naming those constraints explicitly, rather than accepting whatever name SQL Server would otherwise generate automatically, something unreadable like CK__employee__465962..., paid off directly in the two lessons that followed. Modifying a table, through ALTER TABLE, covered adding a column like HireDate with either a nullable default or an explicit one, widening an existing column such as Products.Description while flagging that not every such change is the cheap, metadata-only operation it might look like, dropping a column such as HomeFax that's outlived its usefulness, adding and dropping constraints by the names given to them in the previous lesson, and temporarily disabling a constraint or trigger during a bulk operation with NOCHECK, re-enabling it afterward with the deliberately doubled WITH CHECK CHECK CONSTRAINT, the first CHECK telling SQL Server to actually revalidate the data this time, not merely flip the constraint back to enabled without checking anything. Renaming a column, it turned out, isn't something ALTER TABLE does at all; that's sp_rename's job specifically. Deleting a table with DROP TABLE closed the table lifecycle the same way DROP DATABASE had closed the database one, permanent and immediate the moment it runs. The dependency rules turned out to be sharper than they first appear: a foreign key still pointing at a table blocks the drop outright, raising the specific, nameable error 3726 rather than failing silently, and disabling that foreign key with NOCHECK does nothing to clear the way, since the constraint object itself still exists even with its enforcement switched off; only actually dropping the constraint, found ahead of time with a query against sys.foreign_keys, does. Ordinary views and stored procedures referencing a table behave differently again: they don't block the drop at all, they simply fail the next time they're queried, with one specific exception, a view created WITH SCHEMABINDING, which does block the drop the same way a foreign key does. A quick SELECT * INTO Backup_employee FROM employee before any of this preserved the data itself, though worth remembering exactly what it preserves and what it doesn't: column data types and the values themselves, but none of the original table's indexes, constraints, triggers, or IDENTITY behavior.

One Pattern, Two Levels

Looking back across all twelve lessons together, the pattern first sketched out at the very start of this module turns out to have held throughout, now with every statement actually filled in:

Operation	Database Statement	Table Statement
Create	CREATE DATABASE SalesDB ...	CREATE TABLE employee (...)
Modify	ALTER DATABASE SalesDB SET ...	ALTER TABLE employee ADD/ALTER COLUMN ...
Delete	DROP DATABASE SalesDB	DROP TABLE employee

Database-level work and table-level work differ mostly in scale, not in kind. Both levels gate creation and modification and deletion behind specific, checkable permissions rather than a single blanket "administrator" switch. Both have a permanent, no-undo deletion statement that SQL Server deliberately makes harder to run by accident than to run on purpose, through active-connection checks, dependency checks, and in the table's case a specific numbered error rather than a silent failure. And both reward the same habit above almost anything else covered in this module: verifying a change actually took effect, rather than assuming the statement that was supposed to make it did.

The Habit That Ran Through Every Lesson

Nearly every lesson in this module eventually reached for one of SQL Server's own catalog views to confirm something rather than simply trust that a statement had done what it looked like it should: sys.databases and sys.master_files to confirm a database's current size, growth settings, and recovery model; sys.filegroups and sys.database_files to confirm which filegroup actually holds which file; sys.tables and sys.columns to confirm a table's structure after creating or altering it; sys.foreign_keys and the wider sys.sql_expression_dependencies to confirm what still depends on a table before attempting to drop it; and sys.server_role_members, sys.database_role_members, and functions like sys.fn_my_permissions to confirm exactly who can do what. None of these queries are complicated, and that's rather the point: a thirty-second check against the system's own metadata, run as a matter of habit rather than only when something's already gone wrong, is what actually separates routine database administration from guessing.

Mistakes Worth Avoiding

A few specific missteps surfaced across these twelve lessons often enough to call out directly, since each one is easy to make once and genuinely costly to make on a production system. Sizing a column too loosely is the quietest of these. A FLOAT column holding currency, or an NVARCHAR(MAX) column holding something that will never exceed a few dozen characters, won't throw an error on day one; it will simply cost more storage than it needed to and, in the FLOAT case, eventually produce a rounding discrepancy that's genuinely confusing to track down later, exactly the kind of problem the DECIMAL(9,2) salary column was built to avoid from the start. Letting SQL Server auto-generate a constraint's name is the second. An unnamed CHECK or FOREIGN KEY constraint still works correctly the day it's created; the trouble shows up later, the first time it needs to be dropped or disabled, and the name in the error message or the catalog view result is CK__employee__4659... rather than something a human reading it can immediately recognize. The few extra words it takes to name a constraint when creating it are paid back the first time that constraint needs to be touched again. Mistaking NOCHECK for a real removal is the third, and it caused a direct error in this module's own walkthrough: disabling a foreign key or check constraint with NOCHECK CONSTRAINT switches off its enforcement, but the constraint object itself is still sitting there, and SQL Server still refuses to drop a table it's attached to on that basis alone. Clearing the way for a drop takes an actual DROP CONSTRAINT, not a disable. Skipping a backup before a destructive statement is the last, and the one with no partial credit. DROP DATABASE and DROP TABLE both execute immediately and both are final; neither one pauses to ask for confirmation the way a GUI delete dialog might. A quick SELECT * INTO copy, or a full backup for anything larger than a single table, costs very little time measured against what it costs to not have one the one time it actually mattered.

What's Ahead

This module covered the structures data lives in: the database as a container, and the table as the thing inside it that organizes columns into rows. The next module turns to the data itself, working with the rows a table like employee actually holds once it exists, rather than the structure that holds them.

In this module, you learned the basic Transact-SQL syntax required to create, modify, and delete a SQL Server 2025 database. Having completed this module, you should be able to:

1. List the tools used to create a database
2. Identify the permissions required to create, modify, or delete databases
3. Describe elements of a basic database
4. Issue Transact-SQL statements to create, modify, or delete databases

Glossary Terms: This module introduced you to the following terms:

1. database: In SQL Server 2025, a database is a structured set of data organized for efficient storage, retrieval, and management. It serves as a container for various database objects like tables, views, stored procedures, and indexes, providing a logical separation for different applications or data sets.
2. DBO: In SQL Server 2025, DBO stands for Database Owner, a special user account that has all permissions within a specific database. The DBO can perform any operation within the database, including creating, altering, and deleting objects, as well as managing users and permissions.
3. hierarchy: In SQL Server 2025, a hierarchy refers to a structured relationship between different levels of data, often represented in a parent-child format. This hierarchical structure allows for organizing and querying data in a tree-like manner, which is particularly useful for representing things like organizational charts, product categories, or geographical locations.
4. SA: In SQL Server 2025, SA stands for System Administrator, a special login that has the highest level of privileges on the entire SQL Server instance. The SA can perform any operation on the server, including managing logins, databases, linked servers, and server configuration settings.
5. transaction log:In SQL Server 2025, the transaction log is a critical component that records all changes made to the database, ensuring data integrity and recoverability. It acts as a write-ahead log, meaning changes are written to the log first before being applied to the data files, allowing for rollback or recovery in case of failures.

In the next module, we will investigate column attributes for a SQL Server table.

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
SQL Server Databases - Quiz