Creating and Managing Databases

A schema is a namespace that groups objects and simplifies security.

3.1 Creating a Database

-- Simple
CREATE DATABASE ShopDB;

-- Detailed
CREATE DATABASE ShopDB
ON PRIMARY
(   NAME = ShopDB_Data,  FILENAME = 'D:\Data\ShopDB.mdf',
    SIZE = 512MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB ),
FILEGROUP FG_Archive
(   NAME = ShopDB_Archive, FILENAME = 'E:\Data\ShopDB_Archive.ndf',
    SIZE = 256MB, FILEGROWTH = 128MB )
LOG ON
(   NAME = ShopDB_Log, FILENAME = 'L:\Log\ShopDB.ldf',
    SIZE = 256MB, FILEGROWTH = 256MB )
COLLATE Latin1_General_100_CI_AS_SC_UTF8;

3.2 Database Files

Primary data file (.mdf): exactly one per database.

Secondary data files (.ndf): optional.

Transaction log file (.ldf): at least one. Written sequentially.

Filegroups: logical containers for data files (PRIMARY, user-defined, FILESTREAM, MEMORY_OPTIMIZED_DATA).

3.3 Modifying Databases

ALTER DATABASE ShopDB ADD FILE (NAME = ShopDB_Data2, FILENAME = 'D:\Data\ShopDB2.ndf', SIZE = 256MB);
ALTER DATABASE ShopDB MODIFY FILE (NAME = ShopDB_Log, SIZE = 1GB);
ALTER DATABASE ShopDB MODIFY NAME = ShopDB_New;
ALTER DATABASE ShopDB SET RECOVERY FULL;
ALTER DATABASE ShopDB SET READ_ONLY;
ALTER DATABASE ShopDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ShopDB SET MULTI_USER;
ALTER DATABASE ShopDB SET COMPATIBILITY_LEVEL = 160;  -- SQL 2022 (170 = SQL 2025)

3.4 Recovery Models

Log truncated at checkpoint Dev/test, read-mostly Log kept until a log backup Bulk operations minimally logged During large bulk loads

3.5 Database States and Options

States: ONLINE, OFFLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT, EMERGENCY

Options: AUTO_CLOSE (keep OFF), AUTO_SHRINK (keep OFF), AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS(_ASYNC), PAGE_VERIFY CHECKSUM, READ_COMMITTED_SNAPSHOT, ACCELERATED_DATABASE_RECOVERY

3.6 Schemas

A schema is a namespace that groups objects and simplifies security.

CREATE SCHEMA Sales AUTHORIZATION dbo;
CREATE SCHEMA HR;
ALTER SCHEMA HR TRANSFER dbo.Employees;   -- move an object
-- Four-part naming: Server.Database.Schema.Object
SELECT * FROM ShopDB.Sales.Orders;

3.7 Dropping, Detaching, Attaching, Snapshots

DROP DATABASE IF EXISTS ShopDB_Old;

EXEC sp_detach_db 'ShopDB';
CREATE DATABASE ShopDB ON (FILENAME = 'D:\Data\ShopDB.mdf'),
                           (FILENAME = 'L:\Log\ShopDB.ldf') FOR ATTACH;

-- Database snapshot (read-only, point-in-time view)
CREATE DATABASE ShopDB_Snap ON (NAME = ShopDB_Data, FILENAME = 'D:\Snap\ShopDB.ss')
AS SNAPSHOT OF ShopDB;
RESTORE DATABASE ShopDB FROM DATABASE_SNAPSHOT = 'ShopDB_Snap';  -- revert

3.8 Collation

Controls sorting and comparison: case sensitivity (CI/CS), accent sensitivity (AI/AS), kana and width, and UTF-8.

SELECT name, collation_name FROM sys.databases;
SELECT * FROM Customers WHERE LastName = 'smith' COLLATE Latin1_General_CS_AS;

3.9 Catalog Views to Explore Metadata

SELECT * FROM sys.databases;
SELECT * FROM sys.master_files;
SELECT * FROM sys.schemas;
SELECT * FROM sys.objects WHERE type = 'U';      -- user tables
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customers';
EXEC sp_helpdb 'ShopDB';
EXEC sp_help 'dbo.Customers';