Creation and Deletion of Databases in SQL Server

5 min
Beginner

Every journey begins somewhere, and in SQL Server, our data universe begins by creating a Database.

Before we can create tables, procedures, or save a single byte of information, we need to define the database (that is, the container where everything will live).

Although SQL Server Management Studio (SSMS) allows us to do this with a “right-click”, we also need to know how to do it via code T-SQL.

Doing it with code allows us to automate deployments, copy to another server, backups, have total control over the physical configuration…

And honestly, you will have to do it sooner or later.

Basic Database Creation

The most common way to create a database is to use the CREATE DATABASE statement followed by the name we want to give it.

CREATE DATABASE MiEmpresa;
GO

Copied!

When we execute this command, SQL Server does the following:

Copies the system database called model (which acts as a template).
Creates the physical files in the server’s default location.
Assigns the default sizes.

Remember that to execute database creation commands, you generally must be in the system database master.

Basic creation is fine for testing, but in a real environment we need to control where and how data is saved.

A database in SQL Server consists, at a minimum, of two physical files:

Data File (.mdf): This is the primary data file (Master Data File). This is where tables, indexes, and the actual data are stored.
Log File (.ldf): This is the transaction log file (Log Data File). All modifications are recorded here sequentially. It is vital for guaranteeing ACID properties and disaster recovery.

To define a robust database, we specify the location, the initial size, and very importantly, how the file should grow when it fills up (FILEGROWTH).

USE master;
GO

CREATE DATABASE GestionPedidos
ON PRIMARY 
(
    NAME = N'GestionPedidos_Data',           -- Logical name
    FILENAME = N'C:\DatosSQL\Pedidos.mdf',   -- Physical path (must exist)
    SIZE = 100MB,                            -- Initial size
    MAXSIZE = UNLIMITED,                     -- Size limit
    FILEGROWTH = 64MB                        -- Automatic growth
)
LOG ON 
(
    NAME = N'GestionPedidos_Log',
    FILENAME = N'C:\DatosSQL\Pedidos.ldf',
    SIZE = 50MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 10MB
);
GO

Copied!

Why configure FILEGROWTH? By default, SQL Server might configure growth as a percentage (e.g., 10%). This is dangerous in large databases.

If your database weighs 1TB, a 10% growth will try to reserve 100GB at once! It’s better to set a fixed growth in MB.

Selecting the Database with `USE`

A very common mistake is to create the database and then immediately start creating tables… without having entered it!. And basically messing everything up.

If you don’t change the context, you are creating your tables inside the master database (the one created by default when installing SQL).

To avoid this, we always use the USE command:

USE GestionPedidos;
GO
-- Now everything we execute here will happen inside 'GestionPedidos'

Copied!

Deleting Databases

To delete a database, we use the DROP DATABASE statement. The basic syntax is:

DROP DATABASE GestionPedidos;
GO

Copied!

Be careful! This operation is destructive and irreversible (unless you have backups).

DROP DATABASE deletes the database, its tables, its users and physically deletes the .mdf and .ldf files from the hard drive.

If we try to delete a database that doesn’t exist, SQL Server will return an error. To make our scripts more robust (re-runnable), we can check if it exists before deleting it.

Since SQL Server 2016, we have a simplified and elegant syntax:

DROP DATABASE IF EXISTS GestionPedidos;
GO

Copied!

If you use an older version, you would have to query the system table sys.databases:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'GestionPedidos')
BEGIN
    DROP DATABASE GestionPedidos;
END

Copied!

Try to execute a DROP DATABASE while you (or a colleague) have that database selected or an application is connected to it. SQL Server will throw an error:

Cannot drop database because it is currently in use

To force deletion, we must first kick out all users and put the database in SINGLE_USER mode.

USE master;
GO

-- 1. Put the database in single-user mode and close connections immediately
ALTER DATABASE GestionPedidos 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE; 
GO

-- 2. Now we can delete it without problems
DROP DATABASE GestionPedidos;
GO

Copied!

The WITH ROLLBACK IMMEDIATE command is the key here: it instantly cuts any open transaction, rolling back unsaved changes, and disconnects users.

Basic Database Creation

Physical Architecture: MDF and LDF Files

Selecting the Database with USE

Deleting Databases

Existence Check

The Problem of Open Connections

Selecting the Database with `USE`