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 optimisticREAD 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
-
Master backup/restore before touching HA features.
-
Know when to pick Log Shipping vs AG vs FCI.
-
Practice
DBCC CHECKDB
options on a sample DB to see speed trade-offs. -
Rehearse reading an execution plan and naming the worst operator in < 60 seconds.
-
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 UPDATE s = 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 COMMITTED → SERIALIZABLE , 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
-
Access objects in the same sequence in all procs.
-
Keep transactions short & narrow (proper indexes!).
-
Avoid user interaction inside a transaction.
-
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
-
Build & fragment a sample table; practice
ALTER INDEX … REBUILD
. -
Write a scalar UDF and see why it’s slow; replace with inline TVF.
-
Spin up Extended Events template Standard Query.
-
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
-
Avoid cursors unless absolutely needed.
-
Indexes = performance, but watch for fragmentation.
-
Use parameterized queries — always.
-
Understand JOINs, temp tables, and execution plans deeply.
-
Practice daily in SSMS or Azure Data Studio.
✔ Mastering this cheat-sheet = solid SQL Server foundations.