SQL Server From the Ground Up: The Complete Beginner’s Guide

---

SQL Server is one of the most widely deployed database platforms in the world, yet most documentation assumes the reader is already a DBA. This guide does not make that assumption. It starts from the beginning and covers every major concept a new SQL Server DBA or developer needs to understand: what SQL Server is, every major component, how to configure the server, backups and restores, security, automation, all the key database objects, and how to troubleshoot when something goes wrong. The workshop at the end builds a complete working database from scratch applying every concept from the guide.

1 What SQL Server Is Beginner

SQL Server is a relational database management system (RDBMS) built by Microsoft. It stores data in tables with rows and columns, enforces relationships between those tables, controls who can access the data, and provides a language called Transact-SQL (T-SQL) for querying and manipulating it.

Think of SQL Server as a highly organized, concurrent filing system. Unlike a spreadsheet that one person edits at a time, SQL Server handles thousands of simultaneous read and write operations while keeping data consistent and accurate. It manages transactions, meaning a group of related changes either all succeed together or all fail together, leaving the database in a clean state. This guarantee is called ACID compliance: Atomicity, Consistency, Isolation, and Durability.

2 SQL Server Editions Beginner

Edition	Purpose	Key Limitations
Express	Free, entry-level edition for small databases	10 GB database size limit. No SQL Server Agent. Not suitable for production monitoring or automation.
Developer	Free, full-featured edition for development and testing only	All Enterprise features. Not licensed for production. The correct choice for learning on a personal machine.
Standard	Licensed production edition	No online index operations. Limited memory and CPU. Basic HA features only.
Enterprise	Full-featured licensed production edition	All features enabled. Online index operations, advanced HA, In-Memory OLTP, unlimited memory and CPU.

3 Default vs Named Instances and Collation Beginner

Default vs named instances

Multiple SQL Server instances can be installed on the same physical or virtual machine. The first installation is typically the default instance. Additional installations are named instances with a user-defined name chosen at installation time.

Instance Type	Connection String	Service Name	Port
Default instance	ServerName or . or localhost	MSSQLSERVER	1433 (standard)
Named instance	ServerName\InstanceName	MSSQL$InstanceName	Dynamic port (assigned at startup). SQL Server Browser service required for clients to find the port automatically.

Collation: the most important installation setting

Collation controls three things in SQL Server: the character set (which characters are valid), sort order (how characters are ordered when sorting), and case sensitivity (whether ‘A’ and ‘a’ are considered the same or different). Collation is set at installation time for the server and inherited by new databases unless overridden.

The most commonly used collation in English-language environments is SQL_Latin1_General_CP1_CI_AS. Breaking this name down: CP1 means Code Page 1252 (Western European characters), CI means Case Insensitive (A = a), AS means Accent Sensitive (a does not equal à). A collation ending in CS would be Case Sensitive.

-- Check the current server collation
SELECT SERVERPROPERTY('Collation') AS ServerCollation;

-- Check all database collations on this instance
SELECT name, collation_name FROM sys.databases ORDER BY name;

-- Check collation of specific columns
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

4 The SQL Server Component Family Beginner

SQL Server is a family of components installed separately or together. Understanding what each does prevents confusion about where to configure specific features.

Component	What It Does	Windows Service	Required?
Database Engine	The core. Stores, processes, and secures data. All other components connect to it.	MSSQLSERVER	Always
SQL Server Agent	Automation engine. Runs scheduled jobs, fires alerts, notifies operators. Not in Express edition.	SQLSERVERAGENT	All production environments
SSIS	ETL tool. Moves and transforms data between systems.	MsDtsServer (version varies)	Only for ETL work
SSAS	Analytical engine for multidimensional and tabular BI models.	MSSQLServerOLAPService	Only for BI models
SSRS	Report server hosting paginated reports accessed via browser.	SQLServerReportingServices	Only for hosted reports
Full-Text Search	Linguistic search across large text columns. Better than LIKE for text searching.	MSSQLFDLauncher	Only if using full-text queries
SQL Server Browser	Resolves named instance port numbers for client connections.	SQLBrowser	Required for named instances

5 SQL Server Configuration Manager Beginner

SQL Server Configuration Manager (SSCM) is a separate management tool from SSMS. It manages the SQL Server Windows services and the network protocols SQL Server uses to accept connections. Every DBA uses SSCM regularly. It is found in the Windows Start menu under Microsoft SQL Server, or by running SQLServerManager16.msc (the number matches the SQL Server version) from the Run dialog.

What SQL Server Configuration Manager does

• Start, stop, pause, and restart SQL Server services. The Database Engine, Agent, Browser, and other services are all visible here. Right-click any service to change its state.
• Change service accounts. SQL Server services run under a Windows service account. SSCM is the correct tool to change these accounts because it also updates the permissions SQL Server needs automatically. Never change service accounts through the Windows Services console because that misses those permission updates.
• Enable and disable network protocols. SQL Server accepts connections through TCP/IP, Named Pipes, and Shared Memory. For remote connections, TCP/IP must be enabled. After enabling or disabling any protocol, the SQL Server service must be restarted for the change to take effect.
• Change the TCP port. The default port is 1433 for the default instance. If a different port is needed for security or network reasons, SSCM is where to change it under SQL Server Network Configuration, then Protocols for the instance, then TCP/IP properties.

-- After changing protocols in SSCM and restarting SQL Server,
-- verify which protocols are in use and what port is active
SELECT
    local_net_address,
    local_tcp_port,
    client_net_address,
    auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

-- View all active connections and their protocols
SELECT
    c.session_id,
    c.net_transport,
    c.local_net_address,
    c.local_tcp_port,
    s.login_name
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions    s ON c.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY c.session_id;

6 SQL Server Management Studio: The Primary Tool Beginner

SQL Server Management Studio (SSMS) is the primary graphical tool for managing SQL Server. It connects to any instance, provides a visual tree of all database objects, and includes a query editor for writing T-SQL. Download it free at learn.microsoft.com.

Key SSMS areas

• Object Explorer (left panel): Tree view of every object on the connected server. Expand databases, security, server objects, and management folders here.
• Query Editor (center): Write and execute T-SQL. Ctrl+N opens a new query. F5 executes. Results appear below. Ctrl+D switches output to text mode. Ctrl+Shift+F routes output to a file.
• Activity Monitor: Right-click the server in Object Explorer and select Activity Monitor, or press Ctrl+Alt+A. Shows CPU usage, waits, expensive recent queries, and active processes in near real-time without writing T-SQL. The first monitoring tool to open when something feels slow.
• Template Explorer: Pre-built T-SQL templates for common tasks. View menu then Template Explorer.

Connecting for the first time

When SSMS opens it prompts for connection details. For a local default instance use a period (.) or localhost as the server name. For a named instance use .\InstanceName. Authentication defaults to Windows Authentication which uses the currently logged-in Windows account. After connecting, the Object Explorer shows the server name at the top. Expand it to see databases, security settings, server objects, and management tools.

7 System Databases: master, model, msdb, and tempdb Beginner

SQL Server creates four system databases automatically during installation. They exist on every instance and cannot be deleted.

master

The control center. Stores information about every other database, all server-level logins, linked server configurations, and server-wide settings. If master is corrupted, SQL Server cannot start. Back it up after any server-level configuration change. Never store application data in master.

model

The template for new databases. When CREATE DATABASE runs, SQL Server copies model’s settings and objects to the new database. Model’s size sets the minimum size for any new database. Do not add application objects to model unless every future database should inherit them.

msdb

The operational database for SQL Server Agent and several services. All job definitions, job history, alert configurations, operator contacts, backup history, Database Mail configuration, and replication metadata live in msdb. Back it up regularly. If msdb is lost, all scheduled jobs and backup history are lost with it.

tempdb

The shared scratch pad. Stores temporary tables, sorting operations, hash joins, and many internal work structures. Recreated fresh every time SQL Server restarts. Nothing important should ever be stored here permanently. Best practice is to create multiple tempdb data files equal to the number of CPU cores (up to eight) to reduce allocation contention.

-- View all databases and their state
SELECT name, database_id, state_desc, recovery_model_desc, create_date
FROM sys.databases ORDER BY database_id;

-- Check tempdb file configuration
-- Number of data files should match CPU count (up to 8)
USE tempdb;
SELECT name, type_desc, physical_name, size * 8 / 1024 AS SizeMB
FROM sys.database_files ORDER BY type, file_id;

8 User Databases: Files, Filegroups, and Recovery Models Beginner

Every user database has at minimum two files on disk:

• Data file (.mdf): Stores tables, indexes, and all data. Additional data files use .ndf.
• Log file (.ldf): The transaction log. Records every change. Used for recovery, point-in-time restore, and replication.

Recovery models

Model	Log Behavior	Restore Capability	When to Use
FULL	All operations fully logged. Log grows until backed up with a log backup.	Point-in-time restore to any moment in time. Requires regular log backups.	All production databases where data loss is unacceptable.
BULK_LOGGED	Bulk operations minimally logged. Others fully logged.	Point-in-time restore except during bulk operations.	Temporarily during large data loads, then switch back to FULL.
SIMPLE	Log truncated automatically at each checkpoint. Cannot grow indefinitely.	Restore only to last full or differential backup. No point-in-time recovery.	Development databases, test environments, or read-only reporting databases where some data loss is acceptable.

9 Authentication: Windows Login vs SQL Server Login Beginner

SQL Server supports two authentication modes set during installation and changeable afterward.

Windows Authentication mode: SQL Server trusts Windows to verify identity. The user’s Windows account or a Windows group they belong to must have a SQL Server login. No SQL Server password is required. More secure and recommended for all production environments using Windows accounts.

Mixed mode (SQL Server Authentication): Allows both Windows and SQL Server-specific usernames and passwords stored inside SQL Server itself. Required for applications that cannot use Windows authentication, such as applications on non-Windows platforms or connecting from outside the domain.

-- Check the current authentication mode
-- Returns 1 for Windows-only, 0 for Mixed mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;

-- Check the sa login status
SELECT name, is_disabled, LOGINPROPERTY(name, 'IsLocked') AS IsLocked
FROM sys.sql_logins WHERE name = 'sa';

10 Logins, Users, Schemas, and Roles Beginner

SQL Server security has two layers. The server level controls who can connect to the instance. The database level controls what they can do inside a specific database. Both must be configured.

Logins (server level)

-- Create a Windows Authentication login
CREATE LOGIN [DOMAIN\Username] FROM WINDOWS;

-- Create a SQL Server Authentication login (Mixed mode only)
CREATE LOGIN AppLogin
WITH PASSWORD = 'StrongP@ssw0rd!2026', CHECK_EXPIRATION = ON, CHECK_POLICY = ON;

-- View all logins
SELECT name, type_desc AS LoginType, is_disabled, create_date
FROM sys.server_principals
WHERE type IN ('S','U','G') ORDER BY name;

Database users (database level)

-- Create a database user mapped to an existing login
USE YourDatabase;
CREATE USER AppUser FOR LOGIN AppLogin;

-- View all users in the current database
SELECT name, type_desc, create_date
FROM sys.database_principals WHERE type IN ('S','U','G') ORDER BY name;

Roles

Built-in Role	Grants
db_owner	Full control of the database. Assign sparingly.
db_datareader	SELECT on all tables and views.
db_datawriter	INSERT, UPDATE, DELETE on all tables.
db_ddladmin	Create and modify schema objects. No security management.

-- Add a user to a built-in role
ALTER ROLE db_datareader ADD MEMBER AppUser;

-- Create a custom role with specific permissions
CREATE ROLE SalesReporter;
GRANT SELECT ON dbo.Orders    TO SalesReporter;
GRANT SELECT ON dbo.Customers TO SalesReporter;
ALTER ROLE SalesReporter ADD MEMBER AppUser;

11 Transactions: The Foundation of Data Integrity Beginner

A transaction is a unit of work that must succeed or fail as a complete whole. If a bank transfer debits one account and then fails before crediting the other, the money disappears. A transaction prevents this by guaranteeing that both operations either both commit (complete permanently) or both roll back (undo completely).

SQL Server runs in auto-commit mode by default, meaning every individual statement is its own transaction that commits immediately after execution. When multiple statements need to be treated as one atomic unit, an explicit transaction is required.

-- Explicit transaction pattern
-- Every production stored procedure that modifies data should use this pattern

BEGIN TRANSACTION;      -- starts the transaction
BEGIN TRY

    -- All statements inside the transaction
    UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountID = 1001;
    UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountID = 1002;

    -- If we reach here without error, make the changes permanent
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    -- If any statement failed, undo ALL changes
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Re-raise the error so the caller knows what happened
    THROW;

END CATCH;

-- Check whether there is a transaction open in the current session
-- Returns 0 if no transaction is open, 1 or higher if a transaction is open
SELECT @@TRANCOUNT AS OpenTransactionCount;

Isolation levels

Isolation levels control how transactions interact with each other when reading and writing the same data simultaneously. Higher isolation levels prevent more types of concurrency problems but increase blocking.

Isolation Level	Dirty Reads?	Non-Repeatable Reads?	Common Use
READ UNCOMMITTED	Yes	Yes	Reports that can tolerate slightly stale data and must not block.
READ COMMITTED (default)	No	Yes	Default for most OLTP operations.
REPEATABLE READ	No	No	When a transaction must see consistent data throughout its duration.
SERIALIZABLE	No	No	Strictest. Financial systems requiring complete isolation. High blocking.
READ COMMITTED SNAPSHOT	No	No (uses row versions)	OLTP systems needing high concurrency. Readers never block writers.

-- Set isolation level for the current session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- default

-- Enable Read Committed Snapshot Isolation (RCSI) on a database
-- This is a database-level setting and eliminates most read-write blocking
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

-- Check current database isolation settings
SELECT name, is_read_committed_snapshot_on
FROM sys.databases WHERE name = DB_NAME();

12 SQL Server Error Logs Beginner

SQL Server writes its own activity log called the error log. This is the first place to look when something is wrong. It records startup information, errors, warnings, successful logins (if configured), backup completions, and many other events.

The error log is a text file at the path configured during installation, typically C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG. SQL Server keeps archived logs named ERRORLOG.1, ERRORLOG.2, and so on. A new log is created each time SQL Server starts or when manually cycled.

-- Read the current error log
EXEC xp_readerrorlog 0, 1;

-- Search for specific text in the current log
EXEC xp_readerrorlog 0, 1, N'error';

-- Search for errors in a specific time range
EXEC xp_readerrorlog 0, 1, NULL, NULL, '2026-07-01', '2026-07-15';

-- Read a previous log (1 = previous, 2 = two logs back)
EXEC xp_readerrorlog 1, 1;

-- Force a new log file (useful to start fresh after an issue)
EXEC sp_cycle_errorlog;

-- Write a custom message to the error log
-- Useful to mark when maintenance windows start and end
EXEC xp_logevent 50000, 'Maintenance window started', 'INFORMATIONAL';

Error severities 10 and below are informational. Severities 11 to 16 are user errors applications should handle. Severities 17 to 19 are resource or configuration problems requiring DBA attention. Severities 20 and above are serious and may require a restart. The Windows Application event log also receives copies of the most serious SQL Server errors.

13 Activity Monitor and Server Configuration Beginner

Activity Monitor

Activity Monitor is the first quick-look monitoring tool every SQL Server DBA needs to know. Open it in SSMS by right-clicking the server in Object Explorer and selecting Activity Monitor, or press Ctrl+Alt+A. It refreshes every 10 seconds by default and shows four panels: CPU usage over time, wait statistics by category, recent expensive queries, and active processes. It requires no T-SQL knowledge and gives an immediate picture of what the server is doing right now.

Active Processes panel: each row is one connection. The Head Blocker column identifies which session is causing blocking chains. Right-click any process and select Kill Process to terminate it if necessary, though this should be done with care in production.

sp_configure: server-level settings

SQL Server has dozens of server-wide configuration settings managed through sp_configure. The most important ones for a new DBA are max server memory, MAXDOP, and cost threshold for parallelism. Advanced options require enabling the advanced options view first.

-- Show all configurable server options
EXEC sp_configure;

-- Enable viewing advanced options (required for most settings)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- View all current settings
EXEC sp_configure;

-- Set maximum server memory
-- Default is unlimited (2,147,483,647 MB) which starves the OS
-- Set to approximately 75-80% of total RAM
-- Example for a 32 GB server: leave 6-8 GB for OS
EXEC sp_configure 'max server memory (MB)', 24576;  -- 24 GB for a 32 GB server
RECONFIGURE;

-- Set MAXDOP (max degree of parallelism)
-- Controls max threads for a single parallel query
-- Common starting point: half the logical CPU count up to 8
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Set Cost Threshold for Parallelism
-- Default is 5 (almost every query goes parallel - bad for OLTP)
-- Community best practice for OLTP: 50
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

-- Enable optimize for ad hoc workloads
-- Prevents single-use query plans from filling the plan cache
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- Verify all settings were applied
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'max degree of parallelism',
    'cost threshold for parallelism',
    'optimize for ad hoc workloads'
)
ORDER BY name;

14 SQL Server Agent: Jobs, Alerts, and Operators Beginner

SQL Server Agent automates everything. Three concepts form the foundation: operators (who to notify), jobs (what to run), and alerts (when to trigger a response).

Operators

USE msdb;

EXEC sp_add_operator
    @name           = N'DBA Team',
    @enabled        = 1,
    @email_address  = N'dba@company.com';

Jobs

USE msdb;

-- Create a job
EXEC sp_add_job
    @job_name                   = N'Daily Statistics Update',
    @enabled                    = 1,
    @notify_level_email         = 2,   -- notify on failure only
    @notify_email_operator_name = N'DBA Team';

-- Add a job step
EXEC sp_add_jobstep
    @job_name       = N'Daily Statistics Update',
    @step_name      = N'Update Stats',
    @subsystem      = N'TSQL',
    @database_name  = N'YourDatabase',
    @command        = N'EXEC sp_updatestats;',
    @on_success_action = 1,   -- quit reporting success
    @on_fail_action    = 2;   -- quit reporting failure

-- Create a schedule
EXEC sp_add_schedule
    @schedule_name      = N'Daily 2 AM',
    @freq_type          = 4,   -- daily
    @freq_interval      = 1,   -- every 1 day
    @active_start_time  = 20000;  -- 2:00 AM

-- Attach schedule to job and register it
EXEC sp_attach_schedule @job_name = N'Daily Statistics Update', @schedule_name = N'Daily 2 AM';
EXEC sp_add_jobserver  @job_name = N'Daily Statistics Update', @server_name = N'(LOCAL)';

-- View job history
SELECT
    j.name AS JobName,
    CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' ELSE 'Other' END AS Status,
    msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS RunDateTime,
    jh.run_duration AS DurationHHMMSS
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id AND jh.step_id = 0
ORDER BY RunDateTime DESC;

Alerts

USE msdb;

-- Alert on severity 17+ errors (resource problems requiring DBA attention)
EXEC sp_add_alert
    @name       = N'Severity 17+ Error',
    @enabled    = 1,
    @severity   = 17,
    @message_id = 0;   -- 0 = use severity level, not a specific error number

EXEC sp_add_notification
    @alert_name         = N'Severity 17+ Error',
    @operator_name      = N'DBA Team',
    @notification_method = 1;  -- 1 = email

-- Alert on error 9002 specifically (transaction log full)
EXEC sp_add_alert
    @name       = N'Transaction Log Full - Error 9002',
    @enabled    = 1,
    @message_id = 9002,
    @severity   = 0;  -- 0 = use message_id, not severity

EXEC sp_add_notification
    @alert_name         = N'Transaction Log Full - Error 9002',
    @operator_name      = N'DBA Team',
    @notification_method = 1;

15 Database Mail: Email Notifications Beginner

Database Mail is SQL Server’s built-in email system. It must be configured before SQL Server Agent can send email notifications for job failures or alerts.

-- Step 1: Enable Database Mail
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;     RECONFIGURE;

USE msdb;

-- Step 2: Create a mail account (SMTP connection)
EXEC sysmail_add_account_sp
    @account_name       = N'SQL Server Alerts',
    @email_address      = N'sqlserver@company.com',
    @display_name       = N'SQL Server Monitoring',
    @mailserver_name    = N'smtp.company.com',
    @port               = 587,
    @username           = N'sqlserver@company.com',
    @password           = N'EmailPassword',
    @use_default_credentials = 0,
    @enable_ssl         = 1;

-- Step 3: Create a profile
EXEC sysmail_add_profile_sp
    @profile_name = N'DBA Alerts',
    @description  = N'Profile for DBA alert notifications';

-- Step 4: Link account to profile
EXEC sysmail_add_profileaccount_sp
    @profile_name    = N'DBA Alerts',
    @account_name    = N'SQL Server Alerts',
    @sequence_number = 1;

-- Step 5: Grant public access to the profile
EXEC sysmail_add_principalprofile_sp
    @profile_name   = N'DBA Alerts',
    @principal_name = N'public',
    @is_default     = 1;

-- Step 6: Test
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'DBA Alerts',
    @recipients   = N'dba@company.com',
    @subject      = N'Database Mail Test',
    @body         = N'Database Mail is configured and working.';

-- Check sent status
SELECT TOP 5 sent_date, subject, sent_status
FROM msdb.dbo.sysmail_sentitems ORDER BY sent_date DESC;

16 Service Broker: Asynchronous Messaging Beginner

Service Broker is SQL Server’s built-in asynchronous messaging system. It solves a specific problem: triggering work in another part of the system without waiting for that work to complete, with a guarantee that the message is delivered even if SQL Server restarts in between. Messages are fully transactional: if the sending transaction rolls back, the message is also rolled back.

Service Broker’s most common production use: instead of a trigger doing expensive work synchronously (making every INSERT wait), the trigger sends a Service Broker message and immediately returns. A background process reads the queue and does the expensive work asynchronously.

-- Basic Service Broker setup within one database
USE YourDatabase;

-- Enable Service Broker on the database
ALTER DATABASE YourDatabase SET ENABLE_BROKER;

-- Create message types (define the format of messages)
CREATE MESSAGE TYPE [//App/OrderRequest]  VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//App/OrderResponse] VALIDATION = WELL_FORMED_XML;

-- Create a contract (defines which side sends which message type)
CREATE CONTRACT [//App/OrderProcessing]
(
    [//App/OrderRequest]  SENT BY INITIATOR,
    [//App/OrderResponse] SENT BY TARGET
);

-- Create queues (message storage)
CREATE QUEUE OrderRequestQueue;
CREATE QUEUE OrderResponseQueue;

-- Create services (named endpoints bound to queues)
CREATE SERVICE [//App/OrderRequestService]
    ON QUEUE OrderRequestQueue ([//App/OrderProcessing]);

CREATE SERVICE [//App/OrderResponseService]
    ON QUEUE OrderResponseQueue ([//App/OrderProcessing]);

-- Verify Service Broker is enabled
SELECT name, is_broker_enabled FROM sys.databases WHERE name = DB_NAME();

17 Tables: Structure, Data Types, and Constraints Beginner

A table is the fundamental storage unit in SQL Server. Every piece of data lives in a table. Tables have rows (records) and columns (fields). Each column has a data type that constrains what values it can hold.

Common data types

Type	Use For
INT	Whole numbers -2.1B to 2.1B. Most common for ID columns.
BIGINT	Very large whole numbers. Use when INT range is insufficient.
DECIMAL(p,s)	Exact fixed-point numbers. p=total digits, s=decimal places. Always use for money, never FLOAT.
NVARCHAR(n)	Unicode text. Use for any text that may contain non-English characters.
VARCHAR(n)	Non-Unicode text. Half the storage of NVARCHAR. Only when data is guaranteed to be ASCII.
DATE	Date only (no time). Year, month, day.
DATETIME2	Date and time with high precision. Preferred over DATETIME for all new designs.
BIT	Boolean: 0 or 1. Use for true/false flags.

Constraints

CREATE TABLE dbo.Customers
(
    -- PRIMARY KEY: uniquely identifies each row. Creates a clustered index.
    CustomerID      INT             IDENTITY(1,1)   NOT NULL
        CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED,

    -- NOT NULL: column must always have a value
    FirstName       NVARCHAR(100)                   NOT NULL,
    LastName        NVARCHAR(100)                   NOT NULL,

    -- UNIQUE: no two rows can have the same value
    Email           NVARCHAR(255)                   NOT NULL
        CONSTRAINT UQ_Customers_Email UNIQUE,

    -- DEFAULT: value inserted automatically when none is supplied
    IsActive        BIT                             NOT NULL
        CONSTRAINT DF_Customers_IsActive DEFAULT (1),

    -- CHECK: only values satisfying the condition are accepted
    BirthDate       DATE                            NULL
        CONSTRAINT CK_Customers_BirthDate CHECK (BirthDate > '1900-01-01'),

    CreatedAt       DATETIME2(3)                    NOT NULL
        CONSTRAINT DF_Customers_CreatedAt DEFAULT (SYSUTCDATETIME())
);

-- FOREIGN KEY: enforces referential integrity between tables
CREATE TABLE dbo.Orders
(
    OrderID         INT             IDENTITY(1,1)   NOT NULL
        CONSTRAINT PK_Orders PRIMARY KEY,
    -- Cannot insert an order for a CustomerID that does not exist in Customers
    CustomerID      INT                             NOT NULL
        CONSTRAINT FK_Orders_Customers
        FOREIGN KEY REFERENCES dbo.Customers(CustomerID),
    OrderDate       DATE                            NOT NULL DEFAULT (CAST(GETDATE() AS DATE)),
    TotalAmount     DECIMAL(10,2)                   NOT NULL DEFAULT (0)
        CONSTRAINT CK_Orders_Amount CHECK (TotalAmount >= 0)
);

18 Schemas: Organizing Objects Beginner

A schema is a namespace that groups related database objects. Every object belongs to exactly one schema. The default is dbo. When an object is referenced as dbo.Customers, dbo is the schema and Customers is the object name.

Schemas serve two purposes. Organization: group objects by department or application area (HR.Employees, Finance.Invoices). Security: grant permissions at the schema level so a user gets access to all current and future objects in a schema with a single permission statement.

-- Create schemas for different business areas
CREATE SCHEMA HR      AUTHORIZATION dbo;
CREATE SCHEMA Finance AUTHORIZATION dbo;
CREATE SCHEMA Sales   AUTHORIZATION dbo;

-- Create a table in a specific schema
CREATE TABLE HR.Employees
(
    EmployeeID  INT IDENTITY PRIMARY KEY,
    FirstName   NVARCHAR(100) NOT NULL,
    LastName    NVARCHAR(100) NOT NULL,
    HireDate    DATE          NOT NULL,
    Salary      DECIMAL(10,2) NOT NULL
);

-- Grant a user read access to all objects in the HR schema
-- This covers any future tables added to HR automatically
GRANT SELECT ON SCHEMA::HR TO AppUser;

19 Triggers: Automatic Event Responses Beginner

A trigger is a special stored procedure that executes automatically when a specific event occurs on a table or view. Unlike regular stored procedures that must be called explicitly, triggers fire without any code calling them.

DML triggers fire when INSERT, UPDATE, or DELETE operations occur on a table. They are most commonly used for audit logging (recording who changed what and when), enforcing complex business rules that constraints cannot handle, and maintaining derived data in summary tables.

SQL Server provides two special virtual tables inside trigger scope: inserted contains the new row values (for INSERT and UPDATE), and deleted contains the old row values (for DELETE and UPDATE).

-- Create an audit log table
CREATE TABLE dbo.CustomerAuditLog
(
    AuditID         INT             IDENTITY PRIMARY KEY,
    EventDate       DATETIME2(3)    NOT NULL DEFAULT (SYSUTCDATETIME()),
    EventType       NVARCHAR(10)    NOT NULL,  -- INSERT, UPDATE, DELETE
    CustomerID      INT             NOT NULL,
    ChangedBy       NVARCHAR(128)   NOT NULL DEFAULT (SUSER_SNAME()),
    OldFirstName    NVARCHAR(100)   NULL,
    NewFirstName    NVARCHAR(100)   NULL
);

-- AFTER trigger: fires after the INSERT/UPDATE/DELETE completes
CREATE OR ALTER TRIGGER dbo.trg_Customers_Audit
ON dbo.Customers
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Log inserts
    INSERT INTO dbo.CustomerAuditLog (EventType, CustomerID, NewFirstName)
    SELECT 'INSERT', i.CustomerID, i.FirstName
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM deleted d WHERE d.CustomerID = i.CustomerID);

    -- Log updates
    INSERT INTO dbo.CustomerAuditLog (EventType, CustomerID, OldFirstName, NewFirstName)
    SELECT 'UPDATE', i.CustomerID, d.FirstName, i.FirstName
    FROM inserted i
    JOIN deleted d ON i.CustomerID = d.CustomerID;

    -- Log deletes
    INSERT INTO dbo.CustomerAuditLog (EventType, CustomerID, OldFirstName)
    SELECT 'DELETE', d.CustomerID, d.FirstName
    FROM deleted d
    WHERE NOT EXISTS (SELECT 1 FROM inserted i WHERE i.CustomerID = d.CustomerID);
END;
GO

-- Test the trigger
INSERT INTO dbo.Customers (FirstName, LastName, Email) VALUES ('Test', 'User', 'test@example.com');
UPDATE dbo.Customers SET FirstName = 'Updated' WHERE Email = 'test@example.com';
DELETE FROM dbo.Customers WHERE Email = 'test@example.com';

-- View the audit log
SELECT * FROM dbo.CustomerAuditLog ORDER BY EventDate;

20 Indexes: Clustered and Non-Clustered Beginner

An index is a data structure that allows SQL Server to find rows quickly without scanning every row in the table. Without indexes, every query that filters rows must read the entire table. With the right indexes, SQL Server jumps directly to the matching rows.

Clustered index: Physically orders the table data on disk according to the index key. Only one per table because data can only be physically ordered one way. The PRIMARY KEY constraint creates a clustered index by default. A table without a clustered index is called a heap.

Non-clustered index: A separate structure containing a sorted copy of the indexed column values and row pointers back to the actual table data. A table can have up to 999 non-clustered indexes. They allow fast lookups on columns other than the clustered key. Every non-clustered index must be maintained on every INSERT, UPDATE, and DELETE, which is why over-indexing hurts write performance.

CREATE TABLE dbo.Products
(
    ProductID   INT IDENTITY PRIMARY KEY,   -- clustered index created automatically
    CategoryID  INT           NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    Price       DECIMAL(10,2) NOT NULL,
    IsActive    BIT           NOT NULL DEFAULT (1)
);

-- Non-clustered index for queries filtering by category
-- INCLUDE adds columns to the index leaf so SQL Server does not need to go back to the table
CREATE NONCLUSTERED INDEX IX_Products_CategoryID
ON dbo.Products (CategoryID)
INCLUDE (ProductName, Price);

-- Filtered index: only indexes active products
-- Smaller and faster than indexing all rows
CREATE NONCLUSTERED INDEX IX_Products_ActiveByName
ON dbo.Products (ProductName)
WHERE IsActive = 1;

-- View all indexes on the Products table
SELECT
    i.name          AS IndexName,
    i.type_desc     AS IndexType,
    i.is_primary_key,
    i.is_unique
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Products')
ORDER BY i.index_id;

21 Stored Procedures, Functions, and Views Beginner

Stored procedures

Named, compiled blocks of T-SQL that accept parameters, execute any SQL, and return results. The standard way to expose database operations to applications.

CREATE OR ALTER PROCEDURE dbo.usp_AddCustomer
    @FirstName      NVARCHAR(100),
    @LastName       NVARCHAR(100),
    @Email          NVARCHAR(255),
    @NewCustomerID  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    IF @Email NOT LIKE '%@%.%'
        THROW 50001, 'Invalid email address.', 1;
    IF EXISTS (SELECT 1 FROM dbo.Customers WHERE Email = @Email)
        THROW 50002, 'Email already exists.', 1;
    INSERT INTO dbo.Customers (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email);
    SET @NewCustomerID = SCOPE_IDENTITY();
END;
GO

DECLARE @ID INT;
EXEC dbo.usp_AddCustomer 'Jane', 'Smith', 'jane@example.com', @ID OUTPUT;
PRINT 'New ID: ' + CAST(@ID AS VARCHAR);

Functions

-- Scalar function: returns a single value
CREATE OR ALTER FUNCTION dbo.fn_AgeInYears (@BirthDate DATE)
RETURNS INT AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE())
           - CASE WHEN FORMAT(GETDATE(),'MMdd') < FORMAT(@BirthDate,'MMdd') THEN 1 ELSE 0 END;
END;
GO

-- Inline table-valued function: returns a table (use like a view with parameters)
CREATE OR ALTER FUNCTION dbo.fn_CustomerOrders (@CustomerID INT)
RETURNS TABLE AS
RETURN
(
    SELECT OrderID, OrderDate, TotalAmount
    FROM dbo.Orders WHERE CustomerID = @CustomerID
);
GO

SELECT * FROM dbo.fn_CustomerOrders(1) ORDER BY OrderDate DESC;

Views

-- A view is a stored SELECT query that behaves like a table
CREATE OR ALTER VIEW dbo.vw_CustomerSummary AS
SELECT
    c.CustomerID,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    c.Email,
    COUNT(o.OrderID)   AS TotalOrders,
    SUM(o.TotalAmount) AS TotalSpent
FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE c.IsActive = 1
GROUP BY c.CustomerID, c.FirstName, c.LastName, c.Email;
GO

SELECT * FROM dbo.vw_CustomerSummary WHERE TotalSpent > 500 ORDER BY TotalSpent DESC;

22 DBCC CHECKDB and Database Integrity Beginner

DBCC CHECKDB is the most important database maintenance command for detecting data corruption. It checks the logical and physical integrity of all objects in a database. On production servers, run it at least weekly. Never go more than a week without knowing the integrity status of a production database.

Data corruption in SQL Server can come from disk failures, storage firmware bugs, memory errors, or I/O subsystem problems. SQL Server does not always detect corruption immediately when it writes data. DBCC CHECKDB reads every page and verifies the checksums and structural integrity, surfacing corruption before it causes data loss or application failures.

-- Run a full integrity check on a database
-- On large databases this can take significant time and I/O
-- Schedule during a maintenance window for production
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS;
-- NO_INFOMSGS suppresses informational messages, shows only errors

-- Run with physical only for a faster check (skips logical checks)
-- Use when a quick I/O-level check is needed
DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY, NO_INFOMSGS;

-- Check the last known good DBCC CHECKDB date for all databases
-- This is a critical piece of monitoring data
SELECT
    d.name          AS DatabaseName,
    DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS LastGoodCheckDB,
    DATEDIFF(DAY,
        CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME),
        GETDATE()
    )               AS DaysSinceLastCheck
FROM sys.databases d
WHERE d.database_id > 4   -- user databases only
  AND d.state_desc = 'ONLINE'
ORDER BY DaysSinceLastCheck DESC NULLS LAST;

-- Check a single table instead of the entire database (faster for investigation)
DBCC CHECKTABLE ('dbo.Customers') WITH NO_INFOMSGS;

-- Check all system tables in the current database
DBCC CHECKALLOC WITH NO_INFOMSGS;   -- checks allocation structures
DBCC CHECKCATALOG WITH NO_INFOMSGS; -- checks catalog consistency

23 Backup and Restore: The Most Critical DBA Skill Beginner

A database backup is a copy of the database at a point in time that can be used to restore the database if data is lost, corrupted, or the server fails. Knowing how to take backups and restore from them is the single most critical DBA skill. Without working backups, everything else is irrelevant.

The three backup types

Backup Type	What It Captures	Frequency	File Size
Full backup	The entire database at the moment of the backup. Self-contained and the starting point for any restore.	Weekly for most databases. Daily for critical databases.	Largest. Size of the entire database.
Differential backup	All changes since the last full backup. Smaller and faster than a full backup. Requires the last full backup to restore.	Daily if full backups are weekly.	Medium. Grows larger as more changes accumulate since the last full.
Transaction log backup	All transaction log records since the last log backup. Only available in FULL and BULK_LOGGED recovery models. Enables point-in-time restore.	Every 15 to 60 minutes for production databases. Must be taken regularly or the log file grows indefinitely.	Smallest. Only captures changes since the last log backup.

Taking backups

-- Full backup
-- Replace the path with an actual backup location
BACKUP DATABASE YourDatabase
TO DISK = N'D:\Backups\YourDatabase_Full_20260715.bak'
WITH COMPRESSION,           -- compresses the backup file (recommended)
     CHECKSUM,              -- verifies data integrity during backup
     STATS = 10;            -- shows progress every 10%

-- Differential backup (requires a recent full backup to exist)
BACKUP DATABASE YourDatabase
TO DISK = N'D:\Backups\YourDatabase_Diff_20260715_1200.bak'
WITH DIFFERENTIAL,
     COMPRESSION,
     CHECKSUM,
     STATS = 10;

-- Transaction log backup (only works on FULL or BULK_LOGGED recovery model)
BACKUP LOG YourDatabase
TO DISK = N'D:\Backups\YourDatabase_Log_20260715_1400.trn'
WITH COMPRESSION,
     CHECKSUM,
     STATS = 10;

-- Verify a backup file before trusting it
RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\YourDatabase_Full_20260715.bak'
WITH CHECKSUM;
-- Returns: "The backup set on file 1 is valid."

Restoring a database

-- Restore sequence: Full + Differential + Log chain
-- Each restore uses WITH NORECOVERY until the final step
-- The final step uses WITH RECOVERY to bring the database online

-- Step 1: Restore the full backup (WITH NORECOVERY keeps the database in restoring state)
RESTORE DATABASE YourDatabase
FROM DISK = N'D:\Backups\YourDatabase_Full_20260715.bak'
WITH NORECOVERY,
     STATS = 10;

-- Step 2: Restore the most recent differential backup (if one exists)
RESTORE DATABASE YourDatabase
FROM DISK = N'D:\Backups\YourDatabase_Diff_20260715_1200.bak'
WITH NORECOVERY,
     STATS = 10;

-- Step 3: Restore each transaction log backup in order
RESTORE LOG YourDatabase
FROM DISK = N'D:\Backups\YourDatabase_Log_20260715_1400.trn'
WITH NORECOVERY;

-- Repeat step 3 for each subsequent log backup in chronological order

-- Step 4: Bring the database online (final step only)
-- After this step the database accepts connections
RESTORE DATABASE YourDatabase WITH RECOVERY;

Point-in-time restore

-- Restore to a specific moment in time
-- Useful when recovering from accidental data deletion at a known time
-- The STOPAT time must be within the range of the log backups being applied

RESTORE DATABASE YourDatabase
FROM DISK = N'D:\Backups\YourDatabase_Full_20260715.bak'
WITH NORECOVERY, STATS = 10;

RESTORE LOG YourDatabase
FROM DISK = N'D:\Backups\YourDatabase_Log_20260715_1400.trn'
WITH NORECOVERY,
     STOPAT = '2026-07-15 13:30:00';  -- stop at this exact moment

RESTORE DATABASE YourDatabase WITH RECOVERY;
-- Database is now in the state it was at 1:30 PM on July 15

Viewing backup history

-- SQL Server stores all backup history in msdb
-- This query shows the last backup for each database
SELECT
    d.name                          AS DatabaseName,
    bs.type                         AS BackupType,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END                             AS BackupTypeName,
    bs.backup_start_date,
    bs.backup_finish_date,
    CAST(bs.backup_size / 1048576.0 AS DECIMAL(10,1)) AS SizeMB,
    bmf.physical_device_name        AS BackupFile
FROM sys.databases                  d
LEFT JOIN msdb.dbo.backupset        bs
    ON d.name = bs.database_name
LEFT JOIN msdb.dbo.backupmediafamily bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE bs.backup_start_date = (
    SELECT MAX(bs2.backup_start_date)
    FROM msdb.dbo.backupset bs2
    WHERE bs2.database_name = d.name
    AND bs2.type = bs.type
)
AND d.database_id > 4               -- user databases only
ORDER BY d.name, bs.type;

24 Workshop: Build a Complete Database from Scratch Beginner

This workshop applies every concept from the guide in sequence. Run each step in SSMS in order. Each script builds on the previous one.

Step 1: Create the database with proper file configuration

USE master;
GO

-- Create the database with separate data and log files
-- Adjust drive letters to match available drives
CREATE DATABASE WorkshopDB
ON PRIMARY
(
    NAME        = N'WorkshopDB',
    FILENAME    = N'C:\SQLData\WorkshopDB.mdf',
    SIZE        = 64MB,
    MAXSIZE     = UNLIMITED,
    FILEGROWTH  = 64MB
)
LOG ON
(
    NAME        = N'WorkshopDB_log',
    FILENAME    = N'C:\SQLLogs\WorkshopDB_log.ldf',
    SIZE        = 16MB,
    MAXSIZE     = 2048MB,
    FILEGROWTH  = 64MB
);
GO

ALTER DATABASE WorkshopDB SET RECOVERY FULL;
ALTER DATABASE WorkshopDB SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE WorkshopDB SET READ_COMMITTED_SNAPSHOT ON;

SELECT name, state_desc, recovery_model_desc, compatibility_level
FROM sys.databases WHERE name = 'WorkshopDB';
GO

Step 2: Create schemas

USE WorkshopDB;
GO

CREATE SCHEMA Sales     AUTHORIZATION dbo;
CREATE SCHEMA Inventory AUTHORIZATION dbo;
CREATE SCHEMA Reports   AUTHORIZATION dbo;
GO

Step 3: Create tables with full constraint set

USE WorkshopDB;
GO

CREATE TABLE Inventory.Categories
(
    CategoryID      INT IDENTITY PRIMARY KEY,
    CategoryName    NVARCHAR(100) NOT NULL CONSTRAINT UQ_Categories_Name UNIQUE,
    Description     NVARCHAR(500) NULL
);

CREATE TABLE Inventory.Products
(
    ProductID       INT IDENTITY PRIMARY KEY,
    CategoryID      INT NOT NULL
        CONSTRAINT FK_Products_Categories FOREIGN KEY REFERENCES Inventory.Categories(CategoryID),
    ProductName     NVARCHAR(200) NOT NULL,
    UnitPrice       DECIMAL(10,2) NOT NULL CONSTRAINT CK_Products_Price CHECK (UnitPrice >= 0),
    StockQuantity   INT NOT NULL DEFAULT (0) CONSTRAINT CK_Products_Stock CHECK (StockQuantity >= 0),
    IsActive        BIT NOT NULL DEFAULT (1),
    CreatedAt       DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME())
);

CREATE TABLE Sales.Customers
(
    CustomerID      INT IDENTITY PRIMARY KEY,
    FirstName       NVARCHAR(100) NOT NULL,
    LastName        NVARCHAR(100) NOT NULL,
    Email           NVARCHAR(255) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,
    Phone           NVARCHAR(20) NULL,
    IsActive        BIT NOT NULL DEFAULT (1),
    CreatedAt       DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME())
);

CREATE TABLE Sales.Orders
(
    OrderID         INT IDENTITY PRIMARY KEY,
    CustomerID      INT NOT NULL
        CONSTRAINT FK_Orders_Customers FOREIGN KEY REFERENCES Sales.Customers(CustomerID),
    OrderDate       DATE NOT NULL DEFAULT (CAST(GETDATE() AS DATE)),
    Status          NVARCHAR(20) NOT NULL DEFAULT ('Pending')
        CONSTRAINT CK_Orders_Status CHECK (Status IN ('Pending','Processing','Shipped','Delivered','Cancelled')),
    TotalAmount     DECIMAL(10,2) NOT NULL DEFAULT (0)
);

CREATE TABLE Sales.OrderItems
(
    OrderItemID     INT IDENTITY PRIMARY KEY,
    OrderID         INT NOT NULL CONSTRAINT FK_OrderItems_Orders FOREIGN KEY REFERENCES Sales.Orders(OrderID),
    ProductID       INT NOT NULL CONSTRAINT FK_OrderItems_Products FOREIGN KEY REFERENCES Inventory.Products(ProductID),
    Quantity        INT NOT NULL CONSTRAINT CK_Items_Qty CHECK (Quantity > 0),
    UnitPrice       DECIMAL(10,2) NOT NULL,
    LineTotal       AS (Quantity * UnitPrice) PERSISTED  -- computed column
);

-- Audit log table
CREATE TABLE Sales.OrderAuditLog
(
    AuditID     INT IDENTITY PRIMARY KEY,
    EventDate   DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME()),
    EventType   NVARCHAR(10) NOT NULL,
    OrderID     INT NOT NULL,
    ChangedBy   NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()),
    OldStatus   NVARCHAR(20) NULL,
    NewStatus   NVARCHAR(20) NULL
);
GO

Step 4: Create indexes

USE WorkshopDB;
GO

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Sales.Orders (CustomerID) INCLUDE (OrderDate, Status, TotalAmount);

CREATE NONCLUSTERED INDEX IX_OrderItems_OrderID
ON Sales.OrderItems (OrderID) INCLUDE (ProductID, Quantity, UnitPrice);

CREATE NONCLUSTERED INDEX IX_Products_CategoryID
ON Inventory.Products (CategoryID) WHERE IsActive = 1;

CREATE NONCLUSTERED INDEX IX_Customers_Email
ON Sales.Customers (Email);
GO

Step 5: Create a trigger for order status auditing

USE WorkshopDB;
GO

CREATE OR ALTER TRIGGER Sales.trg_Orders_StatusAudit
ON Sales.Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- Only log when the Status column actually changed
    INSERT INTO Sales.OrderAuditLog (EventType, OrderID, OldStatus, NewStatus)
    SELECT 'UPDATE', i.OrderID, d.Status, i.Status
    FROM inserted i
    JOIN deleted  d ON i.OrderID = d.OrderID
    WHERE i.Status <> d.Status;
END;
GO

Step 6: Insert sample data and test the trigger

USE WorkshopDB;
GO

INSERT INTO Inventory.Categories (CategoryName) VALUES ('Electronics'),('Books'),('Clothing');
INSERT INTO Inventory.Products (CategoryID, ProductName, UnitPrice, StockQuantity)
VALUES (1,'Wireless Mouse',29.99,150),(2,'SQL Server Guide',54.99,50),(1,'USB Hub',44.99,75);
INSERT INTO Sales.Customers (FirstName, LastName, Email)
VALUES ('Alice','Johnson','alice@example.com'),('Bob','Williams','bob@example.com');
INSERT INTO Sales.Orders (CustomerID) VALUES (1);
INSERT INTO Sales.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1, 1, 2, 29.99),(1, 2, 1, 54.99);
UPDATE Sales.Orders SET TotalAmount = (SELECT SUM(LineTotal) FROM Sales.OrderItems WHERE OrderID = 1) WHERE OrderID = 1;

-- Update order status and watch the trigger fire
UPDATE Sales.Orders SET Status = 'Processing' WHERE OrderID = 1;
UPDATE Sales.Orders SET Status = 'Shipped'    WHERE OrderID = 1;

SELECT * FROM Sales.OrderAuditLog ORDER BY EventDate;
GO

Step 7: Create stored procedure and view

USE WorkshopDB;
GO

CREATE OR ALTER PROCEDURE Sales.usp_PlaceOrder
    @CustomerID INT, @ProductID INT, @Quantity INT, @NewOrderID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Price DECIMAL(10,2), @Stock INT;
    SELECT @Price = UnitPrice, @Stock = StockQuantity
    FROM Inventory.Products WHERE ProductID = @ProductID AND IsActive = 1;
    IF @Price IS NULL THROW 50001, 'Product not found.', 1;
    IF @Stock < @Quantity THROW 50002, 'Insufficient stock.', 1;
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Sales.Orders (CustomerID) VALUES (@CustomerID);
        SET @NewOrderID = SCOPE_IDENTITY();
        INSERT INTO Sales.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (@NewOrderID, @ProductID, @Quantity, @Price);
        UPDATE Sales.Orders SET TotalAmount = @Quantity * @Price WHERE OrderID = @NewOrderID;
        UPDATE Inventory.Products SET StockQuantity -= @Quantity WHERE ProductID = @ProductID;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

CREATE OR ALTER VIEW Reports.vw_OrderSummary AS
SELECT
    o.OrderID, c.FirstName + ' ' + c.LastName AS CustomerName,
    o.OrderDate, o.Status, p.ProductName,
    oi.Quantity, oi.UnitPrice, oi.LineTotal, o.TotalAmount AS OrderTotal
FROM Sales.Orders o
JOIN Sales.Customers    c   ON o.CustomerID  = c.CustomerID
JOIN Sales.OrderItems   oi  ON o.OrderID     = oi.OrderID
JOIN Inventory.Products p   ON oi.ProductID  = p.ProductID;
GO

DECLARE @OID INT;
EXEC Sales.usp_PlaceOrder 2, 3, 1, @OID OUTPUT;
PRINT 'New Order: ' + CAST(@OID AS VARCHAR);
SELECT * FROM Reports.vw_OrderSummary ORDER BY OrderID;
GO

Step 8: Take a backup and verify it

-- Take a full backup of the workshop database
BACKUP DATABASE WorkshopDB
TO DISK = N'C:\SQLBackups\WorkshopDB_Full_Workshop.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Verify the backup is readable
RESTORE VERIFYONLY
FROM DISK = N'C:\SQLBackups\WorkshopDB_Full_Workshop.bak'
WITH CHECKSUM;

-- Check the backup history in msdb
SELECT
    database_name,
    CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END AS BackupType,
    backup_start_date,
    CAST(backup_size / 1048576.0 AS DECIMAL(10,1)) AS SizeMB
FROM msdb.dbo.backupset
WHERE database_name = 'WorkshopDB'
ORDER BY backup_start_date DESC;
GO

Step 9: Run an integrity check

-- Verify database integrity
DBCC CHECKDB ('WorkshopDB') WITH NO_INFOMSGS;
-- No output = no errors = database is clean

-- Check the last good CHECKDB date
SELECT DATABASEPROPERTYEX('WorkshopDB', 'LastGoodCheckDbTime') AS LastGoodCheckDB;
GO

Step 10: Audit the environment

USE WorkshopDB;
GO

-- All objects created in the workshop
SELECT s.name AS Schema, o.name AS Object, o.type_desc AS Type
FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0 ORDER BY s.name, o.type_desc, o.name;

-- Row counts
SELECT SCHEMA_NAME(t.schema_id) AS Schema, t.name AS TableName, p.rows AS Rows
FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
WHERE t.is_ms_shipped = 0 ORDER BY Schema, TableName;

-- Check error log for any workshop-related messages
EXEC xp_readerrorlog 0, 1, N'WorkshopDB';
GO

References and Next Steps

• Microsoft Docs: System Databases
• Microsoft Docs: SQL Server Configuration Manager
• Microsoft Docs: Backup Overview (SQL Server)
• Microsoft Docs: Restore and Recovery Overview
• Microsoft Docs: DBCC CHECKDB (Transact-SQL)
• Microsoft Docs: DML Triggers
• Microsoft Docs: Transactions (Transact-SQL)
• Microsoft Docs: Server Configuration Options (sp_configure)
• Microsoft Docs: Getting Started with Database Engine Permissions
• Microsoft Docs: Indexes
• SQLYARD: SQL Server Instance Setup and Best Practices
• SQLYARD: SQL Server Health Check Toolkit
• SQLYARD: SQL Server Agent Jobs Complete Guide
• SQLYARD: SQL Server Index Tuning Guide