LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 717 min read

Creating Your First Database

Use CREATE DATABASE and USE to build and switch into your first SQL Server database, and explore it through Object Explorer.

Introduction

Everything you'll build in this course — tables, constraints, data — lives inside a database. Before you can create a single table, you need a database to hold it. This lesson creates one from scratch, using nothing but T-SQL, and shows how to see it reflected in SSMS.

CREATE DATABASE

Creating a database in SQL Server takes a single statement: CREATE DATABASE followed by the name you want. Open a New Query window (connected to your server, not scoped to any particular existing database) and run the following.

CREATE DATABASE SchoolDB;
Messages

Click Run to see what this code prints.

That's the whole statement. SQL Server creates the database using sensible defaults: a primary data file, a transaction log file, and settings inherited from the model system database, which acts as a template for every new database you create.

Exploring in Object Explorer

Switch to Object Explorer, right-click the Databases folder, and choose 'Refresh'. SchoolDB now appears in the list alongside the system databases. Expand it and you'll see empty folders for Tables, Views, Programmability, Security, and more — the skeleton every database starts with, ready for you to fill in over the coming lessons.

The USE Statement

A single SQL Server instance can host many databases at once, so every connection needs to know which one your statements should run against. The USE statement switches the current query window's context to a specific database.

USE SchoolDB;
GO


SELECT DB_NAME() AS CurrentDatabase;
Result

Click Run to see what this code prints.

Instead of typing USE every time, you can also just pick the database from the dropdown in the SSMS toolbar — both approaches change the same thing: which database your next statement runs against.

Good Habit

Start scripts that build out a new database with USE DatabaseName; at the top. It documents intent and protects you from accidentally creating tables in the wrong database.

Database Files

Behind the scenes, every SQL Server database is backed by at least two physical files: a primary data file (.mdf), which stores your actual tables and indexes, and a transaction log file (.ldf), which records every change for recovery and rollback purposes. You can see these with a quick system view query.

SELECT name, physical_name, type_desc
FROM sys.master_files
WHERE database_id = DB_ID('SchoolDB');
Result

Click Run to see what this code prints.

Dropping a Database

If you need to remove a database entirely — files and all — use DROP DATABASE. This is destructive and cannot be undone outside of a backup, so use it carefully.

-- Only run this if you truly want to delete SchoolDB and everything in it:
DROP DATABASE SchoolDB;
Destructive Operation

DROP DATABASE permanently deletes the database's data and log files from disk. There is no undo short of restoring from a backup. You'll recreate SchoolDB in the next lesson, so avoid dropping it until then.

Common Mistakes

Avoid These Mistakes
  • Running CREATE TABLE statements without first running USE — they can silently land in the wrong database (often master).
  • Assuming Object Explorer will show a new database automatically without a manual refresh.
  • Trying to CREATE DATABASE with a name that's already in use, which raises an error rather than silently succeeding.
  • Running DROP DATABASE against the wrong database because the toolbar dropdown wasn't checked first.

Best Practices

  • Always follow CREATE DATABASE with USE before running any further setup statements.
  • Use SELECT DB_NAME(); whenever you're unsure which database context you're currently in.
  • Choose clear, descriptive database names (SchoolDB, not db1) — you'll be reading this name in scripts for the rest of the course.
  • Treat DROP DATABASE as a last resort in any real project; prefer backups and staged testing environments instead.

Frequently Asked Questions

No, not for learning purposes. Without an ON clause, SQL Server places the .mdf and .ldf files in the server's default data directory automatically.

Yes. Table names only need to be unique within a single database. SchoolDB.Students and CompanyDB.Students can coexist without conflict.

Your statements run against whatever database was last selected — either from a previous USE call or the SSMS toolbar dropdown — which may not be the one you intended.

The practical limit is extremely high (32,767 databases per instance), so for learning and most real applications, you won't come close to hitting it.

Key Takeaways

  • CREATE DATABASE DatabaseName; creates a new database with sensible defaults.
  • USE DatabaseName; switches the current session's context to that database.
  • Every database is backed by a data file (.mdf) and a transaction log file (.ldf).
  • DROP DATABASE permanently deletes a database's files and cannot be undone without a backup.

Summary

You've created your first database, learned to switch context into it with USE, and seen the files backing it. With a database in place, the next lesson introduces T-SQL itself in more depth — the procedural building blocks (variables, control flow, error handling) you'll rely on throughout the rest of this course.

Next Lesson →

Introduction to T-SQL