Back

SQL Server DBA Quick-Study (2025 Edition)

Updated, simplified, and organized so students can skim, learn, and ace interviews.


1 | Upgrade Troubleshooting

Problem Why It Happens Fix in 2025
Setup “hangs” or rolls back during an in-place upgrade Background services, apps, or SSMS windows still hold connections → SQL Server cannot shut down to apply binaries. 1. Stop the SQL Agent and any third-party monitoring tools.2. ALTER DATABASE … SET SINGLE_USER WITH ROLLBACK IMMEDIATE; on user DBs.3. Re-run setup as admin and watch C:\Program Files\Microsoft SQL Server\<ver>\Setup Bootstrap\Log for errors.
Need to undo a failed upgrade In-place installs overwrite binaries. Always keep full backups of system & user DBs plus the master key; if the upgrade bombs, uninstall SQL Server 2022 → reinstall the previous build → restore backups. Easier: do a side-by-side upgrade (new VM / container) and switch DNS when validated.

2 | High-Availability & Disaster-Recovery Cheat Sheet

Technology Scope Traffic Direction Key Terminology
Log Shipping 1 database T-log backups copied & restored on schedule Primary, Secondary, (optional) Monitor
Database Mirroring (legacy) 1 database Synchronous or async stream to mirror Principal, Mirror, Witness
Always On Availability Groups 1–n databases as a unit Synchronous (HA) or async (DR) replicas Primary replica, Secondary replicas
Failover Cluster Instance (FCI) Whole SQL instance Shared storage; Windows Failover Clustering Active node, Passive node

Tip: Microsoft recommends Availability Groups for new designs; database mirroring will be removed in a future version.


3 | Replication Refresher

Role Meaning
Publisher Source database that owns the data/articles
Distributor Stores snapshot & transaction data; can be on same server or standalone
Subscriber Receives the data (push or pull subscriptions)

Types: Snapshot, Transactional, Merge, Peer-to-Peer (xl-scale).


4 | SSIS & Friends

  • SSIS = SQL Server Integration Services → Microsoft’s ETL / ELT platform.
    Modern uses: data lake ingestion, cloud lifts via Azure Data Factory (SSIS Integration Runtime).

  • Analysis Services (SSAS) → Multidimensional or Tabular models for analytic cubes, KPIs.

  • Reporting Services (SSRS) → Pixel-perfect paginated, mobile, or Power BI-linked reports.


5 | DBCC CHECKDB Performance Switches

-- Typical weekly deep check
DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS;  

-- Faster daily “quick” check
DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, PHYSICAL_ONLY;  

-- Even faster (offline!) maintenance window
DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, TABLOCK;
  • PHYSICAL_ONLY → header & allocation checks only (catches torn pages, checksum faults).

  • MAXDOP = x hint can cap CPU usage if the server is busy.


6 | Concurrency & Locking Essentials

Isolation Level Lock Duration (read) Row-version support?
READ COMMITTED Brief (released after read) when READ_COMMITTED_SNAPSHOT = ON
SNAPSHOT None (reads from version store) ✅ (always)
REPEATABLE READ Held until txn commit
SERIALIZABLE Range locks held until commit

Turn on ALTER DATABASE <db> SET ALLOW_SNAPSHOT_ISOLATION = ON; for SNAPSHOT,
… SET READ_COMMITTED_SNAPSHOT = ON; for optimistic READ COMMITTED.

  • Intent Locks – lightweight parent-level indicators (e.g., IX, IS) that advertise a forthcoming row-level lock; prevent incompatible locks higher in the hierarchy.


7 | Maintenance One-Liners

Task Syntax
Truncate a table (reset identity, keep schema) TRUNCATE TABLE dbo.MyTable;
Show estimated plan SET SHOWPLAN_XML ON; — run your query (no execution)
Top n rows SELECT TOP (60) * FROM dbo.MyTable;
Insert row INSERT INTO dbo.MyTable (col1, col2) VALUES (v1, v2);

Reading a graphical plan: start right-to-left; high-cost operators (> 40 %) or thick data-flow arrows signal tuning targets (missing index, key lookup, sort, etc.).


8 | Glossary Lightning Round

  • Log Shipping – scheduled T-log backup copy/restore for warm stand-by.

  • Mirroring – real-time copy to a single mirror DB (deprecated in favor of AGs).

  • Clustering (FCI) – Windows Failover Cluster + shared disks; protects instance.

  • Always On AG – group of DBs replicated to up to eight secondaries; read-scale, automatic failover.

  • SHOWPLAN – SQL Server’s family of “reveal the plan” session settings (SHOWPLAN_TEXT, SHOWPLAN_XML).


Study Checklist

  1. Master backup/restore before touching HA features.

  2. Know when to pick Log Shipping vs AG vs FCI.

  3. Practice DBCC CHECKDB options on a sample DB to see speed trade-offs.

  4. Rehearse reading an execution plan and naming the worst operator in < 60 seconds.

  5. Keep this sheet handy—most classroom and interview questions map back here.

Happy learning!

				
					Insert into table(Column1, column2, etc) Values(‘value1’, ‘value2’, etc)

				
			

 


SQL Server Reporting & Query Optimization Cheat-Sheet


1 | What are the environments used to develop reports in SQL Server?

Environment Purpose
BIDS (Business Intelligence Development Studio) Legacy tool used in SQL Server 2005–2008R2 for creating SSRS, SSIS, and SSAS projects.
SSDT (SQL Server Data Tools) Modern replacement for BIDS, used from SQL Server 2012 onward. Integrated into Visual Studio for building SSRS reports, SSIS packages, and database projects.

📌 Today, SSDT for Visual Studio 2022 is the standard for developing SQL Server Reporting Services (SSRS) reports.


2 | What is an Execution Plan?

An Execution Plan is a roadmap used by SQL Server to execute a query. It shows the steps SQL Server takes—like index scans, joins, sorts—and the estimated cost of each step.

  • Why it matters: It helps developers and DBAs optimize slow queries.

  • You can view:

    • Estimated Plan (won’t run the query)

    • Actual Plan (runs the query and shows real-time stats)

-- Turn on estimated plan
SET SHOWPLAN_ALL ON;
GO
-- Your SQL query here
GO
SET SHOWPLAN_ALL OFF;

💡 Use the “Include Actual Execution Plan” button in SSMS to view it graphically.


3 | How to delete today’s records from a table with a timestamp column?

Assuming the column is named CreatedDate and is of type datetime or datetime2:

DELETE FROM YourTable
WHERE CAST(CreatedDate AS DATE) = CAST(GETDATE() AS DATE);

✅ This removes only the rows created today, regardless of the exact time.

📌 CAST(... AS DATE) strips the time part so the comparison works accurately by date only.

 

				
					Delete from table where datediff(day, timestamp, getdate()) = 0

				
			

 


SQL Server Execution Plan Cheat-Sheet


1 | Types of Query Execution Plans

Type Description When Used
Estimated Execution Plan Shows how SQL Server intends to execute the query — no data is actually retrieved. Used during development and query tuning without running the query.
Actual Execution Plan Shows the real steps and row counts after the query has been executed. Used to troubleshoot performance issues or validate estimated plans.

✅ In SSMS, right-click the query window → Display Estimated Execution Plan or click Include Actual Execution Plan before executing.


2 | Formats of Execution Plans

Format How to View Use Case
Graphical Plan Built-in SSMS visual tool (right-click → Display Execution Plan) Easiest to read, shows tooltips, costs, indexes, and arrows.
Text Plan SET SHOWPLAN_TEXT ON or SET STATISTICS PROFILE ON Useful in scripting or lightweight environments.
XML Plan SET SHOWPLAN_XML ON or right-click plan → Save as .sqlplan Needed for deep analysis, plan caching, or automation tools.

💡 SSMS uses .sqlplan (XML) format behind the scenes for graphical plans.


3 | Permissions Required to View Execution Plans

Role-based Access Users must belong to one of these roles:
sysadmin Full access
db_owner Full database-level access
db_creator Can view plans for databases they create

🔐 To grant access without elevated roles:

GRANT SHOWPLAN TO [username];

This permission allows the user to view estimated execution plans without running the query. For actual plans, they still need execution rights on the objects involved.


 

				
					GRANT SHOWPLAN TO [username]

				
			

SQL Server DBA Cheat-Sheet #2

(Indexes, Programmability, Joins, Integrity, Design, & Troubleshooting — refreshed for SQL Server 2022)


1 | Index Essentials

Concept Key Points (2025)
Why indexes speed queries Engine uses a B-tree to jump straight to matching rows instead of scanning the whole table (like a book’s index).
Clustered vs Non-Clustered Clustered: defines the physical row order; one per table.Non-clustered: separate structure with row pointers (RID/clustered-key); many allowed.
Covering (wide) index Non-clustered index that contains all columns a query needs (key + INCLUDE). No look-ups → pure index-only read.
Update heavy columns Lots of UPDATEs = frequent page splits + fragmentation → schedule REORGANIZE (ALTER INDEX … REORGANIZE) or REBUILD.
Modern extras Filtered indexes = WHERE clause on the index.• Columnstore (xVelocity) = column-oriented analytics; huge scan speed-ups.• Memory-optimized hash / range indexes for In-Memory OLTP.

2 | Stored Procedures vs Functions vs Triggers

  Stored Procedure (SP) User-Defined Function (UDF) Trigger
Returns RETURN INT code + optional OUTPUT params Scalar, inline table, or multi-statement table N/A (fires automatically)
DML allowed? Yes No for scalar / multi-stmt; inline UDF can’t issue DML Executes DML in response to INSERT/UPDATE/DELETE
Use inside SELECT? No Yes (deterministic, side-effect-free) No
Transactions Full control (BEGIN/COMMIT/ROLLBACK) Cannot issue transaction control statements Runs in the firing txn
Typical purpose Encapsulate business logic, batches Reusable calculations, computed columns Enforce complex integrity, auditing

3 | Join Refresher

  • INNER JOIN → rows that match in both tables.

  • LEFT (OUTER) JOIN → all rows from left table + matches on right; NULLs when no match.

SELECT  o.OrderID, c.Name
FROM    dbo.Orders AS o
LEFT JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID;

4 | Integrity via Constraints

Constraint Purpose
PRIMARY KEY Entity identity + implied unique clustered/non-clustered index
FOREIGN KEY Enforce valid parent-child relationship
UNIQUE Prevent duplicate values (allows one NULL unless NOT NULL)
CHECK Custom rule, e.g. CHECK (Quantity > 0)
DEFAULT Auto-populate column when no explicit value supplied

5 | Set Operators

  UNION UNION ALL
Duplicate handling Removes duplicates (uses sort/hash) Keeps all rows (faster)
Typical usage Combine distinct lists Merge logs / staging data where dupes allowed

6 | Normalization Snapshot

Form Rule (simplified)
1NF Atomic values, no repeating groups
2NF 1NF + every non-key column fully depends on whole PK
3NF 2NF + no transitive dependencies
BCNF 3NF + left side of every dependency is a super-key
4NF BCNF + no multi-valued dependencies
5NF 4NF + no join dependencies other than super-keys

Why care? Normalization reduces redundancy, update anomalies, and storage bloat. De-normalize only for performance, and document it.


7 | Views (Virtual Tables)

  • Encapsulate complex joins/filters.

  • Restrict access: expose only necessary columns (WITH SCHEMABINDING to lock schema).

  • Support indexed views (materialized) when SCHEMABINDING + deterministic expressions are used.


8 | Monitoring Tools

Legacy Modern Replacement
SQL Server Profiler / Trace Extended Events (XE) session — lower overhead, granular filtering
SHOWPLAN (SET SHOWPLAN_XML ON) Actual plan (Include Actual Execution Plan in SSMS) + sys.dm_exec_query_plan DMF

9 | ACID & Transactions

Property SQL Server Implementation
Atomicity Commit/Rollback ensures “all or none”.
Consistency Declarative constraints + checked during commit.
Isolation Isolation levels (READ COMMITTEDSERIALIZABLE, plus SNAPSHOT).
Durability WAL (transaction log) + CHECKPOINT; crash recovery replays committed log records.

10 | Deadlock Survival Guide

Occurs when Session A holds X on row 1, wants row 2; Session B vice-versa.
SQL Server runs a deadlock detector ~ every 5 s; it kills the session with lowest cost (victim).

Prevention Tips

  1. Access objects in the same sequence in all procs.

  2. Keep transactions short & narrow (proper indexes!).

  3. Avoid user interaction inside a transaction.

  4. Capture graphs: system_health XE session already logs XML graphs → view in SSMS.


11 | Quick Syntax Reminders

-- Top 60 rows
SELECT TOP (60) *
FROM dbo.MyTable
ORDER BY CreatedOn DESC;

-- Insert
INSERT INTO dbo.MyTable (Col1, Col2) VALUES (@v1, @v2);

-- See estimated plan only
SET SHOWPLAN_XML ON;
GO
-- <run your query>
GO
SET SHOWPLAN_XML OFF;

Study To-Dos

  1. Build & fragment a sample table; practice ALTER INDEX … REBUILD.

  2. Write a scalar UDF and see why it’s slow; replace with inline TVF.

  3. Spin up Extended Events template Standard Query.

  4. Simulate a deadlock with two SSMS windows; watch the victim.

Keep this sheet beside your notebook—master the patterns, not just the syntax. Happy learning!

SQL Server DBA Cheat-Sheet #3

(Advanced Concepts — Cursors, Fragmentation, Schema Design, Security, Partitioning & Query Optimization)


1 | Cursors in SQL Server

Concept Summary (2025)
What is a Cursor? A cursor lets you iterate row-by-row through a result set — like a loop in code.
Use case Complex row-based logic that can’t be done in a set-based query (rare).
Caution Cursors are slow and memory-intensive. Prefer set-based operations using JOIN, APPLY, CASE, MERGE, or window functions.
DECLARE my_cursor CURSOR FOR SELECT id FROM dbo.Customers;
OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @id;
-- Loop logic
CLOSE my_cursor;
DEALLOCATE my_cursor;

2 | Index Fragmentation Explained

Type Cause Fix
Internal Excess free space inside pages (due to deletes/updates) ALTER INDEX … REORGANIZE
External Pages physically out of order ALTER INDEX … REBUILD (or use Maintenance Plans / Ola scripts)

Run this to diagnose:

SELECT * FROM sys.dm_db_index_physical_stats
  (DB_ID(), OBJECT_ID('YourTable'), NULL, NULL, 'LIMITED');

3 | Schemas in SQL Server

Think of a schema as a “folder” inside your database for organizing objects.

  • Syntax: CREATE SCHEMA Sales AUTHORIZATION dbo;

  • Tables: Sales.Orders, Sales.Customers

  • Benefits:

    • Security: Grant permissions at the schema level.

    • Organization: Group logically related objects.

    • Namespace separation: Avoid name collisions (e.g., HR.Employees vs. Sales.Employees).


4 | DELETE vs TRUNCATE

DELETE TRUNCATE
Row-by-row operation (fully logged) Minimal logging (faster)
Can use WHERE Cannot filter; deletes all rows
Triggers fire Triggers don’t fire
Retains identity seed Resets identity seed (in most versions)

5 | Parameterized Queries = Security

Parameterized SQL uses placeholders, reducing risk of SQL injection:

-- BAD (injectable)
EXEC('SELECT * FROM Users WHERE name = ''' + @name + '''')

-- GOOD (parameterized)
SELECT * FROM Users WHERE name = @name

All modern client libraries (ADO.NET, Entity Framework, etc.) support parameters.


6 | #Temporary Table vs Table Variable

Feature #Temp Table @Table Variable
Scope Session-level Batch/procedure-level
Supports indexes? ✅ (can CREATE or declare with index) ✅ (but no stats for optimizer)
Transaction control ❌ (ignored by transactions)
Performance Better for large sets / joins Better for small datasets / simple procs

7 | HAVING Clause

Use HAVING to filter after aggregation:

-- Show products with more than 5 orders
SELECT ProductID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY ProductID
HAVING COUNT(*) > 5;

Use WHERE before grouping, HAVING after.


8 | Authentication Modes

Mode Summary
Windows Authentication Integrated login via Active Directory; best for security.
SQL Server Authentication Username + password stored in SQL Server; less secure; used for app logins or cross-domain scenarios.

Enable both (Mixed Mode) if needed, but use Windows Auth whenever possible.


9 | SQL Server Agent

Automation powerhouse inside SQL Server.

  • Runs Jobs: backups, ETL tasks, index rebuilds, alerts

  • Monitors: job history, success/failure

  • UI: Available in SSMS → “SQL Server Agent” → Jobs

Jobs include:

  • Steps (T-SQL, SSIS packages, OS commands)

  • Schedules

  • Alerts and notifications (e.g., send email on job failure)


10 | Partitioning

Partitioning breaks a table/index into logical chunks (e.g., by date or region)
Benefits:

  • Faster scans & maintenance

  • Can switch partitions in/out (fast archiving)

-- Example (Partition by year)
CREATE PARTITION FUNCTION pfYearRange (int)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021);

Use Partition Schemes + check execution plan for partition elimination.


11 | CLR Integration

CLR = Common Language Runtime (runs .NET code inside SQL Server)

  • Build in C#, compile to DLL, and register:

CREATE ASSEMBLY MyAssembly FROM 'C:\my.dll'
WITH PERMISSION_SET = SAFE;

Use for:

  • Regex

  • File access

  • Crypto

  • Advanced math

Must enable CLR: sp_configure 'clr enabled', 1;


12 | Data Warehousing Overview

  Operational DB Data Warehouse
Purpose CRUD / transactions Analysis, reporting
Design OLTP (3NF) OLAP (star/snowflake)
Data latency Real-time Batch-processed
Examples Point-of-sale system Sales dashboard, Power BI reports

ETL via SSIS or Azure Data Factory → Warehouse → Reports


13 | JOIN vs SUBQUERY

  JOIN SUBQUERY
Combines tables in one flat result Query inside a query (nested)  
More readable & efficient for multi-table logic Better for filtering or scalar values  
Example:    
-- JOIN
SELECT o.OrderID, c.Name
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

-- SUBQUERY
SELECT OrderID
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Region = 'East');

Final Tips for Students

  1. Avoid cursors unless absolutely needed.

  2. Indexes = performance, but watch for fragmentation.

  3. Use parameterized queries — always.

  4. Understand JOINs, temp tables, and execution plans deeply.

  5. Practice daily in SSMS or Azure Data Studio.

✔ Mastering this cheat-sheet = solid SQL Server foundations.

Unlock your potential and shape the future of database administration with SQL DBA School – where learning meets innovation!

sql classes free online - sql training course online

SQL DBA School provides an in-depth, comprehensive curriculum designed to empower students with essential skills and knowledge in database administration.

Work Hours

“SQL DBA School: Navigating You to Database Excellence!”

Digital Marketing Courses Template Kit by Jegtheme
Copyright © 2022. All rights reserved.