SQL Server Management Studio (SSMS) Roadmap & Materials
A comprehensive, end-to-end training program that covers everything from absolute basics to advanced topics.
COURSE STRUCTURE OVERVIEW
Duration: 60-80 hours (can be adjusted based on pace)
Target Audience: Complete beginners to intermediate level
Prerequisites: Basic computer knowledge
MODULE 1: FUNDAMENTALS & SETUP (4-6 hours)
1.1 Introduction to Databases
What is a Database?
A database is an organized collection of structured data stored electronically in a computer system. It allows for efficient storage, retrieval, modification, and deletion of data.
Types of Databases:
Relational Databases (RDBMS) – Data stored in tables with relationships
NoSQL Databases – Document-based, key-value, graph databases
Cloud Databases – Hosted on cloud platforms
Why SQL Server?
Enterprise-grade reliability
Excellent performance and scalability
Strong security features
Integration with Microsoft ecosystem
Industry-standard for many organizations
RDBMS Concepts:
Data stored in tables (rows and columns)
Tables can be related through keys
ACID properties (Atomicity, Consistency, Isolation, Durability)
Data integrity and constraints
1.2 SQL Server Architecture
SQL Server Components:
Database Engine – Core service for storing and processing data
SQL Server Agent – Job scheduling and automation
Analysis Services (SSAS) – OLAP and data mining
Integration Services (SSIS) – ETL operations
Reporting Services (SSRS) – Report generation
Storage Architecture:
Data files (.mdf) – Primary data file
Log files (.ldf) – Transaction log file
Secondary data files (.ndf) – Additional data files
Memory Architecture:
Buffer Pool – Caches data pages
Procedure Cache – Stores execution plans
Log Cache – Transaction log buffer
1.3 Installation & Configuration
System Requirements:
Operating System: Windows Server 2016 or higher, Windows 10/11
Processor: x64 processor, 1.4 GHz minimum
RAM: Minimum 1 GB (4 GB+ recommended)
Disk Space: 6 GB minimum
Installation Steps:
Download SQL Server Developer/Express Edition (free versions)
Run setup.exe as Administrator
Select “New SQL Server stand-alone installation”
Accept license terms
Select features:
Database Engine Services (mandatory)
SQL Server Replication
Full-Text Search
Client Tools Connectivity
Instance Configuration:
Default instance: MSSQLSERVER
Named instance: Custom name
Server Configuration:
Set service accounts
Configure startup types
Database Engine Configuration:
Authentication Mode: Mixed Mode (SQL and Windows)
Set SA password
Add current user as administrator
Complete installation
Installing SQL Server Management Studio (SSMS):
Download latest SSMS separately
Run installer
Follow wizard (straightforward installation)
Launch SSMS after installation
First Connection:
Open SSMS
Server name: localhost or (local) or computer name
Authentication: Windows Authentication or SQL Server Authentication
Click Connect
1.4 SSMS Interface Overview
Object Explorer:
Tree view of all database objects
Databases, Security, Server Objects, Replication, Management
Query Editor:
Where you write and execute SQL queries
IntelliSense for code completion
Multiple query windows support
Solution Explorer:
Organize SQL scripts into projects
Version control integration
Properties Window:
View and modify object properties
Results Pane:
Displays query results
Grid view or Text view
Messages Pane:
Shows execution messages and errors
Toolbar Shortcuts:
New Query (Ctrl+N)
Execute (F5 or Ctrl+E)
Parse (Ctrl+F5)
Database dropdown
Practical Exercise 1.1:
Install SQL Server and SSMS
Connect to local instance
Explore Object Explorer
Create a new query window
Execute: SELECT @@VERSION
View system databases
MODULE 2: SQL FUNDAMENTALS (10-12 hours)
2.1 Understanding Databases
System Databases:
master – System configuration and login information
model – Template for new databases
msdb – SQL Agent jobs and backup history
tempdb – Temporary objects and intermediate results
Creating a Database:
sql– Simple database creation
CREATE DATABASE SchoolDB;
— Database with specific settings
CREATE DATABASE SchoolDB
ON PRIMARY
(
NAME = SchoolDB_Data,
FILENAME = ‘C:\SQLData\SchoolDB.mdf’,
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
LOG ON
(
NAME = SchoolDB_Log,
FILENAME = ‘C:\SQLData\SchoolDB.ldf’,
SIZE = 5MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB
);
Using a Database:
sqlUSE SchoolDB;
Viewing Database Information:
sql– List all databases
SELECT name, database_id, create_date
FROM sys.databases;
— Current database
SELECT DB_NAME() AS CurrentDatabase;
— Database size
EXEC sp_spaceused;
2.2 Data Types
Numeric Data Types:
sql– Integer types
TINYINT — 0 to 255 (1 byte)
SMALLINT — -32,768 to 32,767 (2 bytes)
INT — -2,147,483,648 to 2,147,483,647 (4 bytes)
BIGINT — Very large integers (8 bytes)
— Decimal types
DECIMAL(p,s) — Fixed precision (p = total digits, s = decimal digits)
NUMERIC(p,s) — Same as DECIMAL
FLOAT(n) — Floating point number
REAL — Smaller floating point
— Money types
MONEY — Currency values
SMALLMONEY — Smaller currency values
String Data Types:
sql– Character types
CHAR(n) — Fixed-length string (max 8000)
VARCHAR(n) — Variable-length string (max 8000)
VARCHAR(MAX) — Variable-length string (up to 2GB)
— Unicode types
NCHAR(n) — Fixed-length Unicode (max 4000)
NVARCHAR(n) — Variable-length Unicode (max 4000)
NVARCHAR(MAX) — Variable-length Unicode (up to 2GB)
— Text types (deprecated, use VARCHAR(MAX))
TEXT — Large text data
NTEXT — Large Unicode text data
Date and Time Types:
sqlDATE — Date only (YYYY-MM-DD)
TIME — Time only (HH:MM:SS.mmm)
DATETIME — Date and time (1753-9999)
DATETIME2 — More precise datetime (0001-9999)
SMALLDATETIME — Less precise datetime
DATETIMEOFFSET– Date/time with timezone
Other Data Types:
sqlBIT — Boolean (0, 1, or NULL)
BINARY(n) — Fixed-length binary data
VARBINARY(n) — Variable-length binary data
UNIQUEIDENTIFIER — GUID
XML — XML data
JSON — JSON data (stored as NVARCHAR)
2.3 Creating Tables
Basic Table Creation:
sqlCREATE TABLE Students
(
StudentID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Email VARCHAR(100),
IsActive BIT
);
Table with Constraints:
sqlCREATE TABLE Students
(
StudentID INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(20),
EnrollmentDate DATE DEFAULT GETDATE(),
IsActive BIT DEFAULT 1,
GPA DECIMAL(3,2) CHECK (GPA >= 0 AND GPA <= 4.0)
);
CREATE TABLE Courses
(
CourseID INT PRIMARY KEY IDENTITY(1,1),
CourseName VARCHAR(100) NOT NULL,
CourseCode VARCHAR(10) UNIQUE NOT NULL,
Credits INT CHECK (Credits > 0 AND Credits <= 6),
Department VARCHAR(50) NOT NULL
);
CREATE TABLE Enrollments
(
EnrollmentID INT PRIMARY KEY IDENTITY(1,1),
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrollmentDate DATE DEFAULT GETDATE(),
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Constraint Types Explained:
PRIMARY KEY – Uniquely identifies each record
FOREIGN KEY – Links to primary key in another table
UNIQUE – Ensures all values in column are different
NOT NULL – Column cannot have NULL values
CHECK – Validates data against a condition
DEFAULT – Assigns default value if none provided
IDENTITY – Auto-incrementing numeric column
2.4 INSERT – Adding Data
Basic INSERT:
sql– Insert single row
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email)
VALUES (‘John’, ‘Doe’, ‘2000-05-15’, ‘john.doe@email.com’);
— Insert multiple rows
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email)
VALUES
(‘Jane’, ‘Smith’, ‘1999-08-22’, ‘jane.smith@email.com’),
(‘Mike’, ‘Johnson’, ‘2001-03-10’, ‘mike.j@email.com’),
(‘Sarah’, ‘Williams’, ‘2000-11-30’, ‘sarah.w@email.com’);
— Insert with all columns (not recommended)
INSERT INTO Courses
VALUES (‘Introduction to Programming’, ‘CS101’, 3, ‘Computer Science’);
— Insert with IDENTITY column
INSERT INTO Courses (CourseName, CourseCode, Credits, Department)
VALUES (‘Data Structures’, ‘CS201’, 4, ‘Computer Science’);
INSERT with SELECT:
sql– Copy data from another table
INSERT INTO ArchivedStudents (FirstName, LastName, Email)
SELECT FirstName, LastName, Email
FROM Students
WHERE IsActive = 0;
INSERT with DEFAULT and NULL:
sqlINSERT INTO Students (FirstName, LastName, DateOfBirth, Email, Phone)
VALUES (‘Tom’, ‘Brown’, ‘1998-07-18’, ‘tom.b@email.com’, NULL);
— Using DEFAULT keyword
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email, EnrollmentDate)
VALUES (‘Lisa’, ‘Anderson’, ‘2002-01-25’, ‘lisa.a@email.com’, DEFAULT);
2.5 SELECT – Retrieving Data
Basic SELECT:
sql– Select all columns
SELECT * FROM Students;
— Select specific columns
SELECT FirstName, LastName, Email FROM Students;
— Select with alias
SELECT
FirstName AS [First Name],
LastName AS [Last Name],
Email AS [Email Address]
FROM Students;
— Select with calculated columns
SELECT
FirstName,
LastName,
DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS Age
FROM Students;
WHERE Clause – Filtering:
sql– Simple condition
SELECT * FROM Students
WHERE IsActive = 1;
— Multiple conditions with AND
SELECT * FROM Students
WHERE IsActive = 1 AND GPA > 3.5;
— Multiple conditions with OR
SELECT * FROM Students
WHERE Department = ‘Computer Science’ OR Department = ‘Mathematics’;
— Comparison operators
SELECT * FROM Students WHERE GPA >= 3.0;
SELECT * FROM Students WHERE EnrollmentDate > ‘2023-01-01’;
SELECT * FROM Students WHERE FirstName <> ‘John’; — Not equal
— BETWEEN operator
SELECT * FROM Students
WHERE EnrollmentDate BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
— IN operator
SELECT * FROM Students
WHERE Department IN (‘Computer Science’, ‘Mathematics’, ‘Physics’);
— LIKE operator (pattern matching)
SELECT * FROM Students WHERE FirstName LIKE ‘J%’; — Starts with J
SELECT * FROM Students WHERE Email LIKE ‘%@gmail.com’; — Ends with
SELECT * FROM Students WHERE FirstName LIKE ‘_ohn’; — Second char is o
SELECT * FROM Students WHERE LastName LIKE ‘%son%’; — Contains son
— IS NULL / IS NOT NULL
SELECT * FROM Students WHERE Phone IS NULL;
SELECT * FROM Students WHERE Phone IS NOT NULL;
ORDER BY – Sorting:
sql– Ascending order (default)
SELECT * FROM Students
ORDER BY LastName;
— Descending order
SELECT * FROM Students
ORDER BY GPA DESC;
— Multiple columns
SELECT * FROM Students
ORDER BY Department ASC, GPA DESC;
— Order by column position
SELECT FirstName, LastName, GPA FROM Students
ORDER BY 3 DESC; — Order by GPA (3rd column)
DISTINCT – Removing Duplicates:
sqlSELECT DISTINCT Department FROM Students;
SELECT DISTINCT Department, EnrollmentDate
FROM Students;
TOP – Limiting Results:
sql– Top N rows
SELECT TOP 10 * FROM Students
ORDER BY GPA DESC;
— Top N percent
SELECT TOP 10 PERCENT * FROM Students
ORDER BY GPA DESC;
— Top with ties
SELECT TOP 5 WITH TIES * FROM Students
ORDER BY GPA DESC;
2.6 UPDATE – Modifying Data
Basic UPDATE:
sql– Update single column
UPDATE Students
SET Phone = ‘555-1234’
WHERE StudentID = 1;
— Update multiple columns
UPDATE Students
SET
Phone = ‘555-5678’,
Email = ‘newemail@example.com’
WHERE StudentID = 2;
— Update with calculation
UPDATE Students
SET GPA = GPA + 0.1
WHERE GPA < 3.0;
— Update all rows (be careful!)
UPDATE Students
SET IsActive = 1;
— Update with subquery
UPDATE Enrollments
SET Grade = ‘A’
WHERE StudentID IN (
SELECT StudentID FROM Students WHERE GPA > 3.8
);
UPDATE with JOIN:
sqlUPDATE e
SET e.Grade = ‘A+’
FROM Enrollments e
INNER JOIN Students s ON e.StudentID = s.StudentID
WHERE s.GPA = 4.0;
2.7 DELETE – Removing Data
Basic DELETE:
sql– Delete specific rows
DELETE FROM Students
WHERE StudentID = 5;
— Delete with condition
DELETE FROM Students
WHERE IsActive = 0 AND EnrollmentDate < ‘2020-01-01’;
— Delete all rows (be very careful!)
DELETE FROM Students;
— Delete with subquery
DELETE FROM Enrollments
WHERE StudentID IN (
SELECT StudentID FROM Students WHERE IsActive = 0
);
TRUNCATE vs DELETE:
sql– DELETE – Logged, can use WHERE, slower, can rollback
DELETE FROM Students;
— TRUNCATE – Minimal logging, no WHERE, faster, resets IDENTITY
TRUNCATE TABLE Students;
2.8 Aggregate Functions
Basic Aggregates:
sql– COUNT
SELECT COUNT(*) AS TotalStudents FROM Students;
SELECT COUNT(Phone) AS StudentsWithPhone FROM Students; — Excludes NULLs
SELECT COUNT(DISTINCT Department) AS UniqueDepartments FROM Students;
— SUM
SELECT SUM(Credits) AS TotalCredits FROM Courses;
— AVG
SELECT AVG(GPA) AS AverageGPA FROM Students;
— MIN and MAX
SELECT MIN(GPA) AS LowestGPA FROM Students;
SELECT MAX(GPA) AS HighestGPA FROM Students;
— Multiple aggregates
SELECT
COUNT() AS TotalStudents, AVG(GPA) AS AvgGPA, MIN(GPA) AS MinGPA, MAX(GPA) AS MaxGPA FROM Students; GROUP BY: sql– Group by single column SELECT Department, COUNT() AS StudentCount
FROM Students
GROUP BY Department;
— Group by multiple columns
SELECT
Department,
YEAR(EnrollmentDate) AS EnrollmentYear,
COUNT(*) AS StudentCount
FROM Students
GROUP BY Department, YEAR(EnrollmentDate);
— With aggregate functions
SELECT
Department,
AVG(GPA) AS AvgGPA,
COUNT() AS StudentCount FROM Students GROUP BY Department; HAVING – Filtering Groups: sql– Filter groups SELECT Department, COUNT() AS StudentCount
FROM Students
GROUP BY Department
HAVING COUNT(*) > 10;
— HAVING with multiple conditions
SELECT
Department,
AVG(GPA) AS AvgGPA,
COUNT() AS StudentCount FROM Students GROUP BY Department HAVING AVG(GPA) > 3.0 AND COUNT() > 5;
— WHERE vs HAVING
SELECT
Department,
AVG(GPA) AS AvgGPA
FROM Students
WHERE IsActive = 1 — Filter rows before grouping
GROUP BY Department
HAVING AVG(GPA) > 3.0; — Filter groups after aggregation
Practical Exercise 2.1:
sql– Create sample database
CREATE DATABASE CompanyDB;
USE CompanyDB;
— Create tables
CREATE TABLE Employees
(
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Department VARCHAR(50) NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
HireDate DATE NOT NULL,
ManagerID INT NULL
);
— Insert sample data
INSERT INTO Employees (FirstName, LastName, Department, Salary, HireDate, ManagerID)
VALUES
(‘John’, ‘Smith’, ‘IT’, 75000, ‘2020-01-15’, NULL),
(‘Jane’, ‘Doe’, ‘IT’, 65000, ‘2020-03-20’, 1),
(‘Mike’, ‘Johnson’, ‘HR’, 55000, ‘2019-06-10’, NULL),
(‘Sarah’, ‘Williams’, ‘HR’, 50000, ‘2021-02-14’, 3),
(‘Tom’, ‘Brown’, ‘Finance’, 70000, ‘2018-11-05’, NULL),
(‘Lisa’, ‘Anderson’, ‘Finance’, 60000, ‘2022-01-08’, 5),
(‘David’, ‘Wilson’, ‘IT’, 68000, ‘2020-09-12’, 1),
(‘Emily’, ‘Davis’, ‘HR’, 52000, ‘2021-07-22’, 3);
— Practice queries
— 1. Find all employees in IT department
— 2. Find employees hired in 2020
— 3. Calculate average salary by department
— 4. Find departments with more than 2 employees
— 5. List top 3 highest paid employees
MODULE 3: ADVANCED SQL QUERIES (12-15 hours)
3.1 JOINs – Combining Tables
Understanding Relationships:
Before JOINs, understand table relationships:
One-to-One: One record in Table A matches one record in Table B
One-to-Many: One record in Table A matches multiple records in Table B
Many-to-Many: Multiple records in Table A match multiple records in Table B
INNER JOIN:
Returns only matching records from both tables.
sql– Basic INNER JOIN
SELECT
s.FirstName,
s.LastName,
e.EnrollmentDate,
c.CourseName
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID;
— JOIN multiple tables
SELECT
s.FirstName + ‘ ‘ + s.LastName AS StudentName,
c.CourseName,
c.Credits,
e.Grade
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID
WHERE e.Grade IS NOT NULL;
— Self JOIN (employees and managers)
SELECT
e.FirstName + ‘ ‘ + e.LastName AS Employee,
m.FirstName + ‘ ‘ + m.LastName AS Manager
FROM Employees e
INNER JOIN Employees m ON e.ManagerID = m.EmployeeID;
LEFT OUTER JOIN (LEFT JOIN):
Returns all records from left table and matching records from right table.
sql– Show all students, even those without enrollments
SELECT
s.FirstName,
s.LastName,
c.CourseName
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
LEFT JOIN Courses c ON e.CourseID = c.CourseID;
— Find students with no enrollments
SELECT
s.FirstName,
s.LastName
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.EnrollmentID IS NULL;
RIGHT OUTER JOIN (RIGHT JOIN):
Returns all records from right table and matching records from left table.
sql– Show all courses, even those with no enrollments
SELECT
c.CourseName,
s.FirstName,
s.LastName
FROM Enrollments e
RIGHT JOIN Courses c ON e.CourseID = c.CourseID
LEFT JOIN Students s ON e.StudentID = s.StudentID;
FULL OUTER JOIN:
Returns all records when there’s a match in either table.
sql– Show all students and all courses
SELECT
s.FirstName,
s.LastName,
c.CourseName
FROM Students s
FULL OUTER JOIN Enrollments e ON s.StudentID = e.StudentID
FULL OUTER JOIN Courses c ON e.CourseID = c.CourseID;
CROSS JOIN:
Returns Cartesian product (all combinations).
sql– Every student with every course
SELECT
s.FirstName,
c.CourseName
FROM Students s
CROSS JOIN Courses c;
Complex JOIN Examples:
sql– Students with their total credits
SELECT
s.FirstName + ‘ ‘ + s.LastName AS StudentName,
SUM(c.Credits) AS TotalCredits,
COUNT(e.CourseID) AS CoursesEnrolled
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
LEFT JOIN Courses c ON e.CourseID = c.CourseID
GROUP BY s.StudentID, s.FirstName, s.LastName;
— Courses with enrollment statistics
SELECT
c.CourseName,
c.Credits,
COUNT(e.StudentID) AS EnrolledStudents,
AVG(CAST(s.GPA AS FLOAT)) AS AvgStudentGPA
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
LEFT JOIN Students s ON e.StudentID = s.StudentID
GROUP BY c.CourseID, c.CourseName, c.Credits;
3.2 Subqueries
Subquery in WHERE Clause:
sql– Students with GPA above average
SELECT FirstName, LastName, GPA
FROM Students
WHERE GPA > (SELECT AVG(GPA) FROM Students);
— Students enrolled in ‘CS101’
SELECT FirstName, LastName
FROM Students
WHERE StudentID IN (
SELECT StudentID
FROM Enrollments
WHERE CourseID = (
SELECT CourseID FROM Courses WHERE CourseCode = ‘CS101’
)
);
— Using EXISTS
SELECT FirstName, LastName
FROM Students s
WHERE EXISTS (
SELECT 1
FROM Enrollments e
WHERE e.StudentID = s.StudentID
);
— Using NOT EXISTS
SELECT FirstName, LastName
FROM Students s
WHERE NOT EXISTS (
SELECT 1
FROM Enrollments e
WHERE e.StudentID = s.StudentID
);
— Using ANY
SELECT CourseName
FROM Courses
WHERE Credits > ANY (
SELECT Credits FROM Courses WHERE Department = ‘Mathematics’
);
— Using ALL
SELECT CourseName
FROM Courses
WHERE Credits > ALL (
SELECT Credits FROM Courses WHERE Department = ‘Mathematics’
);
Subquery in SELECT Clause:
sqlSELECT
FirstName,
LastName,
(SELECT COUNT(*)
FROM Enrollments e
WHERE e.StudentID = s.StudentID) AS CourseCount
FROM Students s;
— Multiple correlated subqueries
SELECT
c.CourseName,
(SELECT COUNT(*)
FROM Enrollments e
WHERE e.CourseID = c.CourseID) AS EnrolledCount,
(SELECT AVG(CAST(GPA AS FLOAT))
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.CourseID = c.CourseID) AS AvgGPA
FROM Courses c;
Subquery in FROM Clause (Derived Tables):
sqlSELECT
Department,
AvgGPA
FROM (
SELECT
Department,
AVG(GPA) AS AvgGPA
FROM Students
GROUP BY Department
) AS DeptAvg
WHERE AvgGPA > 3.0;
3.3 Common Table Expressions (CTEs)
Basic CTE:
sql– Simple CTE
WITH HighPerformers AS (
SELECT FirstName, LastName, GPA
FROM Students
WHERE GPA > 3.5
)
SELECT * FROM HighPerformers
ORDER BY GPA DESC;
— Multiple CTEs
WITH
ActiveStudents AS (
SELECT * FROM Students WHERE IsActive = 1
),
CourseEnrollments AS (
SELECT
StudentID,
COUNT(*) AS CourseCount
FROM Enrollments
GROUP BY StudentID
)
SELECT
a.FirstName,
a.LastName,
a.GPA,
ISNULL(c.CourseCount, 0) AS TotalCourses
FROM ActiveStudents a
LEFT JOIN CourseEnrollments c ON a.StudentID = c.StudentID;
Recursive CTE:
sql– Employee hierarchy
WITH EmployeeHierarchy AS (
— Anchor member: Top-level employees
SELECT
EmployeeID,
FirstName,
LastName,
ManagerID,
1 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member: Employees with managers
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
REPLICATE(‘ ‘, Level – 1) + FirstName + ‘ ‘ + LastName AS EmployeeName,
Level
FROM EmployeeHierarchy
ORDER BY Level, FirstName;
— Number sequence
WITH Numbers AS (
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers
WHERE Num < 100
)
SELECT Num FROM Numbers
OPTION (MAXRECURSION 100);
3.4 UNION, INTERSECT, EXCEPT
UNION:
Combines results from multiple queries (removes duplicates).
sql– Combine students and employees
SELECT FirstName, LastName, ‘Student’ AS Type
FROM Students
UNION
SELECT FirstName, LastName, ‘Employee’ AS Type
FROM Employees;
— UNION ALL (includes duplicates)
SELECT Department FROM Students
UNION ALL
SELECT Department FROM Employees;
INTERSECT:
Returns common rows between two queries.
sql– Departments that have both students and employees
SELECT Department FROM Students
INTERSECT
SELECT Department FROM Employees;
EXCEPT:
Returns rows from first query not in second query.
sql– Departments with students but no employees
SELECT Department FROM Students
EXCEPT
SELECT Department FROM Employees;
3.5 Window Functions
ROW_NUMBER:
sql– Assign row numbers
SELECT
FirstName,
LastName,
GPA,
ROW_NUMBER() OVER (ORDER BY GPA DESC) AS Rank
FROM Students;
— Row number within partition
SELECT
Department,
FirstName,
LastName,
GPA,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY GPA DESC) AS DeptRank
FROM Students;
RANK and DENSE_RANK:
sql– RANK (leaves gaps after ties)
SELECT
FirstName,
LastName,
GPA,
RANK() OVER (ORDER BY GPA DESC) AS Rank
FROM Students;
— DENSE_RANK (no gaps)
SELECT
FirstName,
LastName,
GPA,
DENSE_RANK() OVER (ORDER BY GPA DESC) AS DenseRank
FROM Students;
— Comparison
SELECT
FirstName,
LastName,
GPA,
ROW_NUMBER() OVER (ORDER BY GPA DESC) AS RowNum,
RANK() OVER (ORDER BY GPA DESC) AS Rank,
DENSE_RANK() OVER (ORDER BY GPA DESC) AS DenseRank
FROM Students;
NTILE:
sql– Divide into quartiles
SELECT
FirstName,
LastName,
GPA,
NTILE(4) OVER (ORDER BY GPA DESC) AS Quartile
FROM Students;
Aggregate Window Functions:
sql– Running totals
SELECT
EnrollmentDate,
StudentID,
SUM(1) OVER (ORDER BY EnrollmentDate) AS RunningTotal
FROM Enrollments;
— Moving average
SELECT
Department,
Salary,
AVG(Salary) OVER (
PARTITION BY Department
ORDER BY Salary
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS MovingAvg
FROM Employees;
LAG and LEAD:
sql– Previous and next values
SELECT
EmployeeID,
Salary,
LAG(Salary, 1) OVER (ORDER BY Salary) AS PreviousSalary,
LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;
— Calculate differences
SELECT
EmployeeID,
Salary,
Salary – LAG(Salary, 1) OVER (ORDER BY Salary) AS SalaryDifference
FROM Employees;
FIRST_VALUE and LAST_VALUE:
sqlSELECT
Department,
FirstName,
Salary,
FIRST_VALUE(Salary) OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS HighestInDept,
LAST_VALUE(Salary) OVER (
PARTITION BY Department
ORDER BY Salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LowestInDept
FROM Employees;
3.6 PIVOT and UNPIVOT
PIVOT:
sql– Convert rows to columns
SELECT *
FROM (
SELECT Department, YEAR(HireDate) AS HireYear
FROM Employees
) AS SourceTable
PIVOT (
COUNT(HireYear)
FOR HireYear IN ([2018], [2019], [2020], [2021], [2022])
) AS PivotTable;
— With aggregation
SELECT *
FROM (
SELECT Department, Salary
FROM Employees
) AS SourceTable
PIVOT (
AVG(Salary)
FOR Department IN ([IT], [HR], [Finance])
) AS PivotTable;
UNPIVOT:
-- Convert columns to rows
CREATE TABLE SalesData (
Product VARCHAR(50),
Q1 INT,
Q2 INT,
Q3 INT,
Q4 INT
);
INSERT INTO SalesData VALUES
('Product A', 100, 150, 200, 180),
('Product B', 80, 90, 110, 120);
SELECT Product, Quarter, Sales
FROM SalesData
UNPIVOT (
Sales FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;Practical Exercise 3.1:
-- Complex query combining multiple concepts
WITH EnrollmentStats AS (
SELECT
c.CourseID,
c.CourseName,
c.Department,
COUNT(e.StudentID) AS StudentCount,
AVG(CAST(s.GPA AS FLOAT)) AS AvgGPA
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
LEFT JOIN Students s ON e.StudentID = s.StudentID
GROUP BY c.CourseID, c.CourseName, c.Department
),
RankedCourses AS (
SELECT
*,
RANK() OVER (PARTITION BY Department ORDER BY StudentCount DESC) AS PopularityRank
FROM EnrollmentStats
)
SELECT
Department,
CourseName,
StudentCount,
AvgGPA,
PopularityRank
FROM RankedCourses
WHERE PopularityRank <= 3
ORDER BY Department, PopularityRank;MODULE 4: DATABASE DESIGN & OBJECTS (10-12 hours)
4.1 Normalization
What is Normalization?
Process of organizing data to reduce redundancy and improve data integrity.
First Normal Form (1NF):
- Each column contains atomic (indivisible) values
- Each column contains values of same type
- Each column has unique name
- Order of data doesn’t matter
-- Violation of 1NF
CREATE TABLE BadStudents (
StudentID INT,
StudentName VARCHAR(100),
Courses VARCHAR(500) -- Multiple courses in one field: "CS101, MATH201"
);
-- Corrected to 1NF
CREATE TABLE Students (
StudentID INT,
StudentName VARCHAR(100)
);
CREATE TABLE StudentCourses (
StudentID INT,
CourseCode VARCHAR(10)
);Second Normal Form (2NF):
- Must be in 1NF
- All non-key attributes fully depend on primary key
-- Violation of 2NF (composite key with partial dependency)
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
ProductName VARCHAR(100), -- Depends only on ProductID
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);
-- Corrected to 2NF
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE
);
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100)
);
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);Third Normal Form (3NF):
- Must be in 2NF
- No transitive dependencies (non-key columns depend on other non-key columns)
-- Violation of 3NF
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT,
DepartmentName VARCHAR(50), -- Depends on DepartmentID, not EmployeeID
DepartmentLocation VARCHAR(100) -- Depends on DepartmentID
);
-- Corrected to 3NF
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50),
DepartmentLocation VARCHAR(100)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);Boyce-Codd Normal Form (BCNF) and Higher:
More advanced normalization forms that handle specific edge cases. Generally, 3NF is sufficient for most applications.
4.2 Indexes
What are Indexes?
Data structures that improve query performance by allowing faster data retrieval.
Clustered Index:
- Determines physical order of data in table
- One per table
- Usually created on primary key
-- Clustered index (automatically created with PRIMARY KEY)
CREATE TABLE Products (
ProductID INT PRIMARY KEY, -- Clustered index
ProductName VARCHAR(100),
Price DECIMAL(10,2)
);
-- Explicit clustered index
CREATE TABLE Orders (
OrderID INT,
OrderDate DATE,
CustomerID INT
);
CREATE CLUSTERED INDEX IX_Orders_OrderID
ON Orders(OrderID);Non-Clustered Index:
- Separate structure from table data
- Multiple non-clustered indexes per table
- Contains key values and pointers to data
-- Single column index
CREATE NONCLUSTERED INDEX IX_Students_LastName
ON Students(LastName);
-- Composite index
CREATE NONCLUSTERED INDEX IX_Students_Name
ON Students(LastName, FirstName);
-- Index with included columns
CREATE NONCLUSTERED INDEX IX_Students_Email
ON Students(Email)
INCLUDE (FirstName, LastName, Phone);
-- Filtered index
CREATE NONCLUSTERED INDEX IX_ActiveStudents
ON Students(EnrollmentDate)
WHERE IsActive = 1;
-- Unique index
CREATE UNIQUE NONCLUSTERED INDEX IX_Students_Email_Unique
ON Students(Email);Index Management:
-- View indexes on a table
SELECT
i.name AS IndexName,
i.type_desc AS IndexType,
COL_NAME(ic.object_id, ic.column_id) AS ColumnName
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('Students');
-- Rebuild index
ALTER INDEX IX_Students_LastName ON Students REBUILD;
-- Reorganize index
ALTER INDEX IX_Students_LastName ON Students REORGANIZE;
-- Disable index
ALTER INDEX IX_Students_LastName ON Students DISABLE;
-- Enable index
ALTER INDEX IX_Students_LastName ON Students REBUILD;
-- Drop index
DROP INDEX IX_Students_LastName ON Students;
-- Index statistics
EXEC sp_helpindex 'Students';When to Use Indexes:
- Columns used frequently in WHERE clauses
- Columns used in JOIN conditions
- Columns used in ORDER BY
- Foreign key columns
- Columns used in GROUP BY
When NOT to Use Indexes:
- Small tables (overhead > benefit)
- Columns with frequent INSERT/UPDATE/DELETE
- Columns with low selectivity (few distinct values)
- Columns not used in queries
4.3 Views
What are Views?
Virtual tables based on SELECT queries. They don’t store data but provide a way to simplify complex queries and enhance security.
Creating Views:
-- Simple view
CREATE VIEW vw_ActiveStudents AS
SELECT
StudentID,
FirstName,
LastName,
Email,
GPA
FROM Students
WHERE IsActive = 1;
-- Using the view
SELECT * FROM vw_ActiveStudents WHERE GPA > 3.5;
-- Complex view with JOINs
CREATE VIEW vw_StudentEnrollments AS
SELECT
s.StudentID,
s.FirstName + ' ' + s.LastName AS StudentName,
c.CourseName,
c.Credits,
e.Grade,
e.EnrollmentDate
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID;
-- View with aggregations
CREATE VIEW vw_DepartmentStats AS
SELECT
Department,
COUNT(*) AS StudentCount,
AVG(GPA) AS AvgGPA,
MIN(GPA) AS MinGPA,
MAX(GPA) AS MaxGPA
FROM Students
WHERE IsActive = 1
GROUP BY Department;Modifying Views:
-- ALTER VIEW
ALTER VIEW vw_ActiveStudents AS
SELECT
StudentID,
FirstName,
LastName,
Email,
GPA,
Department
FROM Students
WHERE IsActive = 1 AND GPA >= 2.0;
-- DROP VIEW
DROP VIEW vw_ActiveStudents;Updatable Views:
-- Insert through view
INSERT INTO vw_ActiveStudents (FirstName, LastName, Email, GPA)
VALUES ('New', 'Student', 'new@email.com', 3.5);
-- Update through view
UPDATE vw_ActiveStudents
SET GPA = 3.7
WHERE StudentID = 1;
-- Delete through view
DELETE FROM vw_ActiveStudents WHERE StudentID = 5;
-- WITH CHECK OPTION
CREATE VIEW vw_HighPerformers AS
SELECT StudentID, FirstName, LastName, GPA
FROM Students
WHERE GPA >= 3.5
WITH CHECK OPTION; -- Prevents updates that violate WHERE clauseIndexed Views (Materialized Views):
-- Create view with SCHEMABINDING
CREATE VIEW vw_CourseSummary
WITH SCHEMABINDING
AS
SELECT
c.CourseID,
c.CourseName,
COUNT_BIG(*) AS EnrollmentCount,
SUM(c.Credits) AS TotalCredits
FROM dbo.Courses c
INNER JOIN dbo.Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.CourseID, c.CourseName;
-- Create unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX IX_vw_CourseSummary
ON vw_CourseSummary(CourseID);4.4 Stored Procedures
What are Stored Procedures?
Pre-compiled SQL statements stored in database. They improve performance, security, and code reusability.
Creating Stored Procedures:
-- Simple stored procedure
CREATE PROCEDURE usp_GetAllStudents
AS
BEGIN
SELECT * FROM Students;
END;
-- Execute procedure
EXEC usp_GetAllStudents;
-- or
EXECUTE usp_GetAllStudents;
-- Procedure with parameters
CREATE PROCEDURE usp_GetStudentsByDepartment
@Department VARCHAR(50)
AS
BEGIN
SELECT
FirstName,
LastName,
GPA
FROM Students
WHERE Department = @Department
ORDER BY GPA DESC;
END;
-- Execute with parameter
EXEC usp_GetStudentsByDepartment 'Computer Science';
EXEC usp_GetStudentsByDepartment @Department = 'Mathematics';
-- Multiple parameters
CREATE PROCEDURE usp_GetStudentsByGPARange
@MinGPA DECIMAL(3,2),
@MaxGPA DECIMAL(3,2)
AS
BEGIN
SELECT
FirstName,
LastName,
GPA,
Department
FROM Students
WHERE GPA BETWEEN @MinGPA AND @MaxGPA
ORDER BY GPA DESC;
END;
EXEC usp_GetStudentsByGPARange 3.0, 4.0;
-- Default parameter values
CREATE PROCEDURE usp_GetRecentEnrollments
@Days INT = 30 -- Default value
AS
BEGIN
SELECT
s.FirstName + ' ' + s.LastName AS StudentName,
c.CourseName,
e.EnrollmentDate
FROM Enrollments e
INNER JOIN Students s ON e.StudentID = s.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID
WHERE e.EnrollmentDate >= DATEADD(DAY, -@Days, GETDATE())
ORDER BY e.EnrollmentDate DESC;
END;
EXEC usp_GetRecentEnrollments; -- Uses default 30 days
EXEC usp_GetRecentEnrollments 60; -- Uses 60 daysOutput Parameters:
CREATE PROCEDURE usp_GetStudentCount
@Department VARCHAR(50),
@StudentCount INT OUTPUT
AS
BEGIN
SELECT @StudentCount = COUNT(*)
FROM Students
WHERE Department = @Department;
END;
-- Execute with output parameter
DECLARE @Count INT;
EXEC usp_GetStudentCount 'Computer Science', @Count OUTPUT;
SELECT @Count AS StudentCount;INSERT/UPDATE/DELETE Procedures:
-- Insert procedure
CREATE PROCEDURE usp_AddStudent
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Email VARCHAR(100),
@DateOfBirth DATE,
@Department VARCHAR(50),
@GPA DECIMAL(3,2)
AS
BEGIN
SET NOCOUNT ON; -- Don't return row count
INSERT INTO Students (FirstName, LastName, Email, DateOfBirth, Department, GPA, IsActive)
VALUES (@FirstName, @LastName, @Email, @DateOfBirth, @Department, @GPA, 1);
SELECT SCOPE_IDENTITY() AS NewStudentID;
END;
-- Update procedure
CREATE PROCEDURE usp_UpdateStudentGPA
@StudentID INT,
@NewGPA DECIMAL(3,2)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM Students WHERE StudentID = @StudentID)
BEGIN
UPDATE Students
SET GPA = @NewGPA
WHERE StudentID = @StudentID;
SELECT 'Student GPA updated successfully' AS Message;
END
ELSE
BEGIN
SELECT 'Student not found' AS Message;
END
END;
-- Delete procedure
CREATE PROCEDURE usp_DeleteStudent
@StudentID INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
DELETE FROM Enrollments WHERE StudentID = @StudentID;
DELETE FROM Students WHERE StudentID = @StudentID;
SELECT 'Student deleted successfully' AS Message;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;Error Handling:
CREATE PROCEDURE usp_EnrollStudent
@StudentID INT,
@CourseID INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Check if student exists
IF NOT EXISTS (SELECT 1 FROM Students WHERE StudentID = @StudentID)
BEGIN
THROW 50001, 'Student not found', 1;
END
-- Check if course exists
IF NOT EXISTS (SELECT 1 FROM Courses WHERE CourseID = @CourseID)
BEGIN
THROW 50002, 'Course not found', 1;
END
-- Check if already enrolled
IF EXISTS (SELECT 1 FROM Enrollments
WHERE StudentID = @StudentID AND CourseID = @CourseID)
BEGIN
THROW 50003, 'Student already enrolled in this course', 1;
END
-- Enroll student
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)
VALUES (@StudentID, @CourseID, GETDATE());
COMMIT TRANSACTION;
SELECT 'Enrollment successful' AS Message;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;Modifying Procedures:
-- ALTER PROCEDURE
ALTER PROCEDURE usp_GetAllStudents
AS
BEGIN
SELECT
StudentID,
FirstName,
LastName,
Email,
GPA
FROM Students
WHERE IsActive = 1
ORDER BY LastName, FirstName;
END;
-- DROP PROCEDURE
DROP PROCEDURE usp_GetAllStudents;
-- View procedure definition
EXEC sp_helptext 'usp_GetAllStudents';4.5 Functions
Scalar Functions:
Return single value.
-- Simple scalar function
CREATE FUNCTION dbo.fn_GetFullName
(
@FirstName VARCHAR(50),
@LastName VARCHAR(50)
)
RETURNS VARCHAR(101)
AS
BEGIN
RETURN @FirstName + ' ' + @LastName;
END;
-- Using function
SELECT dbo.fn_GetFullName(FirstName, LastName) AS FullName
FROM Students;
-- Function with calculations
CREATE FUNCTION dbo.fn_CalculateAge
(
@DateOfBirth DATE
)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF(YEAR, @DateOfBirth, GETDATE()) -
CASE
WHEN MONTH(@DateOfBirth) > MONTH(GETDATE()) OR
(MONTH(@DateOfBirth) = MONTH(GETDATE()) AND DAY(@DateOfBirth) > DAY(GETDATE()))
THEN 1
ELSE 0
END;
END;
SELECT
FirstName,
LastName,
DateOfBirth,
dbo.fn_CalculateAge(DateOfBirth) AS Age
FROM Students;
-- Function with business logic
CREATE FUNCTION dbo.fn_GetGradeFromGPA
(
@GPA DECIMAL(3,2)
)
RETURNS CHAR(2)
AS
BEGIN
DECLARE @Grade CHAR(2);
SET @Grade = CASE
WHEN @GPA >= 3.7 THEN 'A'
WHEN @GPA >= 3.3 THEN 'A-'
WHEN @GPA >= 3.0 THEN 'B+'
WHEN @GPA >= 2.7 THEN 'B'
WHEN @GPA >= 2.3 THEN 'B-'
WHEN @GPA >= 2.0 THEN 'C+'
WHEN @GPA >= 1.7 THEN 'C'
WHEN @GPA >= 1.3 THEN 'C-'
WHEN @GPA >= 1.0 THEN 'D'
ELSE 'F'
END;
RETURN @Grade;
END;Table-Valued Functions:
-- Inline table-valued function
CREATE FUNCTION dbo.fn_GetStudentsByDepartment
(
@Department VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT
StudentID,
FirstName,
LastName,
Email,
GPA
FROM Students
WHERE Department = @Department AND IsActive = 1
);
-- Using table-valued function
SELECT * FROM dbo.fn_GetStudentsByDepartment('Computer Science');
-- Multi-statement table-valued function
CREATE FUNCTION dbo.fn_GetStudentStatistics
(
@MinGPA DECIMAL(3,2)
)
RETURNS @ResultTable TABLE
(
Department VARCHAR(50),
StudentCount INT,
AvgGPA DECIMAL(4,2),
MaxGPA DECIMAL(3,2)
)
AS
BEGIN
INSERT INTO @ResultTable
SELECT
Department,
COUNT(*) AS StudentCount,
AVG(GPA) AS AvgGPA,
MAX(GPA) AS MaxGPA
FROM Students
WHERE GPA >= @MinGPA AND IsActive = 1
GROUP BY Department;
RETURN;
END;
SELECT * FROM dbo.fn_GetStudentStatistics(3.0);Function Management:
-- Modify function
ALTER FUNCTION dbo.fn_GetFullName
(
@FirstName VARCHAR(50),
@LastName VARCHAR(50)
)
RETURNS VARCHAR(101)
AS
BEGIN
RETURN UPPER(@LastName) + ', ' + @FirstName;
END;
-- Drop function
DROP FUNCTION dbo.fn_GetFullName;4.6 Triggers
What are Triggers?
Special stored procedures that automatically execute when specific events occur.
DML Triggers (AFTER/FOR):
-- AFTER INSERT trigger
CREATE TRIGGER trg_Students_AfterInsert
ON Students
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO AuditLog (TableName, Action, ActionDate, UserName)
SELECT
'Students',
'INSERT',
GETDATE(),
SYSTEM_USER
FROM inserted;
END;
-- AFTER UPDATE trigger
CREATE TRIGGER trg_Students_AfterUpdate
ON Students
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Log GPA changes
IF UPDATE(GPA)
BEGIN
INSERT INTO GPAHistory (StudentID, OldGPA, NewGPA, ChangeDate)
SELECT
i.StudentID,
d.GPA AS OldGPA,
i.GPA AS NewGPA,
GETDATE()
FROM inserted i
INNER JOIN deleted d ON i.StudentID = d.StudentID
WHERE i.GPA <> d.GPA;
END
END;
-- AFTER DELETE trigger
CREATE TRIGGER trg_Students_AfterDelete
ON Students
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
-- Archive deleted students
INSERT INTO ArchivedStudents
SELECT *, GETDATE() AS DeletedDate
FROM deleted;
END;
-- Multiple operations trigger
CREATE TRIGGER trg_Students_AuditAll
ON Students
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Action VARCHAR(10);
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @Action = 'UPDATE';
ELSE IF EXISTS (SELECT * FROM inserted)
SET @Action = 'INSERT';
ELSE
SET @Action = 'DELETE';
INSERT INTO AuditLog (TableName, Action, ActionDate, UserName, RecordCount)
SELECT
'Students',
@Action,
GETDATE(),
SYSTEM_USER,
@@ROWCOUNT;
END;INSTEAD OF Triggers:
-- INSTEAD OF DELETE trigger
CREATE TRIGGER trg_Students_InsteadOfDelete
ON Students
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
-- Soft delete: mark as inactive instead of deleting
UPDATE Students
SET IsActive = 0
WHERE StudentID IN (SELECT StudentID FROM deleted);
PRINT 'Students marked as inactive instead of deleted';
END;
-- INSTEAD OF INSERT trigger with validation
CREATE TRIGGER trg_Students_InsteadOfInsert
ON Students
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Validate email format
IF EXISTS (SELECT 1 FROM inserted WHERE Email NOT LIKE '%@%.%')
BEGIN
RAISERROR('Invalid email format', 16, 1);
RETURN;
END
-- Validate GPA range
IF EXISTS (SELECT 1 FROM inserted WHERE GPA < 0 OR GPA > 4.0)
BEGIN
RAISERROR('GPA must be between 0 and 4.0', 16, 1);
RETURN;
END
-- Insert valid records
INSERT INTO Students (FirstName, LastName, Email, DateOfBirth, Department, GPA, IsActive)
SELECT FirstName, LastName, Email, DateOfBirth, Department, GPA, ISNULL(IsActive, 1)
FROM inserted;
END;DDL Triggers:
-- Database-level DDL trigger
CREATE TRIGGER trg_PreventTableDrop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT 'Table drop operations are not allowed';
ROLLBACK;
END;
-- Server-level DDL trigger
CREATE TRIGGER trg_AuditDatabaseCreation
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
DECLARE @EventData XML = EVENTDATA();
INSERT INTO ServerAudit (EventType, EventData, EventDate)
VALUES ('CREATE_DATABASE', @EventData, GETDATE());
END;Trigger Management:
-- Disable trigger
DISABLE TRIGGER trg_Students_AfterInsert ON Students;
-- Enable trigger
ENABLE TRIGGER trg_Students_AfterInsert ON Students;
-- Modify trigger
ALTER TRIGGER trg_Students_AfterInsert
ON Students
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- New logic here
END;
-- Drop trigger
DROP TRIGGER trg_Students_AfterInsert;
-- View trigger definition
EXEC sp_helptext 'trg_Students_AfterInsert';
-- List all triggers on a table
SELECT name, is_disabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID('Students');Practical Exercise 4.1:
-- Create comprehensive audit system
CREATE TABLE AuditLog (
AuditID INT PRIMARY KEY IDENTITY(1,1),
TableName VARCHAR(100),
Operation VARCHAR(10),
ChangedBy VARCHAR(100),
ChangedDate DATETIME,
OldValues NVARCHAR(MAX),
NewValues NVARCHAR(MAX)
);
CREATE TRIGGER trg_Employees_Audit
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Operation VARCHAR(10);
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @Operation = 'UPDATE';
ELSE IF EXISTS (SELECT * FROM inserted)
SET @Operation = 'INSERT';
ELSE
SET @Operation = 'DELETE';
INSERT INTO AuditLog (TableName, Operation, ChangedBy, ChangedDate, OldValues, NewValues)
SELECT
'Employees',
@Operation,
SYSTEM_USER,
GETDATE(),
(SELECT * FROM deleted FOR JSON PATH),
(SELECT * FROM inserted FOR JSON PATH);
END;MODULE 5: TRANSACTIONS & CONCURRENCY (8-10 hours)
5.1 Transactions
What is a Transaction?
A sequence of operations performed as a single logical unit of work. Must satisfy ACID properties:
- Atomicity: All or nothing
- Consistency: Data integrity maintained
- Isolation: Transactions don’t interfere with each other
- Durability: Changes persist after commit
Basic Transaction Syntax:
-- Simple transaction
BEGIN TRANSACTION;
UPDATE Students
SET GPA = 3.8
WHERE StudentID = 1;
COMMIT TRANSACTION;
-- Transaction with rollback
BEGIN TRANSACTION;
DELETE FROM Enrollments WHERE StudentID = 5;
DELETE FROM Students WHERE StudentID = 5;
-- Something went wrong, undo everything
ROLLBACK TRANSACTION;
-- Transaction with error handling
BEGIN TRANSACTION;
BEGIN TRY
UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';
UPDATE Employees SET Salary = Salary * 1.05 WHERE Department = 'HR';
COMMIT TRANSACTION;
PRINT 'Salary update successful';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;Savepoints:
BEGIN TRANSACTION;
INSERT INTO Students (FirstName, LastName, Email, DateOfBirth, Department, GPA)
VALUES ('John', 'Doe', 'john@email.com', '2000-01-01', 'CS', 3.5);
SAVE TRANSACTION SavePoint1;
UPDATE Students SET GPA = 3.7 WHERE StudentID = 1;
SAVE TRANSACTION SavePoint2;
DELETE FROM Enrollments WHERE StudentID = 2;
-- Rollback to SavePoint2 (keeps updates before SavePoint2)
ROLLBACK TRANSACTION SavePoint2;
COMMIT TRANSACTION;Named Transactions:
BEGIN TRANSACTION SalaryUpdate
WITH MARK 'Annual salary increase';
UPDATE Employees
SET Salary = Salary * 1.10;
COMMIT TRANSACTION SalaryUpdate;Checking Transaction State:
-- Check if in transaction
SELECT @@TRANCOUNT AS ActiveTransactions;
-- Transaction isolation level
DBCC USEROPTIONS;5.2 Isolation Levels
Understanding Concurrency Issues:
- Dirty Read: Reading uncommitted data from another transaction
- Non-Repeatable Read: Data changes between reads in same transaction
- Phantom Read: New rows appear between reads in same transaction
Isolation Levels:
-- READ UNCOMMITTED (lowest isolation, highest concurrency)
-- Allows: Dirty reads, non-repeatable reads, phantom reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Students; -- Can see uncommitted changes
COMMIT;
-- READ COMMITTED (default in SQL Server)
-- Prevents: Dirty reads
-- Allows: Non-repeatable reads, phantom reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Students WHERE StudentID = 1;
-- If another transaction updates this row, we won't see it until committed
SELECT * FROM Students WHERE StudentID = 1;
COMMIT;
-- REPEATABLE READ
-- Prevents: Dirty reads, non-repeatable reads
-- Allows: Phantom reads
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Students WHERE GPA > 3.5;
-- Data won't change, but new rows matching criteria might appear
SELECT * FROM Students WHERE GPA > 3.5;
COMMIT;
-- SERIALIZABLE (highest isolation, lowest concurrency)
-- Prevents: All concurrency issues
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Students WHERE GPA > 3.5;
-- No changes possible to selected rows or new matching rows
SELECT * FROM Students WHERE GPA > 3.5;
COMMIT;
-- SNAPSHOT
-- Uses row versioning, no locks on reads
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Students;
-- Reads consistent snapshot from transaction start
SELECT * FROM Students;
COMMIT;
-- Enable snapshot isolation on database
ALTER DATABASE SchoolDB
SET ALLOW_SNAPSHOT_ISOLATION ON;
-- READ COMMITTED SNAPSHOT
ALTER DATABASE SchoolDB
SET READ_COMMITTED_SNAPSHOT ON;Practical Isolation Examples:
-- Session 1: Update with delay
BEGIN TRANSACTION;
UPDATE Students SET GPA = 4.0 WHERE StudentID = 1;
WAITFOR DELAY '00:00:10'; -- Wait 10 seconds
COMMIT;
-- Session 2: Try to read (run simultaneously)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT GPA FROM Students WHERE StudentID = 1; -- Sees uncommitted 4.0
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT GPA FROM Students WHERE StudentID = 1; -- Waits for commit5.3 Locking
Lock Types:
- Shared (S) – Read locks
- Exclusive (X) – Write locks
- Update (U) – Intent to update
- Intent – Hierarchy locks (IS, IX, IU)
- Schema – DDL locks
- Bulk Update (BU) – Bulk operations
Lock Granularity:
RID (Row Identifier) – Single row lock
Key – Index key lock
Page – 8KB page lock
Extent – 8 pages (64KB)
Table – Entire table
Database – Entire database
Viewing Locks:
sql– Current locks
SELECT
resource_type,
resource_database_id,
resource_description,
request_mode,
request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
— Active blocking
SELECT
blocking.session_id AS BlockingSessionID,
blocked.session_id AS BlockedSessionID,
blocking_text.text AS BlockingSQL,
blocked_text.text AS BlockedSQL
FROM sys.dm_exec_requests blocked
INNER 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_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text;
— sp_who2 – Active processes
EXEC sp_who2;
— sp_lock – Lock information
EXEC sp_lock;
Lock Hints:
sql– NOLOCK (same as READ UNCOMMITTED)
SELECT * FROM Students WITH (NOLOCK);
— HOLDLOCK (hold shared locks until transaction ends)
SELECT * FROM Students WITH (HOLDLOCK) WHERE StudentID = 1;
— ROWLOCK (force row-level locks)
UPDATE Students WITH (ROWLOCK)
SET GPA = 3.9
WHERE StudentID = 1;
— TABLOCKX (exclusive table lock)
SELECT * FROM Students WITH (TABLOCKX);
— UPDLOCK (take update lock immediately)
SELECT * FROM Students WITH (UPDLOCK) WHERE StudentID = 1;
— XLOCK (exclusive lock)
SELECT * FROM Students WITH (XLOCK) WHERE StudentID = 1;
— READPAST (skip locked rows)
SELECT * FROM Students WITH (READPAST);
Deadlock Example and Prevention:
sql– Session 1
BEGIN TRANSACTION;
UPDATE Students SET GPA = 3.8 WHERE StudentID = 1;
WAITFOR DELAY ’00:00:05′;
UPDATE Courses SET Credits = 4 WHERE CourseID = 1;
COMMIT;
— Session 2 (run simultaneously)
BEGIN TRANSACTION;
UPDATE Courses SET Credits = 3 WHERE CourseID = 1;
WAITFOR DELAY ’00:00:05′;
UPDATE Students SET GPA = 3.7 WHERE StudentID = 1;
COMMIT;
— This will cause a deadlock!
— Deadlock Prevention: Access tables in same order
— Session 1
BEGIN TRANSACTION;
UPDATE Students SET GPA = 3.8 WHERE StudentID = 1;
UPDATE Courses SET Credits = 4 WHERE CourseID = 1;
COMMIT;
— Session 2
BEGIN TRANSACTION;
UPDATE Students SET GPA = 3.7 WHERE StudentID = 1;
UPDATE Courses SET Credits = 3 WHERE CourseID = 1;
COMMIT;
— Set deadlock priority
SET DEADLOCK_PRIORITY HIGH; — or LOW, NORMAL, or -10 to 10
— Deadlock monitoring
— Enable trace flag for deadlock graph
DBCC TRACEON(1222, -1);
— View deadlock information
SELECT
deadlock_time,
deadlock_graph
FROM sys.dm_tran_deadlock_events;
5.4 Transaction Best Practices
Keep Transactions Short:
sql– Bad: Long transaction
BEGIN TRANSACTION;
SELECT * FROM LargeTable; — Lots of processing
— … complex business logic …
WAITFOR DELAY ’00:05:00′; — Don’t do this!
UPDATE Students SET GPA = 3.8;
COMMIT;
— Good: Short transaction
— Do processing outside transaction
DECLARE @StudentID INT = 1;
DECLARE @NewGPA DECIMAL(3,2) = 3.8;
— … complex business logic here …
— Quick transaction
BEGIN TRANSACTION;
UPDATE Students SET GPA = @NewGPA WHERE StudentID = @StudentID;
COMMIT;
Explicit vs Implicit Transactions:
sql– Explicit (recommended for control)
BEGIN TRANSACTION;
— statements
COMMIT;
— Implicit (auto-commit each statement)
SET IMPLICIT_TRANSACTIONS OFF; — Default in SQL Server
— Turn on implicit transactions
SET IMPLICIT_TRANSACTIONS ON;
UPDATE Students SET GPA = 3.8;
COMMIT; — Must explicitly commit
Error Handling in Transactions:
sqlCREATE PROCEDURE usp_TransferCredits
@FromStudentID INT,
@ToStudentID INT,
@CourseID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMessage NVARCHAR(4000);
BEGIN TRY
BEGIN TRANSACTION;
-- Validate students exist
IF NOT EXISTS (SELECT 1 FROM Students WHERE StudentID = @FromStudentID)
THROW 50001, 'Source student not found', 1;
IF NOT EXISTS (SELECT 1 FROM Students WHERE StudentID = @ToStudentID)
THROW 50002, 'Destination student not found', 1;
-- Remove from first student
DELETE FROM Enrollments
WHERE StudentID = @FromStudentID AND CourseID = @CourseID;
IF @@ROWCOUNT = 0
THROW 50003, 'Student not enrolled in course', 1;
-- Add to second student
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)
VALUES (@ToStudentID, @CourseID, GETDATE());
COMMIT TRANSACTION;
PRINT 'Credit transfer successful';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @ErrorMessage = ERROR_MESSAGE();
PRINT 'Error: ' + @ErrorMessage;
THROW;
END CATCH;END;
Practical Exercise 5.1:
sql– Create a banking scenario
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY IDENTITY(1,1),
AccountName VARCHAR(100),
Balance DECIMAL(18,2)
);
CREATE TABLE TransactionLog (
TransactionID INT PRIMARY KEY IDENTITY(1,1),
FromAccount INT,
ToAccount INT,
Amount DECIMAL(18,2),
TransactionDate DATETIME,
Status VARCHAR(20)
);
INSERT INTO Accounts VALUES (‘Account A’, 1000.00), (‘Account B’, 500.00);
— Money transfer procedure with proper transaction handling
CREATE PROCEDURE usp_TransferMoney
@FromAccount INT,
@ToAccount INT,
@Amount DECIMAL(18,2)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TransactionID INT;
BEGIN TRY
BEGIN TRANSACTION;
-- Check sufficient balance
DECLARE @Balance DECIMAL(18,2);
SELECT @Balance = Balance FROM Accounts WHERE AccountID = @FromAccount;
IF @Balance < @Amount
THROW 50001, 'Insufficient funds', 1;
-- Debit from source
UPDATE Accounts
SET Balance = Balance - @Amount
WHERE AccountID = @FromAccount;
-- Credit to destination
UPDATE Accounts
SET Balance = Balance + @Amount
WHERE AccountID = @ToAccount;
-- Log transaction
INSERT INTO TransactionLog (FromAccount, ToAccount, Amount, TransactionDate, Status)
VALUES (@FromAccount, @ToAccount, @Amount, GETDATE(), 'Success');
COMMIT TRANSACTION;
PRINT 'Transfer successful';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Log failed transaction
INSERT INTO TransactionLog (FromAccount, ToAccount, Amount, TransactionDate, Status)
VALUES (@FromAccount, @ToAccount, @Amount, GETDATE(), 'Failed: ' + ERROR_MESSAGE());
THROW;
END CATCH;END;
— Test the procedure
EXEC usp_TransferMoney 1, 2, 200.00;
SELECT * FROM Accounts;
SELECT * FROM TransactionLog;
MODULE 6: QUERY OPTIMIZATION & PERFORMANCE (10-12 hours)
6.1 Understanding Execution Plans
What is an Execution Plan?
A roadmap showing how SQL Server executes a query. Essential for performance tuning.
Types of Execution Plans:
Estimated Execution Plan – Plan without executing query
Actual Execution Plan – Plan from actual execution
Cached Execution Plan – Stored in plan cache
Viewing Execution Plans:
sql– Enable actual execution plan
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
— Show estimated plan (Ctrl+L in SSMS)
— Show actual plan (Ctrl+M in SSMS)
— Get graphical plan
SELECT
s.FirstName,
s.LastName,
c.CourseName
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID
WHERE s.Department = ‘Computer Science’;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
— View cached plans
SELECT
cp.objtype,
cp.cacheobjtype,
cp.usecounts,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS query_text,
qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
LEFT JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle;
Reading Execution Plans:
Key operators to understand:
sql– Table Scan (BAD – reads entire table)
SELECT * FROM Students WHERE FirstName = ‘John’;
— Index Seek (GOOD – uses index efficiently)
CREATE INDEX IX_Students_FirstName ON Students(FirstName);
SELECT * FROM Students WHERE FirstName = ‘John’;
— Index Scan (MEDIUM – scans entire index)
SELECT FirstName FROM Students;
— Nested Loop Join (good for small datasets)
— Hash Match (good for large datasets)
— Merge Join (good for sorted data)
— Key Lookup (can be expensive – index doesn’t cover query)
SELECT FirstName, LastName, Email, Phone
FROM Students
WHERE FirstName = ‘John’;
— Fix with covering index
CREATE INDEX IX_Students_FirstName_Covering
ON Students(FirstName) INCLUDE (LastName, Email, Phone);
Common Plan Issues:
sql– Missing Index
— SQL Server will suggest indexes in execution plan
— Parameter Sniffing
CREATE PROCEDURE usp_GetStudentsByGPA
@GPA DECIMAL(3,2)
AS
BEGIN
SELECT * FROM Students WHERE GPA > @GPA;
END;
— First call optimizes for 3.5
EXEC usp_GetStudentsByGPA 3.5;
— Second call uses same plan (might be inefficient for 2.0)
EXEC usp_GetStudentsByGPA 2.0;
— Fix with OPTION (RECOMPILE)
ALTER PROCEDURE usp_GetStudentsByGPA
@GPA DECIMAL(3,2)
AS
BEGIN
SELECT * FROM Students WHERE GPA > @GPA
OPTION (RECOMPILE);
END;
— Or use local variable
ALTER PROCEDURE usp_GetStudentsByGPA
@GPA DECIMAL(3,2)
AS
BEGIN
DECLARE @LocalGPA DECIMAL(3,2) = @GPA;
SELECT * FROM Students WHERE GPA > @LocalGPA;
END;
6.2 Index Optimization
Index Analysis:
sql– Find missing indexes
SELECT
OBJECT_NAME(mid.object_id) AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.avg_user_impact,
migs.user_seeks
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact * migs.user_seeks DESC;
— Find unused indexes
SELECT
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.type_desc,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, ‘IsUserTable’) = 1
AND i.type_desc <> ‘HEAP’
AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)
AND (ius.user_scans IS NULL OR ius.user_scans = 0)
AND (ius.user_lookups IS NULL OR ius.user_lookups = 0)
ORDER BY ius.user_updates DESC;
— 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, ‘DETAILED’) ips
INNER 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;
— Rebuild fragmented indexes
ALTER INDEX IX_Students_LastName ON Students REBUILD;
— Reorganize lightly fragmented indexes
ALTER INDEX IX_Students_LastName ON Students REORGANIZE;
— Update statistics
UPDATE STATISTICS Students;
Index Best Practices:
sql– 1. Create indexes on columns used in WHERE, JOIN, ORDER BY
CREATE INDEX IX_Students_Department ON Students(Department);
CREATE INDEX IX_Enrollments_StudentCourse ON Enrollments(StudentID, CourseID);
— 2. Use covering indexes for frequently accessed columns
CREATE INDEX IX_Students_Email_Covering
ON Students(Email) INCLUDE (FirstName, LastName, Phone);
— 3. Index foreign keys
CREATE INDEX IX_Enrollments_StudentID ON Enrollments(StudentID);
CREATE INDEX IX_Enrollments_CourseID ON Enrollments(CourseID);
— 4. Use filtered indexes for subsets
CREATE INDEX IX_ActiveStudents
ON Students(EnrollmentDate)
WHERE IsActive = 1;
— 5. Consider columnstore indexes for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Students_Analytics
ON Students (Department, GPA, EnrollmentDate);
6.3 Query Optimization Techniques
SELECT Optimization:
sql– Bad: SELECT *
SELECT * FROM Students;
— Good: Select only needed columns
SELECT StudentID, FirstName, LastName FROM Students;
— Bad: Functions in WHERE clause prevent index usage
SELECT * FROM Students
WHERE YEAR(EnrollmentDate) = 2023;
— Good: Rewrite to allow index usage
SELECT * FROM Students
WHERE EnrollmentDate >= ‘2023-01-01’ AND EnrollmentDate < ‘2024-01-01’;
— Bad: Leading wildcard
SELECT * FROM Students WHERE Email LIKE ‘%@gmail.com’;
— Good: Non-leading wildcard
SELECT * FROM Students WHERE Email LIKE ‘john%’;
— Bad: OR conditions (multiple index scans)
SELECT * FROM Students
WHERE Department = ‘IT’ OR Department = ‘HR’;
— Good: Use IN
SELECT * FROM Students
WHERE Department IN (‘IT’, ‘HR’);
— Bad: Implicit conversion
SELECT * FROM Students
WHERE StudentID = ‘123’; — StudentID is INT
— Good: Explicit types
SELECT * FROM Students
WHERE StudentID = 123;
JOIN Optimization:
sql– Bad: Cross join then filter
SELECT s.FirstName, c.CourseName
FROM Students s, Courses c
WHERE s.StudentID = c.CourseID; — Should use JOIN
— Good: Proper JOIN
SELECT s.FirstName, c.CourseName
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID;
— Use EXISTS instead of IN for large datasets
— Bad (for large subqueries)
SELECT * FROM Students
WHERE StudentID IN (SELECT StudentID FROM Enrollments);
— Good
SELECT * FROM Students s
WHERE EXISTS (SELECT 1 FROM Enrollments e WHERE e.StudentID = s.StudentID);
— Avoid functions on JOIN columns
— Bad
SELECT s.FirstName, e.EnrollmentDate
FROM Students s
INNER JOIN Enrollments e ON UPPER(s.Email) = UPPER(e.StudentEmail);
— Good: Use computed column or fix data
ALTER TABLE Students ADD EmailUpper AS UPPER(Email) PERSISTED;
CREATE INDEX IX_Students_EmailUpper ON Students(EmailUpper);
Subquery Optimization:
sql– Bad: Correlated subquery in SELECT
SELECT
FirstName,
(SELECT COUNT(*) FROM Enrollments e WHERE e.StudentID = s.StudentID) AS CourseCount
FROM Students s;
— Good: Use JOIN
SELECT
s.FirstName,
COUNT(e.CourseID) AS CourseCount
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
GROUP BY s.StudentID, s.FirstName;
— Bad: Multiple subqueries
SELECT
(SELECT AVG(GPA) FROM Students WHERE Department = ‘IT’) AS IT_Avg,
(SELECT AVG(GPA) FROM Students WHERE Department = ‘HR’) AS HR_Avg,
(SELECT AVG(GPA) FROM Students WHERE Department = ‘Finance’) AS Finance_Avg;
— Good: Single query with CASE
SELECT
AVG(CASE WHEN Department = ‘IT’ THEN GPA END) AS IT_Avg,
AVG(CASE WHEN Department = ‘HR’ THEN GPA END) AS HR_Avg,
AVG(CASE WHEN Department = ‘Finance’ THEN GPA END) AS Finance_Avg
FROM Students;
Temporary Tables vs Table Variables vs CTEs:
sql– Temp tables: Good for large datasets, complex operations
CREATE TABLE #TempStudents (
StudentID INT,
FullName VARCHAR(101),
GPA DECIMAL(3,2)
);
INSERT INTO #TempStudents
SELECT StudentID, FirstName + ‘ ‘ + LastName, GPA
FROM Students WHERE GPA > 3.5;
CREATE INDEX IX_Temp_StudentID ON #TempStudents(StudentID);
SELECT * FROM #TempStudents;
DROP TABLE #TempStudents;
— Table variables: Good for small datasets (< 1000 rows)
DECLARE @Students TABLE (
StudentID INT,
FullName VARCHAR(101)
);
INSERT INTO @Students
SELECT StudentID, FirstName + ‘ ‘ + LastName
FROM Students WHERE Department = ‘IT’;
SELECT * FROM @Students;
— CTEs: Good for readability, recursive queries
WITH HighPerformers AS (
SELECT StudentID, FirstName, LastName, GPA
FROM Students WHERE GPA > 3.5
)
SELECT * FROM HighPerformers;
Batch Processing:
sql– Bad: Row-by-row processing
DECLARE @StudentID INT;
DECLARE student_cursor CURSOR FOR
SELECT StudentID FROM Students;
OPEN student_cursor;
FETCH NEXT FROM student_cursor INTO @StudentID;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Students SET GPA = GPA + 0.1 WHERE StudentID = @StudentID;
FETCH NEXT FROM student_cursor INTO @StudentID;
END;
CLOSE student_cursor;
DEALLOCATE student_cursor;
— Good: Set-based operation
UPDATE Students
SET GPA = GPA + 0.1
WHERE Department = ‘Computer Science’;
6.4 Monitoring and Troubleshooting
Performance Monitoring:
sql– Long-running queries
SELECT
r.session_id,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END – r.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;
— Top CPU consuming queries
SELECT TOP 10
qs.execution_count,
qs.total_worker_time / qs.execution_count AS avg_cpu_time,
qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
— Database I/O statistics
SELECT
DB_NAME(database_id) AS DatabaseName,
file_id,
num_of_reads,
num_of_writes,
num_of_bytes_read / 1024 / 1024 AS MB_Read,
num_of_bytes_written / 1024 / 1024 AS MB_Written
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY num_of_reads DESC;
— Wait statistics
SELECT
wait_type,
wait_time_ms / 1000.0 AS wait_time_sec,
waiting_tasks_count,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE ‘%SLEEP%’
ORDER BY wait_time_ms DESC;
Query Store (SQL Server 2016+):
sql– Enable Query Store
ALTER DATABASE SchoolDB SET QUERY_STORE = ON;
— Configure Query Store
ALTER DATABASE SchoolDB SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 900,
INTERVAL_LENGTH_MINUTES = 60,
MAX_STORAGE_SIZE_MB = 1000,
QUERY_CAPTURE_MODE = AUTO
);
— View Query Store data
SELECT
q.query_id,
qt.query_sql_text,
rs.count_executions,
rs.avg_duration / 1000.0 AS avg_duration_ms,
rs.avg_cpu_time / 1000.0 AS avg_cpu_time_ms
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;
— Force a plan
EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 2;
— Disable Query Store
ALTER DATABASE SchoolDB SET QUERY_STORE = OFF;
Dynamic Management Views (DMVs):
sql– Current connections
SELECT
session_id,
login_name,
host_name,
program_name,
status,
cpu_time,
memory_usage,
last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
— Table sizes
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER 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 TotalSpaceMB DESC;
— Database file usage
SELECT
DB_NAME() AS DatabaseName,
name AS FileName,
type_desc AS FileType,
size * 8 / 1024 AS SizeMB,
FILEPROPERTY(name, ‘SpaceUsed’) * 8 / 1024 AS UsedSpaceMB,
(size – FILEPROPERTY(name, ‘SpaceUsed’)) * 8 / 1024 AS FreeSpaceMB
FROM sys.database_files;
Practical Exercise 6.1:
sql– Create a poorly performing query and optimize it
— Step 1: Create test data
CREATE TABLE LargeOrders (
OrderID INT PRIMARY KEY IDENTITY(1,1),
CustomerID INT,
OrderDate DATETIME,
TotalAmount DECIMAL(10,2),
Status VARCHAR(20)
);
— Insert 100,000 rows
INSERT INTO LargeOrders (CustomerID, OrderDate, TotalAmount, Status)
SELECT
ABS(CHECKSUM(NEWID())) % 1000 + 1,
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
ABS(CHECKSUM(NEWID())) % 10000 / 100.0,
CASE ABS(CHECKSUM(NEWID())) % 3
WHEN 0 THEN ‘Pending’
WHEN 1 THEN ‘Shipped’
ELSE ‘Delivered’
END
FROM master..spt_values v1
CROSS JOIN master..spt_values v2;
— Bad query
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT *
FROM LargeOrders
WHERE YEAR(OrderDate) = 2024
AND Status = ‘Shipped’;
— Optimize
CREATE INDEX IX_LargeOrders_Status ON LargeOrders(Status);
CREATE INDEX IX_LargeOrders_OrderDate ON LargeOrders(OrderDate);
— Better query
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM LargeOrders
WHERE OrderDate >= ‘2024-01-01’ AND OrderDate < ‘2025-01-01’
AND Status = ‘Shipped’;
— Best: Composite covering index
CREATE INDEX IX_LargeOrders_Optimized
ON LargeOrders(Status, OrderDate)
INCLUDE (OrderID, CustomerID, TotalAmount);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
MODULE 7: SECURITY & ADMINISTRATION (8-10 hours)
7.1 Authentication and Authorization
Authentication Modes:
sql– Check current authentication mode
SELECT SERVERPROPERTY(‘IsIntegratedSecurityOnly’) AS AuthenticationMode;
— 1 = Windows Authentication
— 0 = Mixed Mode (Windows + SQL Server)
— Change to Mixed Mode (requires restart)
— Do this through SQL Server Configuration Manager or:
EXEC xp_instance_regwrite
N’HKEY_LOCAL_MACHINE’,
N’Software\Microsoft\MSSQLServer\MSSQLServer’,
N’LoginMode’,
REG_DWORD,
2; — 1 = Windows, 2 = Mixed
Creating Logins:
sql– Windows Authentication login
CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS;
— SQL Server Authentication login
CREATE LOGIN JohnDoe
WITH PASSWORD = ‘StrongP@ssw0rd!’,
DEFAULT_DATABASE = SchoolDB,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON;
— View logins
SELECT
name,
type_desc,
create_date,
modify_date,
is_disabled
FROM sys.server_principals
WHERE type IN (‘S’, ‘U’, ‘G’); — S=SQL, U=Windows User, G=Windows Group
— Modify login
ALTER LOGIN JohnDoe WITH PASSWORD = ‘NewP@ssw0rd!’;
ALTER LOGIN JohnDoe DISABLE;
ALTER LOGIN JohnDoe ENABLE;
— Drop login
DROP LOGIN JohnDoe;
Creating Database Users:
sql– Create user from login
USE SchoolDB;
CREATE USER JohnDoe FOR LOGIN JohnDoe;
— Create user without login (contained database)
CREATE USER AppUser WITH PASSWORD = ‘AppP@ssw0rd!’;
— View users
SELECT
name,
type_desc,
create_date,
authentication_type_desc
FROM sys.database_principals
WHERE type IN (‘S’, ‘U’);
— Modify user
ALTER USER JohnDoe WITH DEFAULT_SCHEMA = dbo;
— Drop user
DROP USER JohnDoe;
7.2 Permissions and Roles
Server-Level Roles:
sql– Fixed server roles
— sysadmin – Full control
— serveradmin – Configure server settings
— securityadmin – Manage logins
— processadmin – Manage processes
— setupadmin – Add/remove linked servers
— bulkadmin – BULK INSERT permissions
— diskadmin – Manage disk files
— dbcreator – Create, alter, drop databases
— public – All logins
— Add login to server role
ALTER SERVER ROLE sysadmin ADD MEMBER JohnDoe;
— Remove from role
ALTER SERVER ROLE sysadmin DROP MEMBER JohnDoe;
— View server role members
SELECT
r.name AS RoleName,
m.name AS MemberName
FROM sys.server_role_members rm
INNER JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name;
Database-Level Roles:
sqlUSE SchoolDB;
— Fixed database roles
— db_owner – Full control
— db_securityadmin – Manage roles and permissions
— db_accessadmin – Manage user access
— db_ddladmin – DDL commands
— db_datawriter – INSERT, UPDATE, DELETE
— db_datareader – SELECT
— db_denydatawriter – Deny writes
— db_denydatareader – Deny reads
— Add user to database role
ALTER ROLE db_datareader ADD MEMBER JohnDoe;
ALTER ROLE db_datawriter ADD MEMBER JohnDoe;
— Create custom database role
CREATE ROLE StudentManagers;
— Grant permissions to role
GRANT SELECT, INSERT, UPDATE ON Students TO StudentManagers;
GRANT SELECT ON Courses TO StudentManagers;
GRANT EXECUTE ON usp_GetStudentsByDepartment TO StudentManagers;
— Add user to custom role
ALTER ROLE StudentManagers ADD MEMBER JohnDoe;
— View role members
SELECT
r.name AS RoleName,
m.name AS MemberName
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name;
Object-Level Permissions:
-- GRANT permissions
GRANT SELECT ON Students TO JohnDoe;
GRANT INSERT, UPDATE ON Students TO JohnDoe;
GRANT DELETE ON Students TO JohnDoe;
GRANT EXECUTE ON usp_GetAllStudents TO JohnDoe;
-- GRANT with column-level security
GRANT SELECT ON Students(FirstName, LastName, Email) TO JohnDoe;
-- GRANT with GRANT OPTION (can grant to others)
GRANT SELECT ON Students TO JohnDoe WITH GRANT OPTION;
-- DENY permissions (overrides GRANT)
DENY DELETE ON Students TO JohnDoe;
DENY SELECT ON Students(SSN) TO JohnDoe; -- Hide sensitive column
-- REVOKE permissions
REVOKE SELECT ON Students FROM JohnDoe;
REVOKE GRANT OPTION FOR SELECT ON Students FROM JohnDoe;
-- View permissions
SELECT
pr.name AS UserName,
pr.type_desc,
pe.permission_name,
pe.state_desc,
OBJECT_NAME(pe.major_id) AS ObjectName
FROM sys.database_permissions pe
INNER JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'JohnDoe'
ORDER BY pe.permission_name, pe.state_desc;Schema-Level Security:
-- Create schema
CREATE SCHEMA Sales AUTHORIZATION dbo;
CREATE SCHEMA HR AUTHORIZATION dbo;
-- Create tables in schemas
CREATE TABLE Sales.Orders (
OrderID INT PRIMARY KEY,
OrderDate DATETIME
);
CREATE TABLE HR.Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);
-- Grant schema permissions
GRANT SELECT ON SCHEMA::Sales TO JohnDoe;
DENY SELECT ON SCHEMA::HR TO JohnDoe;
-- Set default schema for user
ALTER USER JohnDoe WITH DEFAULT_SCHEMA = Sales;7.3 Row-Level Security
-- Enable Row-Level Security
CREATE SCHEMA Security;
-- Create security predicate function
CREATE FUNCTION Security.fn_SecurityPredicate(@Department VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @Department = USER_NAME() OR IS_MEMBER('db_owner') = 1;
-- Create security policy
CREATE SECURITY POLICY Security.DepartmentSecurityPolicy
ADD FILTER PREDICATE Security.fn_SecurityPredicate(Department)
ON dbo.Students
WITH (STATE = ON);
-- Test: Users can only see their department
EXECUTE AS USER = 'ITUser';
SELECT * FROM Students; -- Only sees IT department
REVERT;
-- Disable policy
ALTER SECURITY POLICY Security.DepartmentSecurityPolicy WITH (STATE = OFF);
-- Drop policy
DROP SECURITY POLICY Security.DepartmentSecurityPolicy;
DROP FUNCTION Security.fn_SecurityPredicate;7.4 Dynamic Data Masking
-- Add masking to existing column
ALTER TABLE Students
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE Students
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(1,"XXX-XXX-",4)');
ALTER TABLE Students
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'default()');
-- Create table with masking
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
CreditCard VARCHAR(20) MASKED WITH (FUNCTION = 'partial(4,"XXXX-XXXX-XXXX-",4)'),
Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'default()')
);
-- Grant permission to see unmasked data
GRANT UNMASK TO JohnDoe;
-- Revoke permission
REVOKE UNMASK TO JohnDoe;
-- Remove masking
ALTER TABLE Students
ALTER COLUMN Email DROP MASKED;7.5 Encryption
Transparent Data Encryption (TDE):
-- 1. Create master key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyP@ssw0rd!';
-- 2. Create certificate
CREATE CERTIFICATE TDE_Cert
WITH SUBJECT = 'TDE Certificate';
-- 3. Create database encryption key
USE SchoolDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
-- 4. Enable TDE
ALTER DATABASE SchoolDB
SET ENCRYPTION ON;
-- Check encryption status
SELECT
DB_NAME(database_id) AS DatabaseName,
encryption_state,
encryption_state_desc,
percent_complete
FROM sys.dm_database_encryption_keys;
-- Backup certificate (IMPORTANT!)
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\Backup\TDE_Cert.cer'
WITH PRIVATE KEY (
FILE = 'C:\Backup\TDE_Cert_Key.pvk',
ENCRYPTION BY PASSWORD = 'CertBackupP@ssw0rd!'
);Always Encrypted:
-- Create column master key (done via SSMS wizard or PowerShell)
-- Create column encryption key
-- Encrypt existing column
ALTER TABLE Students
ALTER COLUMN SSN VARCHAR(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = CEK_Auto1,
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);
-- Insert encrypted data (application handles encryption)
INSERT INTO Students (FirstName, LastName, SSN)
VALUES ('John', 'Doe', '123-45-6789'); -- Encrypted by client driverColumn-Level Encryption:
-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterP@ssw0rd!';
-- Create certificate
CREATE CERTIFICATE SalaryCert
WITH SUBJECT = 'Salary Certificate';
-- Create symmetric key
CREATE SYMMETRIC KEY SalaryKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE SalaryCert;
-- Encrypt data
OPEN SYMMETRIC KEY SalaryKey
DECRYPTION BY CERTIFICATE SalaryCert;
UPDATE Employees
SET EncryptedSalary = ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CONVERT(VARCHAR, Salary));
CLOSE SYMMETRIC KEY SalaryKey;
-- Decrypt data
OPEN SYMMETRIC KEY SalaryKey
DECRYPTION BY CERTIFICATE SalaryCert;
SELECT
EmployeeID,
EmployeeName,
CONVERT(DECIMAL(10,2), DECRYPTBYKEY(EncryptedSalary)) AS Salary
FROM Employees;
CLOSE SYMMETRIC KEY SalaryKey;7.6 Auditing
Server Audit:
-- Create server audit
USE master;
CREATE SERVER AUDIT ServerAudit_SchoolDB
TO FILE (
FILEPATH = 'C:\SQLAudit\',
MAXSIZE = 100 MB,
MAX_ROLLOVER_FILES = 10
)
WITH (QUEUE_DELAY = 1000);
-- Enable audit
ALTER SERVER AUDIT ServerAudit_SchoolDB
WITH (STATE = ON);
-- Create server audit specification
CREATE SERVER AUDIT SPECIFICATION ServerAuditSpec_Logins
FOR SERVER AUDIT ServerAudit_SchoolDB
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP);
-- Enable specification
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpec_Logins
WITH (STATE = ON);
-- Create database audit specification
USE SchoolDB;
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec_Students
FOR SERVER AUDIT ServerAudit_SchoolDB
ADD (SELECT, INSERT, UPDATE, DELETE ON Students BY public);
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec_Students
WITH (STATE = ON);
-- View audit logs
SELECT
event_time,
action_id,
succeeded,
session_server_principal_name,
database_name,
statement
FROM sys.fn_get_audit_file('C:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;
-- Disable and drop audit
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec_Students WITH (STATE = OFF);
DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpec_Students;
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpec_Logins WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION ServerAuditSpec_Logins;
ALTER SERVER AUDIT ServerAudit_SchoolDB WITH (STATE = OFF);
DROP SERVER AUDIT ServerAudit_SchoolDB;Practical Exercise 7.1:
-- Create security scenario
-- 1. Create logins
CREATE LOGIN SalesManager WITH PASSWORD = 'Sales123!';
CREATE LOGIN SalesRep WITH PASSWORD = 'SalesRep123!';
CREATE LOGIN HRManager WITH PASSWORD = 'HR123!';
-- 2. Create database users
USE SchoolDB;
CREATE USER SalesManager FOR LOGIN SalesManager;
CREATE USER SalesRep FOR LOGIN SalesRep;
CREATE USER HRManager FOR LOGIN HRManager;
-- 3. Create custom roles
CREATE ROLE SalesRole;
CREATE ROLE HRRole;
-- 4. Grant permissions
GRANT SELECT, INSERT, UPDATE ON Students TO SalesRole;
GRANT SELECT ON Courses TO SalesRole;
GRANT SELECT, INSERT, UPDATE, DELETE ON Students TO HRRole;
DENY SELECT ON Students(SSN) TO SalesRole; -- Sales can't see SSN
-- 5. Assign users to roles
ALTER ROLE SalesRole ADD MEMBER SalesManager;
ALTER ROLE SalesRole ADD MEMBER SalesRep;
ALTER ROLE HRRole ADD MEMBER HRManager;
-- 6. Test permissions
EXECUTE AS USER = 'SalesRep';
SELECT * FROM Students; -- Should work
SELECT SSN FROM Students; -- Should fail or show NULL
REVERT;MODULE 8: BACKUP & RECOVERY (6-8 hours)
8.1 Backup Types
Understanding Backup Types:
- Full Backup – Complete copy of database
- Differential Backup – Changes since last full backup
- Transaction Log Backup – Transaction log records
Recovery Models:
-- View current recovery model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'SchoolDB';
-- Change recovery model
ALTER DATABASE SchoolDB SET RECOVERY FULL; -- Full recovery
ALTER DATABASE SchoolDB SET RECOVERY SIMPLE; -- Simple recovery
ALTER DATABASE SchoolDB SET RECOVERY BULK_LOGGED; -- Bulk-logged
/*
SIMPLE: No log backups, can only restore to full/differential backup
FULL: Supports point-in-time recovery, requires log backups
BULK_LOGGED: Minimally logged bulk operations
*/8.2 Creating Backups
Full Backup:
-- Basic full backup
BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backup\SchoolDB_Full.bak';
-- Full backup with options
BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH
INIT, -- Overwrite existing file
NAME = 'SchoolDB Full Backup',
DESCRIPTION = 'Full backup of SchoolDB',
COMPRESSION, -- Compress backup
STATS = 10; -- Show progress every 10%
-- Full backup to multiple files (striped)
BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backup\SchoolDB_Full_1.bak',
DISK = 'C:\Backup\SchoolDB_Full_2.bak',
DISK = 'C:\Backup\SchoolDB_Full_3.bak'
WITH FORMAT, COMPRESSION;
-- Verify backup
RESTORE VERIFYONLY
FROM DISK = 'C:\Backup\SchoolDB_Full.bak';Differential Backup:
-- Differential backup (requires previous full backup)
BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backup\SchoolDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;Transaction Log Backup:
-- Set to FULL recovery model first
ALTER DATABASE SchoolDB SET RECOVERY FULL;
-- Transaction log backup
BACKUP LOG SchoolDB
TO DISK = 'C:\Backup\SchoolDB_Log.trn'
WITH COMPRESSION;
-- Log backup with truncation
BACKUP LOG SchoolDB
TO DISK = 'C:\Backup\SchoolDB_Log.trn'
WITH NO_TRUNCATE; -- If database is inaccessible
-- Truncate log without backup (emergency only)
BACKUP LOG SchoolDB WITH TRUNCATE_ONLY; -- Deprecated
-- Use this instead:
ALTER DATABASE SchoolDB SET RECOVERY SIMPLE;
DBCC SHRINKFILE (SchoolDB_log, 1);
ALTER DATABASE SchoolDB SET RECOVERY FULL;File and Filegroup Backup:
-- Create filegroups
ALTER DATABASE SchoolDB ADD FILEGROUP FG_Data;
ALTER DATABASE SchoolDB ADD FILEGROUP FG_Index;
ALTER DATABASE SchoolDB
ADD FILE (
NAME = SchoolDB_Data,
FILENAME = 'C:\SQLData\SchoolDB_Data.ndf',
SIZE = 100MB
) TO FILEGROUP FG_Data;
-- Backup specific filegroup
BACKUP DATABASE SchoolDB
FILEGROUP = 'FG_Data'
TO DISK = 'C:\Backup\SchoolDB_FG_Data.bak';
-- Backup specific file
BACKUP DATABASE SchoolDB
FILE = 'SchoolDB_Data'
TO DISK = 'C:\Backup\SchoolDB_File.bak';Copy-Only Backup:
-- Copy-only backup (doesn't affect backup chain)
BACKUP DATABASE SchoolDB
TO DISK = 'C:\Backup\SchoolDB_CopyOnly.bak'
WITH COPY_ONLY, COMPRESSION;8.3 Restore Operations
Full Restore:
-- Restore with REPLACE (overwrites existing database)
RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH REPLACE, RECOVERY;
-- Restore to different location
RESTORE DATABASE SchoolDB_Test
FROM DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH
MOVE 'SchoolDB' TO 'C:\SQLData\SchoolDB_Test.mdf',
MOVE 'SchoolDB_log' TO 'C:\SQLData\SchoolDB_Test_log.ldf',
REPLACE, RECOVERY;
-- View backup contents
RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\SchoolDB_Full.bak';
-- View backup header
RESTORE HEADERONLY
FROM DISK = 'C:\Backup\SchoolDB_Full.bak';Differential Restore:
-- Restore full backup first (with NORECOVERY)
RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH NORECOVERY, REPLACE;
-- Restore differential backup
RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Diff.bak'
WITH RECOVERY;Point-in-Time Restore:
-- 1. Restore full backup
RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH NORECOVERY, REPLACE;
-- 2. Restore differential (optional)
RESTORE DATABASE SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Diff.bak'
WITH NORECOVERY;
-- 3. Restore log backups
RESTORE LOG SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Log_1.trn'
WITH NORECOVERY;
RESTORE LOG SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Log_2.trn'
WITH NORECOVERY;
-- 4. Restore to specific point in time
RESTORE LOG SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Log_3.trn'
WITH RECOVERY,
STOPAT = '2024-01-15 14:30:00';
-- Or stop at specific mark
RESTORE LOG SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Log_3.trn'
WITH RECOVERY,
STOPATMARK = 'BeforeDataDelete';Page Restore:
-- Identify damaged pages
SELECT
database_id,
file_id,
page_id,
event_type,
error_count
FROM msdb.dbo.suspect_pages;
-- Restore specific page
RESTORE DATABASE SchoolDB
PAGE = '1:157' -- File 1, Page 157
FROM DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH NORECOVERY;
RESTORE LOG SchoolDB
FROM DISK = 'C:\Backup\SchoolDB_Log.trn'
WITH RECOVERY;Partial Restore:
-- Restore primary filegroup only
RESTORE DATABASE SchoolDB
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Backup\SchoolDB_Full.bak'
WITH PARTIAL, NORECOVERY;
RESTORE DATABASE SchoolDB
FILEGROUP = 'FG_Data'
FROM DISK = 'C:\Backup\SchoolDB_FG_Data.bak'
WITH RECOVERY;8.4 Backup Strategy
Recommended Backup Strategy:
-- Full backup schedule (Weekly - Sunday)
-- Differential backup (Daily except Sunday)
-- Transaction log backup (Every hour)
-- Example backup script
DECLARE @BackupPath VARCHAR(500);
DECLARE @FileName VARCHAR(500);
DECLARE @Date VARCHAR(20);
SET @Date = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' +
REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '');
SET @BackupPath = 'C:\Backup\';
-- Full backup on Sunday
IF DATEPART(WEEKDAY, GETDATE()) = 1
BEGIN
SET @FileName = @BackupPath + 'SchoolDB_Full_' + @Date + '.bak';
BACKUP DATABASE SchoolDB
TO DISK = @FileName
WITH INIT, COMPRESSION;
PRINT 'Full backup completed: ' + @FileName;
END
-- Differential on other days
ELSE
BEGIN
SET @FileName = @BackupPath + 'SchoolDB_Diff_' + @Date + '.bak';
BACKUP DATABASE SchoolDB
TO DISK = @FileName
WITH DIFFERENTIAL, INIT, COMPRESSION;
PRINT 'Differential backup completed: ' + @FileName;
END
-- Transaction log backup (run hourly)
SET @FileName = @BackupPath + 'SchoolDB_Log_' + @Date + '.trn';
BACKUP LOG SchoolDB
TO DISK = @FileName
WITH INIT, COMPRESSION;
PRINT 'Log backup completed: ' + @FileName;Maintenance Plans:
-- Create maintenance plan via SSMS:
-- 1. Object Explorer > Management > Maintenance Plans
-- 2. Right-click > New Maintenance Plan
-- 3. Add tasks:
-- - Back Up Database (Full)
-- - Back Up Database (Differential)
-- - Back Up Database (Transaction Log)
-- - Cleanup Old Backups
-- 4. Configure schedules
-- 5. Save and enable
-- View maintenance plan history
SELECT
j.name AS JobName,
h.run_date,
h.run_time,
h.run_status,
h.message
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.name LIKE 'SchoolDB%'
ORDER BY h.run_date DESC, h.run_time DESC;8.5 Disaster Recovery
Database Snapshots (For Testing):
-- Create database snapshot
CREATE DATABASE SchoolDB_Snapshot_20240115
ON (
NAME = SchoolDB,
FILENAME = 'C:\SQLData\SchoolDB_Snapshot_20240115.ss'
)
AS SNAPSHOT OF SchoolDB;
-- Use snapshot for reporting (read-only)
SELECT * FROM SchoolDB_Snapshot_20240115.dbo.Students;
-- Revert database to snapshot
RESTORE DATABASE SchoolDB
FROM DATABASE_SNAPSHOT = 'SchoolDB_Snapshot_20240115';
-- Drop snapshot
DROP DATABASE SchoolDB_Snapshot_20240115;High Availability Options Overview:
-- 1. Always On Availability Groups (Enterprise Edition)
-- - Multiple readable secondaries
-- - Automatic failover
-- - Best for mission-critical applications
-- 2. Log Shipping
-- - Automatic backup, copy, restore of transaction logs
-- - Manual failover
-- - Good for disaster recovery
-- 3. Database Mirroring (Deprecated, use Always On)
-- - Real-time copy to mirror server
-- - Automatic or manual failover
-- 4. Replication
-- - Distribute data to multiple servers
-- - Various types: Snapshot, Transactional, MergeLog Shipping Configuration (Basic):
-- On Primary Server
-- 1. Set to FULL recovery
ALTER DATABASE SchoolDB SET RECOVERY FULL;
-- 2. Create full backup
BACKUP DATABASE SchoolDB
TO DISK = '\\SharedLocation\SchoolDB_LogShip_Init.bak'
WITH INIT;
-- 3. Configure log shipping (via SSMS wizard or T-SQL)
EXEC sp_add_log_shipping_primary_database
@database = 'SchoolDB',
@backup_directory = '\\SharedLocation\LogShip\',
@backup_share = '\\SharedLocation\LogShip\',
@backup_retention_period = 4320; -- 3 days
-- On Secondary Server
-- Restore initial backup with NORECOVERY
RESTORE DATABASE SchoolDB
FROM DISK = '\\SharedLocation\SchoolDB_LogShip_Init.bak'
WITH NORECOVERY;
-- Configure secondary (via wizard)Practical Exercise 8.1:
-- Complete backup and restore scenario
-- 1. Create test database
CREATE DATABASE BackupTest;
USE BackupTest;
CREATE TABLE TestData (
ID INT IDENTITY(1,1) PRIMARY KEY,
DataValue VARCHAR(100),
CreatedDate DATETIME DEFAULT GETDATE()
);
INSERT INTO TestData (DataValue)
VALUES ('Initial Data 1'), ('Initial Data 2');
-- 2. Full backup
BACKUP DATABASE BackupTest
TO DISK = 'C:\Backup\BackupTest_Full.bak'
WITH INIT, COMPRESSION;
-- 3. Insert more data
INSERT INTO TestData (DataValue)
VALUES ('After Full Backup');
-- 4. Differential backup
BACKUP DATABASE BackupTest
TO DISK = 'C:\Backup\BackupTest_Diff.bak'
WITH DIFFERENTIAL, INIT, COMPRESSION;
-- 5. Insert more data
INSERT INTO TestData (DataValue)
VALUES ('After Differential');
-- 6. Log backup
ALTER DATABASE BackupTest SET RECOVERY FULL;
BACKUP LOG BackupTest
TO DISK = 'C:\Backup\BackupTest_Log1.trn'
WITH INIT, COMPRESSION;
-- 7. Mark specific point
BEGIN TRANSACTION DeleteData
WITH MARK 'BeforeDelete';
DELETE FROM TestData WHERE ID = 1;
COMMIT TRANSACTION;
-- 8. Log backup after delete
BACKUP LOG BackupTest
TO DISK = 'C:\Backup\BackupTest_Log2.trn'
WITH INIT, COMPRESSION;
-- 9. Restore to point before delete
USE master;
RESTORE DATABASE BackupTest
FROM DISK = 'C:\Backup\BackupTest_Full.bak'
WITH NORECOVERY, REPLACE;
RESTORE DATABASE BackupTest
FROM DISK = 'C:\Backup\BackupTest_Diff.bak'
WITH NORECOVERY;
RESTORE LOG BackupTest
FROM DISK = 'C:\Backup\BackupTest_Log1.trn'
WITH NORECOVERY;
RESTORE LOG BackupTest
FROM DISK = 'C:\Backup\BackupTest_Log2.trn'
WITH RECOVERY, STOPATMARK = 'BeforeDelete';
-- Verify data restored correctly
USE BackupTest;
SELECT * FROM TestData;MODULE 9: ADVANCED TOPICS (8-10 hours)
9.1 Dynamic SQL
What is Dynamic SQL?
SQL code constructed and executed at runtime.
Basic Dynamic SQL:
-- Simple example
DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(128) = 'Students';
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC(@SQL);
-- With sp_executesql (preferred - supports parameters)
DECLARE @SQL NVARCHAR(MAX);
DECLARE @MinGPA DECIMAL(3,2) = 3.5;
SET @SQL = N'SELECT * FROM Students WHERE GPA > @MinGPAParam';
EXEC sp_executesql
@SQL,
N'@MinGPAParam DECIMAL(3,2)',
@MinGPAParam = @MinGPA;Dynamic SQL with Output Parameters:
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Count INT;
SET @SQL = N'SELECT @CountOut = COUNT(*) FROM Students WHERE Department = @Dept';
EXEC sp_executesql
@SQL,
N'@Dept VARCHAR(50), @CountOut INT OUTPUT',
@Dept = 'Computer Science',
@CountOut = @Count OUTPUT;
PRINT 'Count: ' + CAST(@Count AS VARCHAR);Dynamic Pivot:
CREATE PROCEDURE usp_DynamicPivot
@TableName NVARCHAR(128),
@PivotColumn NVARCHAR(128),
@AggColumn NVARCHAR(128)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Columns NVARCHAR(MAX);
-- Get distinct values for pivot
SET @SQL = N'SELECT @ColsOut = STRING_AGG(QUOTENAME(' + QUOTENAME(@PivotColumn) + '), '','')
FROM (SELECT DISTINCT ' + QUOTENAME(@PivotColumn) + ' FROM ' + QUOTENAME(@TableName) + ') AS DistinctVals';
EXEC sp_executesql @SQL, N'@ColsOut NVARCHAR(MAX) OUTPUT', @ColsOut = @Columns OUTPUT;
-- Build pivot query
SET @SQL = N'
SELECT *
FROM (
SELECT ' + QUOTENAME(@PivotColumn) + ', ' + QUOTENAME(@AggColumn) + '
FROM ' + QUOTENAME(@TableName) + '
) AS SourceTable
PIVOT (
COUNT(' + QUOTENAME(@AggColumn) + ')
FOR ' + QUOTENAME(@PivotColumn) + ' IN (' + @Columns + ')
) AS PivotTable';
EXEC(@SQL);
END;
-- Execute
EXEC usp_DynamicPivot 'Students', 'Department', 'StudentID';Security Considerations:
-- BAD: SQL Injection vulnerability
DECLARE @UserInput VARCHAR(100) = '''; DROP TABLE Students; --';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Students WHERE LastName = ''' + @UserInput + '''';
EXEC(@SQL); -- DANGEROUS!
-- GOOD: Parameterized with sp_executesql
DECLARE @UserInput VARCHAR(100) = 'Smith';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Students WHERE LastName = @LastName';
EXEC sp_executesql @SQL, N'@LastName VARCHAR(100)', @LastName = @UserInput; -- SAFE
-- GOOD: Use QUOTENAME for identifiers
DECLARE @TableName NVARCHAR(128) = 'Students';
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM ' + QUOTENAME(@TableName); -- SAFE9.2 XML and JSON
Working with XML:
-- Convert query results to XML
SELECT
StudentID,
FirstName,
LastName,
Email
FROM Students
FOR XML RAW('Student'), ROOT('Students'), ELEMENTS;
-- FOR XML PATH
SELECT
StudentID AS '@ID',
FirstName AS 'Name/First',
LastName AS 'Name/Last',
Email
FROM Students
FOR XML PATH('Student'), ROOT('Students');
-- Query XML data
DECLARE @XML XML = '<Students>
<Student ID="1">
<Name>John Doe</Name>
<Email>john@email.com</Email>
</Student>
<Student ID="2">
<Name>Jane Smith</Name>
<Email>jane@email.com</Email>
</Student>
</Students>';
-- XQuery methods
SELECT @XML.value('(/Students/Student/@ID)[1]', 'INT') AS FirstStudentID;
SELECT @XML.query('/Students/Student[@ID=1]') AS FirstStudent;
SELECT @XML.exist('/Students/Student[@ID=1]') AS StudentExists;
-- Shred XML into table
SELECT
Student.value('@ID', 'INT') AS StudentID,
Student.value('(Name)[1]', 'VARCHAR(100)') AS Name,
Student.value('(Email)[1]', 'VARCHAR(100)') AS Email
FROM @XML.nodes('/Students/Student') AS T(Student);
-- Store XML in table
CREATE TABLE XMLData (
ID INT PRIMARY KEY IDENTITY,
XMLContent XML
);
INSERT INTO XMLData (XMLContent) VALUES (@XML);
-- XML indexes
CREATE PRIMARY XML INDEX PXML_XMLData_XMLContent
ON XMLData(XMLContent);Working with JSON (SQL Server 2016+):
-- Convert query results to JSON
SELECT
StudentID,
FirstName,
LastName,
Email
FROM Students
FOR JSON PATH, ROOT('Students');
-- FOR JSON AUTO (uses table structure)
SELECT
s.StudentID,
s.FirstName,
s.LastName,
c.CourseName
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID
FOR JSON AUTO;
-- Parse JSON
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"StudentID": 1,
"FirstName": "John",
"LastName": "Doe",
"Email": "john@email.com"
},
{
"StudentID": 2,
"FirstName": "Jane",
"LastName": "Smith",
"Email": "jane@email.com"
}
]';
-- Check if valid JSON
SELECT ISJSON(@JSON) AS IsValidJSON;
-- Extract values
SELECT JSON_VALUE(@JSON, '$[0].FirstName') AS FirstName;
SELECT JSON_VALUE(@JSON, '$[1].Email') AS Email;
-- Query JSON array
SELECT *
FROM OPENJSON(@JSON)
WITH (
StudentID INT '$.StudentID',
FirstName VARCHAR(50) '$.FirstName',
LastName VARCHAR(50) '$.LastName',
Email VARCHAR(100) '$.Email'
);
-- Modify JSON
SET @JSON = JSON_MODIFY(@JSON, '$[0].FirstName', 'Jonathan');
SELECT @JSON;
-- Store JSON in table
CREATE TABLE JSONData (
ID INT PRIMARY KEY IDENTITY,
JSONContent NVARCHAR(MAX) CHECK (ISJSON(JSONContent) = 1)
);
INSERT INTO JSONData (JSONContent) VALUES (@JSON);
-- Query JSON column
SELECT
ID,
JSON_VALUE(JSONContent, '$[0].FirstName') AS FirstStudentName
FROM JSONData;
### **9.3 Temporal Tables (System-Versioned Tables)**sql
— Create temporal table
CREATE TABLE Students_Temporal (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100),
GPA DECIMAL(3,2),
-- Required columns for temporal
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Students_History));
— Insert data
INSERT INTO Students_Temporal (StudentID, FirstName, LastName, Email, GPA)
VALUES (1, ‘John’, ‘Doe’, ‘john@email.com’, 3.5);
— Update data
UPDATE Students_Temporal
SET GPA = 3.8
WHERE StudentID = 1;
— Query current data
SELECT * FROM Students_Temporal;
— Query historical data
SELECT * FROM Students_Temporal
FOR SYSTEM_TIME AS OF ‘2024-01-15 10:00:00’;
— Query all versions
SELECT * FROM Students_Temporal
FOR SYSTEM_TIME ALL
WHERE StudentID = 1;
— Query between dates
SELECT * FROM Students_Temporal
FOR SYSTEM_TIME BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;
— Turn off versioning
ALTER TABLE Students_Temporal SET (SYSTEM_VERSIONING = OFF);
— Drop tables
DROP TABLE Students_Temporal;
DROP TABLE Students_History;
### **9.4 Graph Tables (SQL Server 2017+)**sql
— Create node tables
CREATE TABLE Person (
PersonID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT
) AS NODE;
CREATE TABLE City (
CityID INT PRIMARY KEY,
CityName VARCHAR(100)
) AS NODE;
— Create edge table
CREATE TABLE LivesIn AS EDGE;
— Insert nodes
INSERT INTO Person VALUES (1, ‘John’, 30);
INSERT INTO Person VALUES (2, ‘Jane’, 25);
INSERT INTO City VALUES (1, ‘New York’);
INSERT INTO City VALUES (2, ‘Los Angeles’);
— Insert edges (relationships)
INSERT INTO LivesIn VALUES (
(SELECT $node_id FROM Person WHERE PersonID = 1),
(SELECT $node_id FROM City WHERE CityID = 1)
);
INSERT INTO LivesIn VALUES (
(SELECT $node_id FROM Person WHERE PersonID = 2),
(SELECT $node_id FROM City WHERE CityID = 2)
);
— Query graph data
SELECT
p.Name,
c.CityName
FROM Person p, LivesIn, City c
WHERE MATCH(p-(LivesIn)->c);
— Find all people living in New York
SELECT p.Name
FROM Person p, LivesIn, City c
WHERE MATCH(p-(LivesIn)->c) AND c.CityName = ‘New York’;
### **9.5 PolyBase (External Data Access)**sql
— Enable PolyBase (requires installation)
EXEC sp_configure ‘polybase enabled’, 1;
RECONFIGURE;
— Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘PolyBaseP@ssw0rd!’;
— Create database scoped credential
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’,
SECRET = ‘your_sas_token_here’;
— Create external data source
CREATE EXTERNAL DATA SOURCE AzureBlobStorage
WITH (
TYPE = HADOOP,
LOCATION = ‘wasbs://container@account.blob.core.windows.net’,
CREDENTIAL = AzureStorageCredential
);
— Create external file format
CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ‘,’,
STRING_DELIMITER = ‘”‘,
FIRST_ROW = 2
)
);
— Create external table
CREATE EXTERNAL TABLE ExternalStudents (
StudentID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
)
WITH (
LOCATION = ‘/data/students.csv’,
DATA_SOURCE = AzureBlobStorage,
FILE_FORMAT = TextFileFormat
);
— Query external data
SELECT * FROM ExternalStudents;
— Join external and internal data
SELECT
e.FirstName,
e.LastName,
c.CourseName
FROM ExternalStudents e
INNER JOIN Courses c ON e.StudentID = c.InstructorID;
**Practical Exercise 9.1:**sql
— Create dynamic search procedure
CREATE PROCEDURE usp_DynamicStudentSearch
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@Department VARCHAR(50) = NULL,
@MinGPA DECIMAL(3,2) = NULL,
@MaxGPA DECIMAL(3,2) = NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Params NVARCHAR(MAX);
DECLARE @Where NVARCHAR(MAX) = ”;
-- Build WHERE clause dynamically
IF @FirstName IS NOT NULL
SET @Where = @Where + ' AND FirstName LIKE @FirstNameParam';
IF @LastName IS NOT NULL
SET @Where = @Where + ' AND LastName LIKE @LastNameParam';
IF @Department IS NOT NULL
SET @Where = @Where + ' AND Department = @DepartmentParam';
IF @MinGPA IS NOT NULL
SET @Where = @Where + ' AND GPA >= @MinGPAParam';
IF @MaxGPA IS NOT NULL
SET @Where = @Where + ' AND GPA <= @MaxGPAParam';
-- Remove leading AND
IF LEN(@Where) > 0
SET @Where = SUBSTRING(@Where, 6, LEN(@Where));
ELSE
SET @Where = '1=1';
-- Build SQL
SET @SQL = N'
SELECT
StudentID,
FirstName,
LastName,
Department,
GPA,
Email
FROM Students
WHERE ' + @Where + '
ORDER BY LastName, FirstName';
-- Define parameters
SET @Params = N'
@FirstNameParam VARCHAR(50),
@LastNameParam VARCHAR(50),
@DepartmentParam VARCHAR(50),
@MinGPAParam DECIMAL(3,2),
@MaxGPAParam DECIMAL(3,2)';
-- Execute
EXEC sp_executesql
@SQL,
@Params,
@FirstNameParam = @FirstName,
@LastNameParam = @LastName,
@DepartmentParam = @Department,
@MinGPAParam = @MinGPA,
@MaxGPAParam = @MaxGPA;END;
— Test
EXEC usp_DynamicStudentSearch @Department = ‘Computer Science’, @MinGPA = 3.5;
---
## **MODULE 10: BEST PRACTICES & REAL-WORLD SCENARIOS (6-8 hours)**
### **10.1 Coding Standards**
**Naming Conventions:**sql
— Tables: Pascal Case, plural
CREATE TABLE Students (…);
CREATE TABLE CourseEnrollments (…);
— Columns: Pascal Case
FirstName, LastName, CreatedDate
— Stored Procedures: usp_ prefix
CREATE PROCEDURE usp_GetStudentsByDepartment …
— Functions: fn_ prefix
CREATE FUNCTION dbo.fn_CalculateAge …
— Views: vw_ prefix
CREATE VIEW vw_ActiveStudents …
— Triggers: trg_ prefix, include table name and action
CREATE TRIGGER trg_Students_AfterInsert …
— Indexes: IX_ prefix, include table and columns
CREATE INDEX IX_Students_LastName_FirstName …
— Primary Keys: PK_ prefix
CONSTRAINT PK_Students PRIMARY KEY
— Foreign Keys: FK_ prefix, from_to format
CONSTRAINT FK_Enrollments_Students FOREIGN KEY
**Formatting:**sql
— Good formatting
SELECT
s.StudentID,
s.FirstName,
s.LastName,
d.DepartmentName,
COUNT(e.CourseID) AS CourseCount
FROM Students s
INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE s.IsActive = 1
GROUP BY
s.StudentID,
s.FirstName,
s.LastName,
d.DepartmentName
HAVING COUNT(e.CourseID) > 0
ORDER BY s.LastName, s.FirstName;
— Use comments
/*
Purpose: Get active students with their course counts
Author: John Doe
Date: 2024-01-15
Modified: 2024-01-20 – Added department filter
*/
**Error Handling Pattern:**sql
CREATE PROCEDURE usp_StandardErrorHandling
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Your code here
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Log error
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
-- Insert into error log table
INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorDate)
VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, GETDATE());
-- Re-throw error
THROW;
END CATCH;END;
### **10.2 Performance Best Practices**sql
— 1. Use EXISTS instead of COUNT
— Bad
IF (SELECT COUNT(*) FROM Students WHERE Email = @Email) > 0
BEGIN
— Email exists
END
— Good
IF EXISTS (SELECT 1 FROM Students WHERE Email = @Email)
BEGIN
— Email exists
END
— 2. Use appropriate data types
— Bad
CREATE TABLE BadTable (
ID VARCHAR(50), — Should be INT
Amount VARCHAR(20), — Should be DECIMAL
IsActive VARCHAR(5) — Should be BIT
);
— Good
CREATE TABLE GoodTable (
ID INT,
Amount DECIMAL(10,2),
IsActive BIT
);
— 3. Avoid SELECT * in production
— Bad
INSERT INTO TargetTable
SELECT * FROM SourceTable;
— Good
INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM SourceTable;
— 4. Use schema names
— Bad
SELECT * FROM Students;
— Good
SELECT * FROM dbo.Students;
— 5. Avoid cursors when possible
— Bad
DECLARE student_cursor CURSOR FOR
SELECT StudentID FROM Students;
— … cursor logic
— Good
UPDATE s
SET s.GPA = s.GPA + 0.1
FROM Students s
WHERE s.Department = ‘Computer Science’;
— 6. Use SET NOCOUNT ON in procedures
CREATE PROCEDURE usp_Example
AS
BEGIN
SET NOCOUNT ON; — Reduces network traffic
— Your code
END;
— 7. Use table variables for small datasets
DECLARE @SmallList TABLE (
ID INT,
Name VARCHAR(100)
);
— Use temp tables for large datasets
CREATE TABLE #LargeList (
ID INT,
Name VARCHAR(100),
INDEX IX_ID (ID)
);
### **10.3 Common Patterns and Solutions**
**Pagination:**sql
CREATE PROCEDURE usp_GetStudentsPaginated
@PageNumber INT = 1,
@PageSize INT = 10
AS
BEGIN
SELECT
StudentID,
FirstName,
LastName,
Email,
GPA
FROM Students
ORDER BY StudentID
OFFSET (@PageNumber – 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
-- Return total count
SELECT COUNT(*) AS TotalRecords FROM Students;END;
EXEC usp_GetStudentsPaginated @PageNumber = 2, @PageSize = 20;
**Running Totals (Without Window Functions):**sql
— Using correlated subquery
SELECT
e.EnrollmentDate,
e.StudentID,
(SELECT COUNT(*)
FROM Enrollments e2
WHERE e2.EnrollmentDate <= e.EnrollmentDate) AS RunningTotal
FROM Enrollments e
ORDER BY e.EnrollmentDate;
— Using window function (better)
SELECT
EnrollmentDate,
StudentID,
COUNT(*) OVER (ORDER BY EnrollmentDate) AS RunningTotal
FROM Enrollments
ORDER BY EnrollmentDate;
**Dealing with Duplicates:**sql
— Find duplicates
SELECT
Email,
COUNT() AS DuplicateCount FROM Students GROUP BY Email HAVING COUNT() > 1;
— Remove duplicates (keep lowest ID)
WITH CTE_Duplicates AS (
SELECT
StudentID,
Email,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY StudentID) AS RowNum
FROM Students
)
DELETE FROM CTE_Duplicates
WHERE RowNum > 1;
— Prevent future duplicates
CREATE UNIQUE INDEX UQ_Students_Email ON Students(Email);
**Slowly Changing Dimensions (SCD Type 2):**sql
CREATE TABLE DimStudent (
StudentKey INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
GPA DECIMAL(3,2),
EffectiveDate DATE,
EndDate DATE,
IsCurrent BIT
);
— Insert new record and expire old one
CREATE PROCEDURE usp_UpdateStudentSCD
@StudentID INT,
@NewDepartment VARCHAR(50),
@NewGPA DECIMAL(3,2)
AS
BEGIN
BEGIN TRANSACTION;
-- Expire current record
UPDATE DimStudent
SET EndDate = GETDATE(),
IsCurrent = 0
WHERE StudentID = @StudentID AND IsCurrent = 1;
-- Insert new record
INSERT INTO DimStudent (StudentID, FirstName, LastName, Department, GPA, EffectiveDate, EndDate, IsCurrent)
SELECT
@StudentID,
FirstName,
LastName,
@NewDepartment,
@NewGPA,
GETDATE(),
'9999-12-31',
1
FROM Students
WHERE StudentID = @StudentID;
COMMIT TRANSACTION;END;
**Audit Trail Pattern:**sql
CREATE TABLE AuditTrail (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
TableName VARCHAR(128),
Operation VARCHAR(10), — INSERT, UPDATE, DELETE
RecordID INT,
OldValues NVARCHAR(MAX),
NewValues NVARCHAR(MAX),
ChangedBy VARCHAR(100),
ChangedDate DATETIME DEFAULT GETDATE()
);
CREATE TRIGGER trg_Students_AuditTrail
ON Students
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Operation VARCHAR(10);
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
SET @Operation = 'UPDATE';
ELSE IF EXISTS (SELECT * FROM inserted)
SET @Operation = 'INSERT';
ELSE
SET @Operation = 'DELETE';
INSERT INTO AuditTrail (TableName, Operation, RecordID, OldValues, NewValues, ChangedBy)
SELECT
'Students',
@Operation,
COALESCE(i.StudentID, d.StudentID),
(SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
(SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
SYSTEM_USER
FROM inserted i
FULL OUTER JOIN deleted d ON i.StudentID = d.StudentID;END;
### **10.4 Troubleshooting Common Issues**
**Blocking and Deadlocks:**sql
— Find blocking
SELECT
blocking.session_id AS BlockingSessionID,
blocked.session_id AS BlockedSessionID,
blocking.wait_type,
blocking.wait_time,
blocking_sql.text AS BlockingSQL,
blocked_sql.text AS BlockedSQL
FROM sys.dm_exec_requests blocked
INNER 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;
— Kill blocking session (use cautiously!)
KILL 53; — Replace with actual SPID
— View deadlock graph
SELECT CAST(target_data AS XML) AS DeadlockGraph
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = ‘system_health’
AND target_name = ‘ring_buffer’;
**High CPU Usage:**sql
— Find expensive queries
SELECT TOP 10
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS query_text,
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_time_ms,
qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_time_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;
**Parameter Sniffing Issues:**sql
— Detect parameter sniffing
— Look for wide variance in execution times for same procedure
— Solution 1: OPTION (RECOMPILE)
CREATE PROCEDURE usp_GetStudents
@Department VARCHAR(50)
AS
BEGIN
SELECT * FROM Students
WHERE Department = @Department
OPTION (RECOMPILE);
END;
— Solution 2: OPTION (OPTIMIZE FOR)
CREATE PROCEDURE usp_GetStudents
@Department VARCHAR(50)
AS
BEGIN
SELECT * FROM Students
WHERE Department = @Department
OPTION (OPTIMIZE FOR (@Department = ‘Computer Science’));
END;
— Solution 3: Local variable
CREATE PROCEDURE usp_GetStudents
@Department VARCHAR(50)
AS
BEGIN
DECLARE @LocalDept VARCHAR(50) = @Department;
SELECT * FROM Students
WHERE Department = @LocalDept;
END;
**TempDB Contention:**sql
— Check TempDB usage
SELECT
SUM(unallocated_extent_page_count) / 128.0 AS FreeMB,
SUM(user_object_reserved_page_count) / 128.0 AS UserObjectsMB,
SUM(internal_object_reserved_page_count) / 128.0 AS InternalObjectsMB
FROM sys.dm_db_file_space_usage;
— Identify queries using TempDB
SELECT
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t3.text
FROM (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM(internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id
) AS t1
INNER JOIN sys.dm_exec_requests AS t2 ON t1.session_id = t2.session_id
CROSS APPLY sys.dm_exec_sql_text(t2.sql_handle) AS t3
ORDER BY t1.task_alloc DESC;
---
## **FINAL PROJECT & ASSESSMENT**
### **Comprehensive School Management System**sql
— Create complete database schema
CREATE DATABASE SchoolManagementSystem;
USE SchoolManagementSystem;
— 1. Create all tables with proper constraints
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY IDENTITY(1,1),
DepartmentName VARCHAR(100) NOT NULL UNIQUE,
DepartmentHead INT NULL,
Budget DECIMAL(12,2),
CreatedDate DATETIME DEFAULT GETDATE()
);
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(20),
DepartmentID INT NOT NULL,
HireDate DATE NOT NULL,
Salary DECIMAL(10,2),
IsActive BIT DEFAULT 1,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
CREATE TABLE Students (
StudentID INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(20),
DateOfBirth DATE NOT NULL,
DepartmentID INT NOT NULL,
EnrollmentDate DATE DEFAULT GETDATE(),
GPA DECIMAL(3,2) CHECK (GPA >= 0 AND GPA <= 4.0),
IsActive BIT DEFAULT 1,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
CREATE TABLE Courses (
CourseID INT PRIMARY KEY IDENTITY(1,1),
CourseName VARCHAR(100) NOT NULL,
CourseCode VARCHAR(10) UNIQUE NOT NULL,
Credits INT CHECK (Credits > 0 AND Credits <= 6),
DepartmentID INT NOT NULL,
InstructorID INT,
MaxEnrollment INT DEFAULT 30,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY IDENTITY(1,1),
StudentID INT NOT NULL,
CourseID INT NOT NULL,
EnrollmentDate DATE DEFAULT GETDATE(),
Grade CHAR(2),
Status VARCHAR(20) DEFAULT ‘Enrolled’,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
UNIQUE (StudentID, CourseID)
);
— 2. Create indexes
CREATE INDEX IX_Students_Department ON Students(DepartmentID);
CREATE INDEX IX_Students_Email ON Students(Email);
CREATE INDEX IX_Enrollments_Student ON Enrollments(StudentID);
CREATE INDEX IX_Enrollments_Course ON Enrollments(CourseID);
CREATE INDEX IX_Instructors_Department ON Instructors(DepartmentID);
— 3. Create views
CREATE VIEW vw_StudentEnrollmentDetails AS
SELECT
s.StudentID,
s.FirstName + ‘ ‘ + s.LastName AS StudentName,
s.Email,
d.DepartmentName,
c.CourseName,
c.CourseCode,
c.Credits,
i.FirstName + ‘ ‘ + i.LastName AS InstructorName,
e.Grade,
e.EnrollmentDate
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID
INNER JOIN Departments d ON s.DepartmentID = d.DepartmentID
LEFT JOIN Instructors i ON c.InstructorID = i.InstructorID;
— 4. Create stored procedures
CREATE PROCEDURE usp_EnrollStudent
@StudentID INT,
@CourseID INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Validate student exists and is active
IF NOT EXISTS (SELECT 1 FROM Students WHERE StudentID = @StudentID AND IsActive = 1)
THROW 50001, 'Invalid or inactive student', 1;
-- Validate course exists
IF NOT EXISTS (SELECT 1 FROM Courses WHERE CourseID = @CourseID)
THROW 50002, 'Course not found', 1;
-- Check if already enrolled
IF EXISTS (SELECT 1 FROM Enrollments WHERE StudentID = @StudentID AND CourseID = @CourseID)
THROW 50003, 'Student already enrolled in this course', 1;
-- Check course capacity
DECLARE @CurrentEnrollment INT, @MaxEnrollment INT;
SELECT @MaxEnrollment = MaxEnrollment FROM Courses WHERE CourseID = @CourseID;
SELECT @CurrentEnrollment = COUNT(*) FROM Enrollments WHERE CourseID = @CourseID;
IF @CurrentEnrollment >= @MaxEnrollment
THROW 50004, 'Course is full', 1;
-- Enroll student
INSERT INTO Enrollments (StudentID, CourseID)
VALUES (@StudentID, @CourseID);
COMMIT TRANSACTION;
SELECT 'Enrollment successful' AS Message, SCOPE_IDENTITY() AS EnrollmentID;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;END;
— 5. Create functions
CREATE FUNCTION dbo.fn_GetStudentGPA
(
@StudentID INT
)
RETURNS DECIMAL(3,2)
AS
BEGIN
DECLARE @GPA DECIMAL(3,2);
SELECT @GPA = AVG(
CASE Grade
WHEN 'A' THEN 4.0
WHEN 'A-' THEN 3.7
WHEN 'B+' THEN 3.3
WHEN 'B' THEN 3.0
WHEN 'B-' THEN 2.7
WHEN 'C+' THEN 2.3
WHEN 'C' THEN 2.0
WHEN 'C-' THEN 1.7
WHEN 'D' THEN 1.0
ELSE 0.0
END
)
FROM Enrollments
WHERE StudentID = @StudentID AND Grade IS NOT NULL;
RETURN ISNULL(@GPA, 0.0);END;
— 6. Create triggers
CREATE TRIGGER trg_UpdateStudentGPA
ON Enrollments
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE s
SET s.GPA = dbo.fn_GetStudentGPA(s.StudentID)
FROM Students s
INNER JOIN inserted i ON s.StudentID = i.StudentID
WHERE i.Grade IS NOT NULL;END;
— 7. Insert sample data
— Departments
INSERT INTO Departments (DepartmentName, Budget)
VALUES
(‘Computer Science’, 500000),
(‘Mathematics’, 350000),
(‘Physics’, 400000),
(‘Engineering’, 600000);
— Instructors
INSERT INTO Instructors (FirstName, LastName, Email, Phone, DepartmentID, HireDate, Salary)
VALUES
(‘John’, ‘Smith’, ‘john.smith@school.edu’, ‘555-0101’, 1, ‘2018-08-15’, 75000),
(‘Jane’, ‘Doe’, ‘jane.doe@school.edu’, ‘555-0102’, 1, ‘2019-01-10’, 70000),
(‘Robert’, ‘Johnson’, ‘robert.j@school.edu’, ‘555-0103’, 2, ‘2017-06-01’, 72000);
— Courses
INSERT INTO Courses (CourseName, CourseCode, Credits, DepartmentID, InstructorID, MaxEnrollment)
VALUES
(‘Introduction to Programming’, ‘CS101’, 3, 1, 1, 30),
(‘Data Structures’, ‘CS201’, 4, 1, 1, 25),
(‘Calculus I’, ‘MATH101’, 4, 2, 3, 40),
(‘Linear Algebra’, ‘MATH201’, 3, 2, 3, 30);
— Students
INSERT INTO Students (FirstName, LastName, Email, Phone, DateOfBirth, DepartmentID, GPA)
VALUES
(‘Alice’, ‘Williams’, ‘alice.w@student.edu’, ‘555-1001’, ‘2002-03-15’, 1, 3.8),
(‘Bob’, ‘Brown’, ‘bob.b@student.edu’, ‘555-1002’, ‘2001-07-22’, 1, 3.5),
(‘Charlie’, ‘Davis’, ‘charlie.d@student.edu’, ‘555-1003’, ‘2002-11-08’, 2, 3.6);
— Test enrollment procedure
EXEC usp_EnrollStudent @StudentID = 1, @CourseID = 1;
EXEC usp_EnrollStudent @StudentID = 1, @CourseID = 2;
EXEC usp_EnrollStudent @StudentID = 2, @CourseID = 1;
— 8. Create reporting queries
— Student performance report
SELECT
d.DepartmentName,
COUNT(DISTINCT s.StudentID) AS TotalStudents,
AVG(s.GPA) AS AverageDepartmentGPA,
COUNT(DISTINCT e.EnrollmentID) AS TotalEnrollments
FROM Departments d
LEFT JOIN Students s ON d.DepartmentID = s.DepartmentID
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
GROUP BY d.DepartmentID, d.DepartmentName
ORDER BY AverageDepartmentGPA DESC;
— Course enrollment report
SELECT
c.CourseCode,
c.CourseName,
i.FirstName + ‘ ‘ + i.LastName AS Instructor,
COUNT(e.EnrollmentID) AS CurrentEnrollment,
c.MaxEnrollment,
c.MaxEnrollment – COUNT(e.EnrollmentID) AS AvailableSeats
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
LEFT JOIN Instructors i ON c.InstructorID = i.InstructorID
GROUP BY c.CourseID, c.CourseCode, c.CourseName, c.MaxEnrollment, i.FirstName, i.LastName
ORDER BY c.CourseCode;
PRACTICE EXERCISES AND CHALLENGES
Exercise Set 1: Basic Queries
- Retrieve all students with GPA above 3.5
- Find students enrolled in more than 3 courses
- List all courses with no enrollments
- Calculate average GPA by department
- Find the top 5 most popular courses
Exercise Set 2: Intermediate
- Create a stored procedure to transfer a student to another department
- Write a query to find students who haven’t enrolled in any courses
- Create a view showing instructor workload (number of courses and students)
- Implement a function to calculate course completion percentage
- Write a query using window functions to rank students within each department
Exercise Set 3: Advanced
- Implement a complete audit system for all tables
- Create a procedure for bulk student enrollment with validation
- Design and implement a grading system with weighted categories
- Build a dynamic reporting system using dynamic SQL
- Implement row-level security based on department
QUICK REFERENCE GUIDE
Common System Stored Procedures:
sp_help 'TableName' -- Table information
sp_helptext 'ObjectName' -- Object definition
sp_depends 'ObjectName' -- Dependencies
sp_who2 -- Active processes
sp_spaceused -- Database/table size
sp_helpindex 'TableName' -- Index information
sp_helpconstraint 'TableName' -- Constraint informationCommon System Functions:
@@VERSION -- SQL Server version
@@SERVERNAME -- Server name
@@ROWCOUNT -- Rows affected
@@IDENTITY -- Last identity value
@@TRANCOUNT -- Active transactions
SCOPE_IDENTITY() -- Last identity in current scope
DB_NAME() -- Current database
OBJECT_NAME(object_id) -- Object name from ID
SYSTEM_USER -- Current userCommon DMVs:
sys.dm_exec_sessions -- Active sessions
sys.dm_exec_requests -- Active requests
sys.dm_exec_query_stats -- Query statistics
sys.dm_db_index_usage_stats -- Index usage
sys.dm_os_wait_stats -- Wait statistics
sys.databases -- Database list
sys.tables -- Table list
sys.columns -- Column informationCONCLUSION & NEXT STEPS
You now have a complete, comprehensive training manual for SQL Server Management Studio covering everything from installation to advanced topics. This document provides:
✅ Complete theoretical knowledge
✅ Practical code examples
✅ Real-world scenarios
✅ Best practices
✅ Troubleshooting guides
✅ Performance optimization
✅ Security implementation
✅ Backup and recovery
Recommended Learning Path:
- Complete each module sequentially
- Practice all code examples
- Complete all exercises
- Build the final project
- Review and master troubleshooting
- Explore additional Microsoft documentation for specific advanced features
Additional Resources to Explore:
- Microsoft SQL Server Documentation
- Execution plan analysis tools
- Third-party monitoring solutions
- Community forums and best practices
