Microsoft SQL Server Cheatsheet

Microsoft SQL Server - Enterprise Database Platform

Microsoft SQL Server is a relational database management system developed by Microsoft. It's a comprehensive database platform that provides enterprise-grade features for data storage, processing, and analytics with strong integration with Microsoft ecosystem.

📋 Copy All Commands 📄 Generate PDF

Table of Contents

• Installation
• Basic Commands
• Database Operations
• Schema and User Management
• Table Operations
• Data Types
• CRUD Operations
• Query Operations
• Indexes
• Views
• Stored Procedures
• Functions
• Triggers
• Transactions
• T-SQL Programming
• Backup and Recovery
• Performance Tuning
• Monitoring
• Security
• Best Practices

Installation

SQL Server Installation (Windows)

# Download SQL Server from Microsoft website
# https://www.microsoft.com/en-us/sql-server/sql-server-downloads

# Silent installation example
Setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQLENGINE,TOOLS /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /TCPENABLED=1 /SECURITYMODE=SQL /SAPWD="YourStrongPassword"

# Install SQL Server Management Studio (SSMS)
# Download from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

# PowerShell installation
choco install sql-server-management-studio

SQL Server on Linux

# Add Microsoft repository (Ubuntu)
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

# Install SQL Server
sudo apt-get update
sudo apt-get install -y mssql-server

# Configure SQL Server
sudo /opt/mssql/bin/mssql-conf setup

# Install SQL Server command-line tools
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

# Add to PATH
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# Start SQL Server
sudo systemctl start mssql-server
sudo systemctl enable mssql-server

Docker Installation

# Pull SQL Server image
docker pull mcr.microsoft.com/mssql/server:2019-latest

# Run SQL Server container
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrongPassword123" \
   -p 1433:1433 --name sql-server \
   -d mcr.microsoft.com/mssql/server:2019-latest

# Connect to SQL Server
docker exec -it sql-server /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P "YourStrongPassword123"

# Docker Compose
cat > docker-compose.yml << EOF
version: '3.8'
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: sqlserver
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=YourStrongPassword123
      - MSSQL_PID=Express
    ports:
      - "1433:1433"
    volumes:
      - sqlserver_data:/var/opt/mssql

volumes:
  sqlserver_data:
EOF

docker-compose up -d

Basic Commands

Connecting to SQL Server

# Using sqlcmd
sqlcmd -S server_name -U username -P password
sqlcmd -S localhost -U sa -P password
sqlcmd -S server_name -E  # Windows Authentication

# Using PowerShell
Import-Module SqlServer
Invoke-Sqlcmd -ServerInstance "localhost" -Database "master" -Query "SELECT @@VERSION"

# Connection string examples
# SQL Server Authentication
Server=localhost;Database=master;User Id=sa;Password=password;

# Windows Authentication
Server=localhost;Database=master;Integrated Security=true;

# Named instance
Server=localhost\SQLEXPRESS;Database=master;Integrated Security=true;

Basic Information Commands

-- SQL Server version
SELECT @@VERSION;

-- Current database
SELECT DB_NAME();

-- Current user
SELECT SYSTEM_USER, USER_NAME();

-- Server information
SELECT 
    SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('ProductVersion') AS Version,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition;

-- Show databases
SELECT name FROM sys.databases;

-- Show tables in current database
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

-- Show columns of a table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName';

-- Current date and time
SELECT GETDATE();

-- Exit sqlcmd
EXIT
QUIT

Database Operations

Creating and Managing Databases

-- Create database
CREATE DATABASE MyDatabase;

-- Create database with options
CREATE DATABASE MyDatabase
ON 
( NAME = 'MyDatabase_Data',
  FILENAME = 'C:\Data\MyDatabase.mdf',
  SIZE = 100MB,
  MAXSIZE = 1GB,
  FILEGROWTH = 10MB )
LOG ON 
( NAME = 'MyDatabase_Log',
  FILENAME = 'C:\Data\MyDatabase.ldf',
  SIZE = 10MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 10% );

-- Use database
USE MyDatabase;

-- Alter database
ALTER DATABASE MyDatabase 
MODIFY FILE (NAME = 'MyDatabase_Data', SIZE = 200MB);

-- Set database options
ALTER DATABASE MyDatabase SET RECOVERY FULL;
ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS ON;

-- Drop database
DROP DATABASE MyDatabase;

-- Show database information
SELECT 
    name,
    database_id,
    create_date,
    collation_name,
    state_desc,
    recovery_model_desc
FROM sys.databases;

-- Show database files
SELECT 
    name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    max_size,
    growth,
    type_desc
FROM sys.database_files;

Backup and Restore

-- Full backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT, INIT, COMPRESSION;

-- Differential backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- Transaction log backup
BACKUP LOG MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Log.trn';

-- Restore database
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH NORECOVERY;

RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH RECOVERY;

-- Point-in-time restore
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH STOPAT = '2023-12-01 14:30:00', RECOVERY;

Schema and User Management

Creating Logins and Users

-- Create SQL Server login
CREATE LOGIN app_user WITH PASSWORD = 'StrongPassword123';

-- Create Windows login
CREATE LOGIN [DOMAIN\username] FROM WINDOWS;

-- Create user in database
USE MyDatabase;
CREATE USER app_user FOR LOGIN app_user;

-- Create user with default schema
CREATE USER app_user FOR LOGIN app_user WITH DEFAULT_SCHEMA = dbo;

-- Alter user
ALTER USER app_user WITH DEFAULT_SCHEMA = app_schema;

-- Drop user and login
DROP USER app_user;
DROP LOGIN app_user;

-- Show logins
SELECT name, type_desc, create_date, modify_date, is_disabled
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G');

-- Show users in current database
SELECT name, type_desc, create_date, default_schema_name
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G');

Permissions and Roles

-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employees TO app_user;
GRANT EXECUTE ON dbo.GetEmployeeInfo TO app_user;
GRANT SELECT ON SCHEMA::dbo TO app_user;

-- Deny permissions
DENY DELETE ON dbo.Employees TO app_user;

-- Revoke permissions
REVOKE INSERT ON dbo.Employees FROM app_user;

-- Create custom role
CREATE ROLE app_role;

-- Add permissions to role
GRANT SELECT, INSERT, UPDATE ON dbo.Employees TO app_role;

-- Add user to role
ALTER ROLE app_role ADD MEMBER app_user;

-- Built-in database roles
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
ALTER ROLE db_owner ADD MEMBER app_user;

-- Server roles
ALTER SERVER ROLE sysadmin ADD MEMBER app_user;
ALTER SERVER ROLE dbcreator ADD MEMBER app_user;

-- Show permissions
SELECT 
    p.permission_name,
    p.state_desc,
    pr.name AS principal_name,
    o.name AS object_name
FROM sys.database_permissions p
JOIN sys.objects o ON p.major_id = o.object_id
JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id;

Schemas

-- Create schema
CREATE SCHEMA sales AUTHORIZATION dbo;

-- Transfer object to schema
ALTER SCHEMA sales TRANSFER dbo.Orders;

-- Drop schema
DROP SCHEMA sales;

-- Show schemas
SELECT name, schema_id, principal_id FROM sys.schemas;

Table Operations

Creating Tables

-- Basic table creation
CREATE TABLE Employees (
    EmployeeID int IDENTITY(1,1) PRIMARY KEY,
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL,
    Email nvarchar(100) UNIQUE,
    HireDate date DEFAULT GETDATE(),
    Salary decimal(10,2),
    DepartmentID int,
    CreatedDate datetime2 DEFAULT GETDATE()
);

-- Table with constraints
CREATE TABLE Orders (
    OrderID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate datetime2 DEFAULT GETDATE(),
    TotalAmount decimal(10,2) CHECK (TotalAmount > 0),
    Status nvarchar(20) DEFAULT 'Pending' CHECK (Status IN ('Pending', 'Processing', 'Completed', 'Cancelled')),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Table with computed column
CREATE TABLE Products (
    ProductID int IDENTITY(1,1) PRIMARY KEY,
    ProductName nvarchar(100) NOT NULL,
    UnitPrice decimal(10,2) NOT NULL,
    Quantity int NOT NULL,
    TotalValue AS (UnitPrice * Quantity) PERSISTED
);

-- Temporary table
CREATE TABLE #TempEmployees (
    EmployeeID int,
    FullName nvarchar(100)
);

-- Table variable
DECLARE @EmployeeTable TABLE (
    EmployeeID int,
    FullName nvarchar(100)
);

-- Partitioned table
CREATE PARTITION FUNCTION SalesDateRange (datetime2)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01');

CREATE PARTITION SCHEME SalesDateScheme
AS PARTITION SalesDateRange
TO (Sales2021, Sales2022, Sales2023, Sales2024);

CREATE TABLE Sales (
    SaleID int IDENTITY(1,1),
    SaleDate datetime2,
    Amount decimal(10,2),
    CustomerID int
) ON SalesDateScheme(SaleDate);

Modifying Tables

-- Add column
ALTER TABLE Employees ADD MiddleName nvarchar(50);

-- Modify column
ALTER TABLE Employees ALTER COLUMN Salary decimal(12,2);

-- Drop column
ALTER TABLE Employees DROP COLUMN MiddleName;

-- Add constraint
ALTER TABLE Employees ADD CONSTRAINT CK_Salary CHECK (Salary > 0);

-- Drop constraint
ALTER TABLE Employees DROP CONSTRAINT CK_Salary;

-- Add foreign key
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers 
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

-- Drop foreign key
ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Customers;

-- Rename table
EXEC sp_rename 'OldTableName', 'NewTableName';

-- Rename column
EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';

-- Drop table
DROP TABLE Employees;

Table Information

-- Describe table structure
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees';

-- Show constraints
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = 'Employees';

-- Show indexes
SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyOrdinal
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Employees')
ORDER BY i.name, ic.key_ordinal;

-- Table size information
SELECT 
    t.name AS TableName,
    p.rows AS RowCount,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = 'Employees'
GROUP BY t.name, p.rows;

Data Types

Numeric Data Types

-- Integer types
bit                 -- 0, 1, or NULL
tinyint             -- 0 to 255
smallint            -- -32,768 to 32,767
int                 -- -2,147,483,648 to 2,147,483,647
bigint              -- -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Decimal types
decimal(p,s)        -- Fixed precision and scale
numeric(p,s)        -- Synonym for decimal
money               -- -922,337,203,685,477.5808 to 922,337,203,685,477.5807
smallmoney          -- -214,748.3648 to 214,748.3647

-- Floating point
float(n)            -- Floating precision number
real                -- 4-byte floating point

-- Examples
CREATE TABLE NumericExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Price decimal(10,2),
    Quantity int,
    IsActive bit,
    LargeNumber bigint,
    FloatValue float,
    MoneyValue money
);

String Data Types

-- Character types
char(n)             -- Fixed-length non-Unicode string
varchar(n)          -- Variable-length non-Unicode string
varchar(max)        -- Variable-length non-Unicode string (up to 2GB)
nchar(n)            -- Fixed-length Unicode string
nvarchar(n)         -- Variable-length Unicode string
nvarchar(max)       -- Variable-length Unicode string (up to 2GB)
text                -- Variable-length non-Unicode string (deprecated)
ntext               -- Variable-length Unicode string (deprecated)

-- Binary types
binary(n)           -- Fixed-length binary data
varbinary(n)        -- Variable-length binary data
varbinary(max)      -- Variable-length binary data (up to 2GB)
image               -- Variable-length binary data (deprecated)

-- Examples
CREATE TABLE StringExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Code char(5),
    Name nvarchar(100),
    Description nvarchar(max),
    BinaryData varbinary(max)
);

Date and Time Data Types

-- Date and time types
date                -- Date only (0001-01-01 to 9999-12-31)
time                -- Time only (00:00:00.0000000 to 23:59:59.9999999)
datetime            -- Date and time (1753-01-01 to 9999-12-31)
datetime2           -- Date and time with higher precision
smalldatetime       -- Date and time with minute precision
datetimeoffset      -- Date and time with timezone offset

-- Examples
CREATE TABLE DateTimeExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    BirthDate date,
    StartTime time,
    CreatedDateTime datetime,
    PreciseDateTime datetime2(7),
    SmallDateTime smalldatetime,
    UTCDateTime datetimeoffset
);

-- Insert examples
INSERT INTO DateTimeExamples VALUES (
    '1990-05-15',
    '09:30:00',
    '2023-12-01 14:30:25',
    '2023-12-01 14:30:25.1234567',
    '2023-12-01 14:30',
    '2023-12-01 14:30:25 +05:30'
);

Other Data Types

-- Unique identifier
uniqueidentifier    -- GUID

-- XML data type
xml                 -- XML documents

-- Spatial data types
geometry            -- Planar spatial data
geography           -- Ellipsoidal spatial data

-- JSON (SQL Server 2016+)
-- No native JSON type, use nvarchar(max) with JSON functions

-- Examples
CREATE TABLE OtherExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    UniqueID uniqueidentifier DEFAULT NEWID(),
    XMLData xml,
    JSONData nvarchar(max),
    Location geography
);

CRUD Operations

Insert Operations

-- Insert single row
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES ('John', 'Doe', 'john.doe@company.com', 50000, 1);

-- Insert multiple rows
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES 
    ('Jane', 'Smith', 'jane.smith@company.com', 55000, 2),
    ('Bob', 'Johnson', 'bob.johnson@company.com', 48000, 1),
    ('Alice', 'Brown', 'alice.brown@company.com', 52000, 3);

-- Insert from SELECT
INSERT INTO EmployeeBackup (FirstName, LastName, Email, Salary)
SELECT FirstName, LastName, Email, Salary
FROM Employees
WHERE HireDate < '2020-01-01';

-- Insert with OUTPUT clause
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName, INSERTED.LastName
VALUES ('Charlie', 'Wilson', 'charlie.wilson@company.com', 60000, 2);

-- MERGE statement (UPSERT)
MERGE Employees AS target
USING (VALUES (1, 'John', 'Doe', 'john.doe@company.com', 55000)) 
    AS source (EmployeeID, FirstName, LastName, Email, Salary)
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Salary = source.Salary
WHEN NOT MATCHED THEN
    INSERT (FirstName, LastName, Email, Salary)
    VALUES (source.FirstName, source.LastName, source.Email, source.Salary);

Select Operations

-- Basic select
SELECT * FROM Employees;

-- Select specific columns
SELECT EmployeeID, FirstName, LastName, Salary FROM Employees;

-- Select with alias
SELECT EmployeeID AS ID, FirstName + ' ' + LastName AS FullName
FROM Employees;

-- Select with WHERE clause
SELECT * FROM Employees WHERE DepartmentID = 1;
SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 60000;
SELECT * FROM Employees WHERE FirstName LIKE 'J%';
SELECT * FROM Employees WHERE HireDate > '2020-01-01';

-- Select with ORDER BY
SELECT * FROM Employees ORDER BY Salary DESC;
SELECT * FROM Employees ORDER BY DepartmentID, Salary DESC;

-- Select with GROUP BY
SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

-- Select with HAVING
SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

-- Select with TOP
SELECT TOP 10 * FROM Employees ORDER BY Salary DESC;
SELECT TOP 10 PERCENT * FROM Employees ORDER BY HireDate;

-- Select with OFFSET/FETCH (SQL Server 2012+)
SELECT * FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- Select with CTE
WITH HighEarners AS (
    SELECT * FROM Employees WHERE Salary > 50000
)
SELECT * FROM HighEarners WHERE DepartmentID = 1;

Update Operations

-- Update single row
UPDATE Employees 
SET Salary = 55000 
WHERE EmployeeID = 1;

-- Update multiple columns
UPDATE Employees 
SET Salary = Salary * 1.1, 
    LastName = 'Smith-Johnson' 
WHERE EmployeeID = 2;

-- Update with JOIN
UPDATE e
SET e.Salary = e.Salary * 1.05
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'IT';

-- Update with OUTPUT clause
UPDATE Employees 
SET Salary = Salary * 1.1
OUTPUT DELETED.EmployeeID, DELETED.Salary AS OldSalary, 
       INSERTED.Salary AS NewSalary
WHERE DepartmentID = 1;

-- Update with subquery
UPDATE Employees 
SET Salary = (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = Employees.DepartmentID)
WHERE Salary IS NULL;

Delete Operations

-- Delete specific rows
DELETE FROM Employees WHERE EmployeeID = 1;

-- Delete with conditions
DELETE FROM Employees WHERE HireDate < '2010-01-01';

-- Delete with JOIN
DELETE e
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Temp';

-- Delete with OUTPUT clause
DELETE FROM Employees 
OUTPUT DELETED.EmployeeID, DELETED.FirstName, DELETED.LastName
WHERE DepartmentID = 5;

-- Delete all rows
DELETE FROM Employees;

-- Truncate table (faster than DELETE)
TRUNCATE TABLE Employees;

Query Operations

Joins

-- Inner join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Left outer join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Right outer join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Full outer join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Cross join
SELECT e.FirstName, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

-- Self join
SELECT e1.FirstName + ' ' + e1.LastName AS Employee,
       e2.FirstName + ' ' + e2.LastName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Subqueries

-- Subquery in WHERE clause
SELECT * FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Subquery with IN
SELECT * FROM Employees 
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

-- Subquery with EXISTS
SELECT * FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);

-- Correlated subquery
SELECT * FROM Employees e1
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

-- Subquery in SELECT clause
SELECT EmployeeID, FirstName, LastName,
       (SELECT DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DeptName
FROM Employees e;

-- Subquery in FROM clause
SELECT DeptName, AvgSalary
FROM (SELECT d.DepartmentName AS DeptName, AVG(e.Salary) AS AvgSalary
      FROM Employees e 
      JOIN Departments d ON e.DepartmentID = d.DepartmentID
      GROUP BY d.DepartmentName) AS DeptAvg;

Window Functions

-- ROW_NUMBER
SELECT EmployeeID, FirstName, LastName, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

-- RANK and DENSE_RANK
SELECT EmployeeID, FirstName, LastName, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

-- PARTITION BY
SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary,
       ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptRank
FROM Employees;

-- LAG and LEAD
SELECT EmployeeID, HireDate, Salary,
       LAG(Salary, 1) OVER (ORDER BY HireDate) AS PrevSalary,
       LEAD(Salary, 1) OVER (ORDER BY HireDate) AS NextSalary
FROM Employees;

-- FIRST_VALUE and LAST_VALUE
SELECT EmployeeID, DepartmentID, Salary,
       FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS MinSalary,
       LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary 
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MaxSalary
FROM Employees;

-- Running totals
SELECT EmployeeID, HireDate, Salary,
       SUM(Salary) OVER (ORDER BY HireDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Employees;

-- Moving averages
SELECT EmployeeID, HireDate, Salary,
       AVG(Salary) OVER (ORDER BY HireDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;

Common Table Expressions (CTE)

-- Basic CTE
WITH HighEarners AS (
    SELECT * FROM Employees WHERE Salary > 50000
)
SELECT * FROM HighEarners WHERE DepartmentID = 1;

-- Recursive CTE
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

-- Multiple CTEs
WITH 
DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
),
HighPerformers AS (
    SELECT e.*, da.AvgSalary
    FROM Employees e
    JOIN DeptAvg da ON e.DepartmentID = da.DepartmentID
    WHERE e.Salary > da.AvgSalary
)
SELECT * FROM HighPerformers;

Indexes

Creating Indexes

-- Simple index
CREATE INDEX IX_Employees_LastName ON Employees(LastName);

-- Composite index
CREATE INDEX IX_Employees_Dept_Salary ON Employees(DepartmentID, Salary);

-- Unique index
CREATE UNIQUE INDEX IX_Employees_Email ON Employees(Email);

-- Clustered index (only one per table)
CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees(EmployeeID);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Employees_HireDate ON Employees(HireDate);

-- Filtered index
CREATE INDEX IX_Employees_Active ON Employees(EmployeeID) 
WHERE IsActive = 1;

-- Included columns
CREATE INDEX IX_Employees_Dept_Incl ON Employees(DepartmentID) 
INCLUDE (FirstName, LastName, Salary);

-- Columnstore index
CREATE COLUMNSTORE INDEX IX_Sales_Columnstore ON Sales;

-- XML index
CREATE PRIMARY XML INDEX IX_Products_XML ON Products(XMLData);

Managing Indexes

-- Show indexes
SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique,
    i.is_primary_key,
    c.name AS ColumnName
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Employees')
ORDER BY i.name, ic.key_ordinal;

-- Index usage statistics
SELECT 
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE i.object_id = OBJECT_ID('Employees');

-- Rebuild index
ALTER INDEX IX_Employees_LastName ON Employees REBUILD;

-- Reorganize index
ALTER INDEX IX_Employees_LastName ON Employees REORGANIZE;

-- Disable index
ALTER INDEX IX_Employees_LastName ON Employees DISABLE;

-- Drop index
DROP INDEX IX_Employees_LastName ON Employees;

-- Update statistics
UPDATE STATISTICS Employees;
UPDATE STATISTICS Employees IX_Employees_LastName;

Views

Creating Views

-- Simple view
CREATE VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Email, DepartmentID
FROM Employees
WHERE IsActive = 1;

-- Complex view with joins
CREATE VIEW vw_EmployeeDepartment AS
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    e.Salary,
    d.DepartmentName,
    d.Location
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- View with aggregation
CREATE VIEW vw_DepartmentSummary AS
SELECT 
    d.DepartmentName,
    COUNT(e.EmployeeID) AS EmployeeCount,
    AVG(e.Salary) AS AvgSalary,
    MAX(e.Salary) AS MaxSalary,
    MIN(e.Salary) AS MinSalary
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;

-- Indexed view (materialized view)
CREATE VIEW vw_SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    ProductID,
    COUNT_BIG(*) AS SalesCount,
    SUM(Quantity) AS TotalQuantity,
    SUM(Amount) AS TotalAmount
FROM dbo.Sales
GROUP BY ProductID;

CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON vw_SalesSummary(ProductID);

Managing Views

-- Show views
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS;

-- Show view definition
SELECT OBJECT_DEFINITION(OBJECT_ID('vw_ActiveEmployees'));

-- Alter view
ALTER VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Email, DepartmentID, Salary
FROM Employees
WHERE IsActive = 1;

-- Drop view
DROP VIEW vw_ActiveEmployees;

-- Update through view (if updatable)
UPDATE vw_ActiveEmployees 
SET Salary = 60000 
WHERE EmployeeID = 1;

Stored Procedures

Creating Stored Procedures

-- Simple stored procedure
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS EmployeeCount FROM Employees;
END;

-- Procedure with parameters
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID int
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END;

-- Procedure with input and output parameters
CREATE PROCEDURE GetEmployeeStats
    @DepartmentID int,
    @EmployeeCount int OUTPUT,
    @AvgSalary decimal(10,2) OUTPUT
AS
BEGIN
    SELECT 
        @EmployeeCount = COUNT(*),
        @AvgSalary = AVG(Salary)
    FROM Employees 
    WHERE DepartmentID = @DepartmentID;
END;

-- Procedure with error handling
CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID int,
    @NewSalary decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
        BEGIN
            RAISERROR('Employee not found', 16, 1);
            RETURN;
        END;

        UPDATE Employees 
        SET Salary = @NewSalary 
        WHERE EmployeeID = @EmployeeID;

        COMMIT TRANSACTION;

        SELECT 'Salary updated successfully' AS Message;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;

Calling Stored Procedures

-- Call simple procedure
EXEC GetEmployeeCount;

-- Call procedure with parameters
EXEC GetEmployeesByDepartment @DepartmentID = 1;

-- Call procedure with output parameters
DECLARE @Count int, @AvgSal decimal(10,2);
EXEC GetEmployeeStats 
    @DepartmentID = 1, 
    @EmployeeCount = @Count OUTPUT, 
    @AvgSalary = @AvgSal OUTPUT;
SELECT @Count AS EmployeeCount, @AvgSal AS AverageSalary;

-- Call procedure with error handling
BEGIN TRY
    EXEC UpdateEmployeeSalary @EmployeeID = 1, @NewSalary = 65000;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Managing Stored Procedures

-- Show stored procedures
SELECT name, create_date, modify_date
FROM sys.procedures;

-- Show procedure definition
SELECT OBJECT_DEFINITION(OBJECT_ID('GetEmployeeCount'));

-- Alter stored procedure
ALTER PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS TotalEmployees, 
           COUNT(CASE WHEN IsActive = 1 THEN 1 END) AS ActiveEmployees
    FROM Employees;
END;

-- Drop stored procedure
DROP PROCEDURE GetEmployeeCount;

Functions

Creating Functions

-- Scalar function
CREATE FUNCTION GetFullName(@FirstName nvarchar(50), @LastName nvarchar(50))
RETURNS nvarchar(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

-- Inline table-valued function
CREATE FUNCTION GetEmployeesByDept(@DepartmentID int)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

-- Multi-statement table-valued function
CREATE FUNCTION GetEmployeeSummary(@DepartmentID int)
RETURNS @EmployeeTable TABLE
(
    EmployeeID int,
    FullName nvarchar(101),
    Salary decimal(10,2),
    SalaryGrade nvarchar(10)
)
AS
BEGIN
    INSERT INTO @EmployeeTable
    SELECT 
        EmployeeID,
        FirstName + ' ' + LastName,
        Salary,
        CASE 
            WHEN Salary >= 60000 THEN 'High'
            WHEN Salary >= 40000 THEN 'Medium'
            ELSE 'Low'
        END
    FROM Employees
    WHERE DepartmentID = @DepartmentID;

    RETURN;
END;

-- Function with complex logic
CREATE FUNCTION CalculateBonus(@EmployeeID int, @BonusPercent decimal(5,2))
RETURNS decimal(10,2)
AS
BEGIN
    DECLARE @Salary decimal(10,2);
    DECLARE @YearsOfService int;
    DECLARE @Bonus decimal(10,2);

    SELECT @Salary = Salary, @YearsOfService = DATEDIFF(YEAR, HireDate, GETDATE())
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    IF @YearsOfService >= 10
        SET @Bonus = @Salary * (@BonusPercent * 1.5) / 100;
    ELSE IF @YearsOfService >= 5
        SET @Bonus = @Salary * (@BonusPercent * 1.2) / 100;
    ELSE
        SET @Bonus = @Salary * @BonusPercent / 100;

    RETURN @Bonus;
END;

Using Functions

-- Use scalar function
SELECT EmployeeID, dbo.GetFullName(FirstName, LastName) AS FullName
FROM Employees;

-- Use inline table-valued function
SELECT * FROM dbo.GetEmployeesByDept(1);

-- Use multi-statement table-valued function
SELECT * FROM dbo.GetEmployeeSummary(2);

-- Use function in WHERE clause
SELECT * FROM Employees
WHERE dbo.CalculateBonus(EmployeeID, 10) > 5000;

Managing Functions

-- Show functions
SELECT name, type_desc, create_date, modify_date
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF');

-- Show function definition
SELECT OBJECT_DEFINITION(OBJECT_ID('GetFullName'));

-- Alter function
ALTER FUNCTION GetFullName(@FirstName nvarchar(50), @LastName nvarchar(50))
RETURNS nvarchar(101)
AS
BEGIN
    RETURN ISNULL(@FirstName, '') + ' ' + ISNULL(@LastName, '');
END;

-- Drop function
DROP FUNCTION GetFullName;

Triggers

Creating Triggers

-- AFTER INSERT trigger
CREATE TRIGGER trg_Employee_Insert
ON Employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EmployeeAudit (EmployeeID, Action, ActionDate, ActionBy)
    SELECT EmployeeID, 'INSERT', GETDATE(), SYSTEM_USER
    FROM inserted;
END;

-- AFTER UPDATE trigger
CREATE TRIGGER trg_Employee_Update
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(Salary)
    BEGIN
        INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate, ChangedBy)
        SELECT 
            i.EmployeeID,
            d.Salary,
            i.Salary,
            GETDATE(),
            SYSTEM_USER
        FROM inserted i
        JOIN deleted d ON i.EmployeeID = d.EmployeeID
        WHERE i.Salary != d.Salary;
    END;
END;

-- INSTEAD OF trigger (for views)
CREATE TRIGGER trg_EmployeeView_Insert
ON vw_EmployeeDepartment
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
    SELECT FirstName, LastName, Email, 
           (SELECT DepartmentID FROM Departments WHERE DepartmentName = inserted.DepartmentName)
    FROM inserted;
END;

-- DDL trigger
CREATE TRIGGER trg_DatabaseChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData xml = EVENTDATA();

    INSERT INTO DDLAudit (EventType, ObjectName, LoginName, EventDate, EventData)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
        GETDATE(),
        @EventData
    );
END;

Managing Triggers

-- Show triggers
SELECT 
    t.name AS TriggerName,
    o.name AS TableName,
    t.type_desc,
    t.is_disabled
FROM sys.triggers t
JOIN sys.objects o ON t.parent_id = o.object_id;

-- Show trigger definition
SELECT OBJECT_DEFINITION(OBJECT_ID('trg_Employee_Insert'));

-- Enable/disable trigger
DISABLE TRIGGER trg_Employee_Insert ON Employees;
ENABLE TRIGGER trg_Employee_Insert ON Employees;

-- Drop trigger
DROP TRIGGER trg_Employee_Insert;

Transactions

Transaction Control

-- Basic transaction
BEGIN TRANSACTION;

INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
VALUES ('Test', 'User', 'test@company.com', 1);

UPDATE Employees SET Salary = 50000 WHERE EmployeeID = SCOPE_IDENTITY();

COMMIT TRANSACTION;

-- Transaction with rollback
BEGIN TRANSACTION;

INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
VALUES ('Test', 'User2', 'test2@company.com', 1);

-- Simulate error condition
IF @@ERROR != 0
BEGIN
    ROLLBACK TRANSACTION;
    RETURN;
END;

COMMIT TRANSACTION;

-- Named transaction with savepoint
BEGIN TRANSACTION MainTransaction;

INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
VALUES ('Test', 'User3', 'test3@company.com', 1);

SAVE TRANSACTION SavePoint1;

UPDATE Employees SET Salary = 60000 WHERE FirstName = 'Test';

-- Rollback to savepoint
ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION MainTransaction;

Transaction Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- Table hints for specific queries
SELECT * FROM Employees WITH (NOLOCK);
SELECT * FROM Employees WITH (READPAST);
SELECT * FROM Employees WITH (UPDLOCK);
SELECT * FROM Employees WITH (XLOCK);

Error Handling in Transactions

-- TRY-CATCH with transactions
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
    VALUES ('John', 'Doe', 'john.doe@company.com', 1);

    UPDATE Employees SET Salary = 50000 WHERE EmployeeID = SCOPE_IDENTITY();

    -- Simulate error
    DECLARE @Error int = 1/0;

    COMMIT TRANSACTION;
    SELECT 'Transaction completed successfully' AS Message;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine;
END CATCH;

T-SQL Programming

Variables and Data Types

-- Declare variables
DECLARE @EmployeeID int;
DECLARE @FirstName nvarchar(50);
DECLARE @Salary decimal(10,2) = 50000;
DECLARE @HireDate datetime = GETDATE();

-- Set variable values
SET @EmployeeID = 1;
SET @FirstName = 'John';

-- Set from query
SELECT @FirstName = FirstName FROM Employees WHERE EmployeeID = @EmployeeID;

-- Table variables
DECLARE @EmployeeTable TABLE (
    EmployeeID int,
    FullName nvarchar(101),
    Salary decimal(10,2)
);

INSERT INTO @EmployeeTable
SELECT EmployeeID, FirstName + ' ' + LastName, Salary
FROM Employees
WHERE DepartmentID = 1;

SELECT * FROM @EmployeeTable;

Control Flow

-- IF-ELSE
DECLARE @Salary decimal(10,2) = 55000;

IF @Salary > 50000
    PRINT 'High salary';
ELSE IF @Salary > 30000
    PRINT 'Medium salary';
ELSE
    PRINT 'Low salary';

-- CASE statement
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    CASE 
        WHEN Salary >= 60000 THEN 'High'
        WHEN Salary >= 40000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryGrade
FROM Employees;

-- WHILE loop
DECLARE @Counter int = 1;

WHILE @Counter <= 10
BEGIN
    PRINT 'Counter: ' + CAST(@Counter AS nvarchar(10));
    SET @Counter = @Counter + 1;

    IF @Counter = 5
        BREAK;
END;

-- FOR loop (using CTE)
WITH Numbers AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT Number FROM Numbers;

Cursors

-- Declare cursor
DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE DepartmentID = 1;

-- Variables for cursor
DECLARE @EmpID int, @FName nvarchar(50), @LName nvarchar(50), @Sal decimal(10,2);

-- Open cursor
OPEN employee_cursor;

-- Fetch first row
FETCH NEXT FROM employee_cursor INTO @EmpID, @FName, @LName, @Sal;

-- Loop through cursor
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @FName + ' ' + @LName + ', Salary: ' + CAST(@Sal AS nvarchar(20));

    -- Fetch next row
    FETCH NEXT FROM employee_cursor INTO @EmpID, @FName, @LName, @Sal;
END;

-- Close and deallocate cursor
CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Dynamic SQL

-- Simple dynamic SQL
DECLARE @SQL nvarchar(max);
DECLARE @TableName nvarchar(50) = 'Employees';

SET @SQL = 'SELECT COUNT(*) FROM ' + @TableName;
EXEC sp_executesql @SQL;

-- Dynamic SQL with parameters
DECLARE @SQL nvarchar(max);
DECLARE @DeptID int = 1;

SET @SQL = 'SELECT * FROM Employees WHERE DepartmentID = @DepartmentID';
EXEC sp_executesql @SQL, N'@DepartmentID int', @DepartmentID = @DeptID;

-- Building complex dynamic SQL
DECLARE @SQL nvarchar(max) = 'SELECT EmployeeID, FirstName, LastName';
DECLARE @IncludeSalary bit = 1;
DECLARE @DepartmentFilter int = NULL;

IF @IncludeSalary = 1
    SET @SQL = @SQL + ', Salary';

SET @SQL = @SQL + ' FROM Employees';

IF @DepartmentFilter IS NOT NULL
    SET @SQL = @SQL + ' WHERE DepartmentID = ' + CAST(@DepartmentFilter AS nvarchar(10));

PRINT @SQL;
EXEC sp_executesql @SQL;

Backup and Recovery

Full Backup

-- Full database backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

-- Full backup with multiple files
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full_1.bak',
   DISK = 'C:\Backup\MyDatabase_Full_2.bak'
WITH FORMAT, INIT, COMPRESSION;

-- Full backup to URL (Azure)
BACKUP DATABASE MyDatabase 
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/MyDatabase_Full.bak'
WITH CREDENTIAL = 'MyAzureCredential', COMPRESSION;

Differential and Log Backups

-- Differential backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

-- Transaction log backup
BACKUP LOG MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH COMPRESSION, STATS = 10;

-- Backup verification
RESTORE VERIFYONLY 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak';

Restore Operations

-- Full restore
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY, STATS = 10;

-- Restore differential backup
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH NORECOVERY, STATS = 10;

-- Restore log backup
RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH RECOVERY, STATS = 10;

-- Point-in-time restore
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH STOPAT = '2023-12-01 14:30:00', RECOVERY;

-- Restore to different database
RESTORE DATABASE MyDatabase_Test 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH MOVE 'MyDatabase_Data' TO 'C:\Data\MyDatabase_Test.mdf',
     MOVE 'MyDatabase_Log' TO 'C:\Data\MyDatabase_Test.ldf',
     REPLACE;

Backup Information

-- Show backup history
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type,
    bs.backup_size / 1024 / 1024 AS backup_size_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'MyDatabase'
ORDER BY bs.backup_start_date DESC;

-- Show restore history
SELECT 
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    bs.backup_start_date,
    bmf.physical_device_name
FROM msdb.dbo.restorehistory rh
JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE rh.destination_database_name = 'MyDatabase'
ORDER BY rh.restore_date DESC;

Performance Tuning

Query Performance

-- Show execution plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET SHOWPLAN_ALL OFF;

-- Include actual execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Query hints
SELECT * FROM Employees WITH (INDEX(IX_Employees_DepartmentID))
WHERE DepartmentID = 1;

SELECT * FROM Employees 
WHERE DepartmentID = 1
OPTION (RECOMPILE);

-- Plan guides
EXEC sp_create_plan_guide 
    @name = N'Guide1',
    @stmt = N'SELECT * FROM Employees WHERE DepartmentID = @DeptID',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@DeptID int',
    @hints = N'OPTION (PARAMETERIZATION FORCED)';

Index Analysis

-- Missing indexes
SELECT 
    mid.statement AS TableName,
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + '_' + 
    REPLACE(REPLACE(mid.equality_columns + ISNULL('_' + mid.inequality_columns, ''), '[', ''), ']', '') +
    ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NOT NULL AND mid.equality_columns IS NOT NULL THEN ',' ELSE '' END +
    ISNULL(mid.inequality_columns, '') + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY improvement_measure DESC;

-- Unused indexes
SELECT 
    i.name AS IndexName,
    o.name AS TableName,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE o.type = 'U'
    AND i.index_id > 0
    AND (s.user_seeks + s.user_scans + s.user_lookups) = 0
ORDER BY o.name, i.name;

-- Index fragmentation
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Query Store

-- Enable Query Store
ALTER DATABASE MyDatabase SET QUERY_STORE = ON;

-- Configure Query Store
ALTER DATABASE MyDatabase SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 60
);

-- Top resource consuming queries
SELECT 
    qsq.query_id,
    qsqt.query_sql_text,
    qsrs.avg_duration / 1000 AS avg_duration_ms,
    qsrs.avg_cpu_time / 1000 AS avg_cpu_time_ms,
    qsrs.avg_logical_io_reads,
    qsrs.count_executions
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsrs.last_execution_time > DATEADD(day, -7, GETDATE())
ORDER BY qsrs.avg_duration DESC;

-- Clear Query Store
ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR;

Monitoring

System Information

-- Server information
SELECT 
    @@SERVERNAME AS ServerName,
    @@VERSION AS Version,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('MachineName') AS MachineName;

-- Database information
SELECT 
    name AS DatabaseName,
    database_id,
    create_date,
    state_desc AS State,
    recovery_model_desc AS RecoveryModel,
    collation_name AS Collation
FROM sys.databases;

-- Active connections
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.is_user_process = 1;

-- Blocking sessions
SELECT 
    blocking.session_id AS blocking_session_id,
    blocked.session_id AS blocked_session_id,
    blocking_sql.text AS blocking_sql,
    blocked_sql.text AS blocked_sql,
    blocked.wait_type,
    blocked.wait_time
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_sql
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql;

Performance Monitoring

-- CPU usage
SELECT 
    record_id,
    DATEADD(ms, -1 * ((SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info) - [timestamp]), GETDATE()) AS EventTime,
    100 - SystemIdle AS SQLServerCPU,
    100 - SystemIdle - SQLProcessUtilization AS OtherProcessCPU
FROM (
    SELECT 
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
        timestamp
    FROM (
        SELECT timestamp, CONVERT(xml, record) AS record 
        FROM sys.dm_os_ring_buffers 
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
            AND record LIKE '%<SystemHealth>%'
    ) AS x
) AS y
ORDER BY record_id DESC;

-- Memory usage
SELECT 
    (total_physical_memory_kb / 1024) AS total_physical_memory_mb,
    (available_physical_memory_kb / 1024) AS available_physical_memory_mb,
    (total_page_file_kb / 1024) AS total_page_file_mb,
    (available_page_file_kb / 1024) AS available_page_file_mb,
    (system_memory_state_desc) AS system_memory_state
FROM sys.dm_os_sys_memory;

-- Wait statistics
SELECT TOP 20
    wait_type,
    wait_time_ms,
    signal_wait_time_ms,
    waiting_tasks_count,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
    AND wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
                         'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
                         'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                         'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
                         'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT',
                         'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY wait_time_ms DESC;

-- I/O statistics
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    file_id,
    io_stall_read_ms,
    num_of_reads,
    CASE WHEN num_of_reads = 0 THEN 0 ELSE io_stall_read_ms / num_of_reads END AS avg_read_stall_ms,
    io_stall_write_ms,
    num_of_writes,
    CASE WHEN num_of_writes = 0 THEN 0 ELSE io_stall_write_ms / num_of_writes END AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms + io_stall_write_ms DESC;

Database Size and Growth

-- Database file sizes
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalName,
    physical_name AS PhysicalName,
    type_desc AS FileType,
    size * 8 / 1024 AS SizeMB,
    max_size * 8 / 1024 AS MaxSizeMB,
    CASE 
        WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(10)) + '%'
        ELSE CAST(growth * 8 / 1024 AS varchar(10)) + ' MB'
    END AS GrowthSetting
FROM sys.master_files
ORDER BY database_id, file_id;

-- Table sizes
SELECT 
    t.name AS TableName,
    p.rows AS RowCount,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.name, p.rows
ORDER BY SUM(a.total_pages) DESC;

Security

Authentication and Authorization

-- Create login with password policy
CREATE LOGIN secure_user WITH PASSWORD = 'ComplexPassword123!',
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON;

-- Create user with certificate
CREATE CERTIFICATE UserCert WITH SUBJECT = 'Certificate for secure_user';
CREATE LOGIN cert_user FROM CERTIFICATE UserCert;

-- Password policy
ALTER LOGIN secure_user WITH PASSWORD = 'NewComplexPassword123!',
    OLD_PASSWORD = 'ComplexPassword123!';

-- Lock/unlock login
ALTER LOGIN secure_user DISABLE;
ALTER LOGIN secure_user ENABLE;

-- Show login information
SELECT 
    name,
    type_desc,
    is_disabled,
    create_date,
    modify_date,
    default_database_name,
    is_policy_checked,
    is_expiration_checked
FROM sys.server_principals
WHERE type IN ('S', 'U');

Encryption

-- Transparent Data Encryption (TDE)
-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword123!';

-- Create certificate
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

-- Create database encryption key
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Enable TDE
ALTER DATABASE MyDatabase SET ENCRYPTION ON;

-- Check encryption status
SELECT 
    db.name AS DatabaseName,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id;

-- Column-level encryption
-- Create symmetric key
CREATE SYMMETRIC KEY ColumnKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TDECert;

-- Open symmetric key
OPEN SYMMETRIC KEY ColumnKey
DECRYPTION BY CERTIFICATE TDECert;

-- Encrypt data
INSERT INTO Employees (FirstName, LastName, SSN)
VALUES ('John', 'Doe', EncryptByKey(Key_GUID('ColumnKey'), '123-45-6789'));

-- Decrypt data
SELECT FirstName, LastName, 
       CONVERT(varchar, DecryptByKey(SSN)) AS SSN
FROM Employees;

-- Close symmetric key
CLOSE SYMMETRIC KEY ColumnKey;

Auditing

-- Create server audit
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'C:\Audit\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 10)
WITH (ON_FAILURE = CONTINUE);

-- Enable server audit
ALTER SERVER AUDIT ServerAudit WITH (STATE = ON);

-- Create database audit specification
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
FOR SERVER AUDIT ServerAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Employees BY public),
ADD (EXECUTE ON dbo.GetEmployeeInfo BY public);

-- Enable database audit specification
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec WITH (STATE = ON);

-- View audit records
SELECT 
    event_time,
    action_id,
    succeeded,
    database_name,
    schema_name,
    object_name,
    statement,
    server_principal_name,
    database_principal_name
FROM sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Row Level Security

-- Create security policy function
CREATE FUNCTION dbo.fn_securitypredicate(@EmployeeID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @EmployeeID = USER_ID() OR IS_MEMBER('db_owner') = 1;

-- Create security policy
CREATE SECURITY POLICY EmployeeSecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(EmployeeID) ON dbo.Employees,
ADD BLOCK PREDICATE dbo.fn_securitypredicate(EmployeeID) ON dbo.Employees AFTER INSERT,
ADD BLOCK PREDICATE dbo.fn_securitypredicate(EmployeeID) ON dbo.Employees AFTER UPDATE
WITH (STATE = ON);

-- Dynamic data masking
ALTER TABLE Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE Employees
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');

-- Grant unmask permission
GRANT UNMASK TO hr_manager;

Best Practices

Schema Design Best Practices

-- Use appropriate data types
CREATE TABLE Orders (
    OrderID int IDENTITY(1,1) PRIMARY KEY,        -- Use IDENTITY for auto-increment
    OrderDate datetime2 NOT NULL DEFAULT GETDATE(), -- Use datetime2 for better precision
    Amount decimal(10,2) NOT NULL,                 -- Use decimal for money
    Status nvarchar(20) NOT NULL DEFAULT 'Pending', -- Use nvarchar for Unicode
    CustomerID int NOT NULL,
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    CONSTRAINT CK_Orders_Amount CHECK (Amount > 0),
    CONSTRAINT CK_Orders_Status CHECK (Status IN ('Pending', 'Processing', 'Completed', 'Cancelled'))
);

-- Use proper indexing strategy
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);
CREATE INDEX IX_Orders_Status_OrderDate ON Orders(Status, OrderDate) INCLUDE (Amount);

-- Use schemas for organization
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE SCHEMA finance;

-- Move tables to appropriate schemas
ALTER SCHEMA sales TRANSFER dbo.Orders;
ALTER SCHEMA hr TRANSFER dbo.Employees;

Performance Best Practices

-- Use parameterized queries
-- Bad: Dynamic SQL without parameters
DECLARE @SQL nvarchar(max) = 'SELECT * FROM Employees WHERE DepartmentID = ' + CAST(@DeptID AS nvarchar(10));

-- Good: Parameterized query
DECLARE @SQL nvarchar(max) = 'SELECT * FROM Employees WHERE DepartmentID = @DepartmentID';
EXEC sp_executesql @SQL, N'@DepartmentID int', @DepartmentID = @DeptID;

-- Use EXISTS instead of IN for subqueries
-- Good:
SELECT * FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.DepartmentID = e.DepartmentID AND d.Location = 'New York');

-- Avoid SELECT *
-- Bad: SELECT * FROM Employees;
-- Good: SELECT EmployeeID, FirstName, LastName FROM Employees;

-- Use appropriate JOIN types
-- Use INNER JOIN when possible
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Use SET NOCOUNT ON in stored procedures
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Employees;
END;

Security Best Practices

-- Use least privilege principle
-- Create role for specific access
CREATE ROLE app_read_role;
GRANT SELECT ON schema::dbo TO app_read_role;
ALTER ROLE app_read_role ADD MEMBER app_user;

-- Use stored procedures for data access
CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeID, FirstName, LastName, Email
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

GRANT EXECUTE ON GetEmployeeInfo TO app_user;

-- Regular maintenance tasks
-- Update statistics
UPDATE STATISTICS Employees;

-- Rebuild indexes
ALTER INDEX ALL ON Employees REBUILD;

-- Check database integrity
DBCC CHECKDB('MyDatabase');

-- Backup database
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH COMPRESSION, CHECKSUM;

Monitoring and Maintenance

-- Create maintenance plan queries
-- Check database integrity
DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS;

-- Update statistics for all tables
EXEC sp_updatestats;

-- Rebuild fragmented indexes
DECLARE @SQL nvarchar(max) = '';
SELECT @SQL = @SQL + 'ALTER INDEX ' + i.name + ' ON ' + OBJECT_NAME(i.object_id) + ' REBUILD;' + CHAR(13)
FROM sys.indexes i
JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps 
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.avg_fragmentation_in_percent > 30
    AND ps.page_count > 1000;

EXEC sp_executesql @SQL;

-- Monitor long-running queries
SELECT 
    r.session_id,
    r.start_time,
    r.status,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.estimated_completion_time > 0;

---

Summary

Microsoft SQL Server is a comprehensive enterprise database platform that provides robust features for data management, business intelligence, and application development with strong integration with the Microsoft ecosystem.

Key Strengths: - Enterprise Integration: Seamless integration with Microsoft technologies and Azure cloud - Business Intelligence: Built-in reporting, analysis, and data warehousing capabilities - Performance: Advanced query optimizer and in-memory technologies - Security: Comprehensive security features including TDE, RLS, and auditing - Scalability: Support for large databases and high-availability solutions

Best Use Cases: - Enterprise applications in Microsoft environments - Business intelligence and data warehousing - Applications requiring strong ACID compliance - Systems needing advanced security features - Cloud-hybrid deployments with Azure

Important Considerations: - Licensing costs can be significant for enterprise features - Best performance on Windows environments - Requires regular maintenance and monitoring - Memory and storage requirements can be substantial - Backup and recovery strategies must be well-planned

By following the practices and techniques outlined in this cheatsheet, you can effectively design, implement, and maintain SQL Server databases that meet enterprise requirements for performance, security, and reliability while leveraging the full power of the Microsoft data platform.