As organizations rely on relational databases for transactional systems, reporting, and analytics, recruiters must identify MS SQL Server professionals who can design, manage, and optimize high-performance database environments. Microsoft SQL Server is widely used in enterprise applications, BI systems, and mission-critical workloads.
This resource, "100+ MS SQL Interview Questions and Answers," is tailored for recruiters to simplify the evaluation process. It covers a wide range of topics—from SQL Server fundamentals to advanced database administration and performance tuning, including T-SQL, indexing, and security.
Whether you're hiring SQL Developers, Database Administrators (DBAs), Data Engineers, or BI Professionals, this guide enables you to assess a candidate’s:
For a streamlined assessment process, consider platforms like WeCP, which allow you to:
Save time, enhance your hiring process, and confidently hire MS SQL professionals who can build, manage, and optimize reliable database systems from day one.
MS SQL Server is a relational database management system (RDBMS) developed by Microsoft that is used to store, manage, retrieve, and manipulate structured data efficiently. It is designed to handle large volumes of data while ensuring data integrity, security, high availability, and performance.
SQL Server uses T-SQL (Transact-SQL) as its primary query language, which extends standard SQL with additional features such as variables, error handling, procedural logic, and system functions. It is widely used in enterprise applications, web applications, data warehousing, reporting systems, and business intelligence solutions.
Key strengths of MS SQL Server include:
In simple terms, MS SQL Server acts as a central system where applications store and retrieve data reliably and securely.
SQL Server consists of several core components that work together to manage data and support applications:
Together, these components make SQL Server a complete data platform, not just a database.
A database is an organized collection of structured data stored electronically in a way that allows efficient access, management, and updating. In SQL Server, a database serves as a container that holds tables, views, stored procedures, functions, indexes, and other database objects.
Each database in SQL Server has:
Databases help in:
Examples include system databases (master, model, tempdb, msdb) and user-created databases for applications.
A table is a database object used to store data in a structured format of rows and columns. Each table represents a specific entity, such as customers, orders, or products.
A table consists of:
Tables are fundamental to relational databases because:
Tables can also have constraints, indexes, triggers, and relationships to maintain data accuracy and performance.
A row represents a single record in a table. Each row contains data values for all columns defined in that table. For example, one row may represent one customer.
A column represents an attribute or field of the data. Each column has:
Example:
Columns define the structure, while rows hold the actual data.
A primary key is a constraint that uniquely identifies each row in a table. It ensures that:
Primary keys are critical for:
Characteristics:
Example: CustomerID as a primary key uniquely identifies each customer.
A foreign key is a constraint that creates a relationship between two tables by referencing the primary key (or unique key) of another table.
Purpose of a foreign key:
Example:
Foreign keys ensure that:
Both primary key and unique key enforce uniqueness, but they differ in important ways:
Key differences:
Primary keys are essential, while unique keys are optional based on design needs.
NULL represents the absence of a value. It does not mean zero, empty string, or false—it means unknown or not applicable.
Important characteristics:
= do not work with NULLIS NULL or IS NOT NULLExample:
SELECT * FROM Employees WHERE MiddleName IS NULL;
NULL helps represent missing or optional data but should be handled carefully to avoid incorrect query results.
SQL constraints are rules applied to columns or tables to enforce data integrity and consistency.
Common SQL Server constraints include:
Constraints ensure:
They act as the first line of defense for data quality in SQL Server.
The SELECT statement is the most fundamental SQL command used to retrieve data from one or more tables or views in SQL Server. It allows users to specify what data they want to see and how it should be presented.
Using SELECT, you can:
SELECT *)Example:
SELECT CustomerName, City FROM Customers;
Key points:
In short, the SELECT statement is used to read and analyze data stored in SQL Server.
The WHERE clause is used to filter rows based on specified conditions. It ensures that only relevant records are returned, updated, or deleted.
Without WHERE, SQL Server processes all rows in a table.
Example:
SELECT * FROM Employees WHERE Department = 'IT';
The WHERE clause supports:
=, >, <, >=, <=)AND, OR, NOT)LIKE)BETWEEN)IN)IS NULL)It is essential for:
The ORDER BY clause is used to sort query results in either ascending (ASC) or descending (DESC) order.
Example:
SELECT Name, Salary FROM Employees ORDER BY Salary DESC;
Key characteristics:
Example with multiple columns:
ORDER BY Department ASC, Salary DESC;
ORDER BY improves:
The GROUP BY clause is used to group rows that have the same values in specified columns and then apply aggregate functions to each group.
Example:
SELECT Department, COUNT(*)
FROM Employees
GROUP BY Department;
How it works:
Rules:
SELECT must appear in GROUP BYGROUP BY is widely used for:
The HAVING clause is used to filter grouped data after aggregation. While WHERE filters rows before grouping, HAVING filters after grouping.
Example:
SELECT Department, COUNT(*) AS EmpCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
Key differences:
WHERE → works on individual rowsHAVING → works on aggregated resultsUse cases:
DELETE and TRUNCATE both remove data from a table, but they work very differently.
DELETE
WHERE clauseTRUNCATE
WHEREUse DELETE for controlled removal and TRUNCATE for fast cleanup of entire tables.
DELETE removes data, while DROP removes the entire object.
DELETE
DROP
Example:
DROP TABLE Employees;
Use DELETE when you want to keep the table, and DROP when the table is no longer needed.
Aggregate functions perform calculations on a set of values and return a single summarized result.
They are commonly used with GROUP BY.
Common aggregate functions:
Example:
SELECT AVG(Salary) FROM Employees;
Use cases:
Aggregate functions ignore NULL values (except COUNT(*)).
These are the most commonly used aggregate functions:
Examples:
SELECT
COUNT(*) AS TotalEmployees,
SUM(Salary) AS TotalSalary,
AVG(Salary) AS AvgSalary,
MIN(Salary) AS MinSalary,
MAX(Salary) AS MaxSalary
FROM Employees;
They are essential for summary reports and decision-making.
A database schema is a logical container that organizes database objects such as tables, views, procedures, and functions.
Example:
SELECT * FROM Sales.Orders;
Benefits of schemas:
Common schemas:
dbo (default)saleshrfinanceSchemas help structure large databases and improve security, clarity, and manageability.
Normalization is a database design technique used to organize data efficiently by reducing data redundancy and improving data integrity. The main goal of normalization is to store data logically so that each piece of information is stored only once.
Normalization achieves this by:
Benefits of normalization:
Normalization is most commonly applied in OLTP systems where data integrity is critical.
Normal forms are rules or guidelines that define levels of database normalization. Each normal form builds on the previous one.
In real-world systems, most databases are normalized up to 3NF.
Denormalization is the intentional process of introducing redundancy into a database design to improve read performance.
Instead of splitting data into many tables, denormalization:
Trade-offs:
Denormalization is commonly used in:
An index is a database object that improves the speed of data retrieval operations on a table.
Indexes work similarly to a book index, allowing SQL Server to find data quickly without scanning the entire table.
Key points:
Example:
CREATE INDEX idx_emp_name ON Employees(Name);
Indexes are essential for performance tuning in SQL Server.
A clustered index determines the physical order of data in a table. The table’s data rows are stored in the same order as the clustered index key.
Key characteristics:
Example:
CREATE CLUSTERED INDEX idx_emp_id ON Employees(EmployeeID);
If a table has no clustered index, it is called a heap.
A non-clustered index is a separate structure from the table that stores index keys and pointers to the actual data rows.
Key characteristics:
Example:
CREATE NONCLUSTERED INDEX idx_emp_email ON Employees(Email);
Non-clustered indexes are ideal for frequently searched columns.
FeatureClustered IndexNon-Clustered IndexData orderPhysical order of dataLogical order onlyCountOne per tableMultiple allowedStorageData stored in indexSeparate index structureSpeedFaster for range queriesFaster for lookupsDefaultCreated with primary keyCreated manually
In short, clustered index defines how data is stored, while non-clustered index defines how data is searched.
A view is a virtual table created using a SQL query. It does not store data itself but displays data dynamically from one or more tables.
Example:
CREATE VIEW vw_EmployeeDetails AS
SELECT Name, Department FROM Employees;
Benefits:
Views are commonly used in reporting and application development.
A stored procedure is a precompiled collection of SQL statements stored in the database and executed as a single unit.
Key advantages:
Example:
CREATE PROCEDURE GetEmployees
AS
SELECT * FROM Employees;
Stored procedures can accept parameters and contain business logic.
A function is a database object that returns a value and can be used inside SQL statements.
Types of functions:
Example:
CREATE FUNCTION GetTax(@Salary INT)
RETURNS INT
AS
BEGIN
RETURN @Salary * 0.1
END
Functions are used for calculations, validations, and reusable logic.
A function and a stored procedure are both reusable database objects, but they serve different purposes and have different capabilities.
A function:
SELECT, WHERE, JOIN, etc.A stored procedure:
SELECT statementIn summary, functions are for computation, while stored procedures are for processing and operations.
A transaction is a logical unit of work that consists of one or more SQL statements that must be executed together as a single operation.
Example:
BEGIN TRANSACTION
UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;
Transactions ensure:
If any statement fails, the transaction can be rolled back to maintain correctness.
ACID properties define the reliability guarantees of transactions in SQL Server.
These properties ensure data integrity, especially in multi-user environments.
COMMIT and ROLLBACK are transaction control commands.
Example:
ROLLBACK;
They allow safe execution of critical operations and recovery from failures.
An IDENTITY column is used to automatically generate unique numeric values for a column, commonly used as primary keys.
Syntax:
EmployeeID INT IDENTITY(1,1)
Key points:
It is widely used to uniquely identify records.
A default constraint assigns a default value to a column when no value is provided during insertion.
Example:
Status VARCHAR(20) DEFAULT 'Active'
Benefits:
Default constraints help maintain data completeness.
A CHECK constraint enforces a logical condition on column values.
Example:
CHECK (Age >= 18)
Key features:
CHECK constraints improve data quality and reliability.
The NOT NULL constraint ensures that a column cannot contain NULL values.
Example:
Name VARCHAR(50) NOT NULL
Purpose:
Columns with NOT NULL must always have valid data.
The DISTINCT keyword is used to remove duplicate values from query results.
Example:
SELECT DISTINCT Department FROM Employees;
Key points:
It is commonly used in reporting and data analysis.
Both CHAR and VARCHAR store character data, but they differ in storage behavior.
CHAR
VARCHAR
Example:
Name CHAR(10)
Email VARCHAR(100)
Use CHAR for fixed-length values and VARCHAR for variable-length values.
Joins in SQL Server are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve meaningful data that is spread across multiple tables in a relational database.
Why joins are needed:
Common join conditions are based on:
Types of joins include:
Joins are fundamental for data retrieval, reporting, and analytics in SQL Server.
An INNER JOIN returns only the rows that have matching values in both tables involved in the join. Rows that do not have a matching record in the other table are excluded from the result.
Example:
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
Key characteristics:
INNER JOIN is ideal when only related data is required.
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If no match exists, NULL values are returned for right table columns.
Example:
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
Key characteristics:
LEFT JOIN is often used for data completeness analysis.
A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matching rows from the left table. If no match exists, NULL values appear for left table columns.
Example:
SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
Key characteristics:
RIGHT JOIN is used when the right table is the primary focus.
A FULL OUTER JOIN returns all rows from both tables, including matched and unmatched rows. When no match exists, NULL values are returned for missing columns.
Example:
SELECT e.Name, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;
Key characteristics:
FULL OUTER JOIN is ideal for comparison and audit scenarios.
A SELF JOIN is a join where a table is joined to itself. It is used when rows in the same table are related to each other.
Example:
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerID = m.EmployeeID;
Key characteristics:
SELF JOIN is useful for organizational hierarchies and recursive relationships.
A CROSS JOIN returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table.
Example:
SELECT a.Name, b.Product
FROM Customers a
CROSS JOIN Products b;
Key characteristics:
CROSS JOIN is used for combinations, permutations, and testing scenarios.
A subquery is a query nested inside another query. It can appear in SELECT, WHERE, FROM, or HAVING clauses.
Example:
SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Key points:
Subqueries help solve complex logic in a structured way.
A correlated subquery is a subquery that depends on values from the outer query and is executed once for each row processed by the outer query.
Example:
SELECT e.Name
FROM Employees e
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = e.DepartmentID
);
Key characteristics:
Correlated subqueries are powerful but should be used carefully.
Both UNION and UNION ALL are used to combine result sets of multiple SELECT statements.
UNION
UNION ALL
Example:
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;
Use UNION when duplicates must be removed, and UNION ALL when performance matters and duplicates are acceptable.
Temporary tables are special tables used to store temporary data during a SQL Server session or procedure execution. They are mainly used for intermediate result storage, complex transformations, and breaking large queries into manageable steps.
Types of temporary tables:
#TempTable)##TempTable)Key characteristics:
Temporary tables are widely used in ETL, reporting, and data processing scenarios.
Local and global temporary tables differ mainly in scope and visibility.
Local Temporary Table (#Temp)
Global Temporary Table (##Temp)
Example:
CREATE TABLE #LocalTemp (ID INT);
CREATE TABLE ##GlobalTemp (ID INT);
Local temp tables are preferred in most cases due to session isolation.
Table variables are in-memory table-like structures declared using the DECLARE statement. They are primarily used for small datasets and short-lived operations.
Example:
DECLARE @Employees TABLE (
ID INT,
Name VARCHAR(50)
);
Key characteristics:
Table variables are lightweight and easy to use for small result sets.
FeatureTable VariableTemporary TableDeclarationDECLARECREATE TABLEScopeBatch / procedureSessionIndexingLimitedFull indexingStatisticsNo (limited)YesPerformanceBetter for small dataBetter for large dataRollbackNot affectedTransaction-aware
Use table variables for small datasets and temporary tables for large, complex operations.
A Common Table Expression (CTE) is a temporary named result set defined using the WITH keyword. It exists only for the duration of a single query.
Example:
WITH EmployeeCTE AS (
SELECT ID, Name, Salary
FROM Employees
)
SELECT * FROM EmployeeCTE;
Benefits:
CTEs are ideal for hierarchical data and complex logic.
Window functions perform calculations across a set of rows related to the current row without collapsing rows like aggregate functions.
Example:
SELECT Name, Salary,
AVG(Salary) OVER (PARTITION BY Department)
FROM Employees;
Key features:
OVER() clauseCommon window functions include:
These are ranking window functions used to assign numbers to rows based on sorting.
Example:
SELECT Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
RANK() OVER (ORDER BY Salary DESC) AS RankNum,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;
These functions are widely used in reporting and pagination.
Partitioning divides data into logical groups within a window function using PARTITION BY.
Example:
SELECT Name, Department, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC)
FROM Employees;
Key points:
Partitioning is essential for advanced analytical queries.
Indexing strategy is the planned approach to creating and managing indexes to balance performance, storage, and maintenance.
Key considerations:
Good indexing strategy:
Indexing strategy is a core responsibility of DBAs and senior developers.
A composite index is an index created on multiple columns of a table.
Example:
CREATE INDEX idx_Emp_Dep_Sal
ON Employees (DepartmentID, Salary);
Key characteristics:
Composite indexes are ideal for frequently used multi-column filters.
Index fragmentation occurs when the logical order of index pages does not match the physical order in which the data is stored on disk. Over time, frequent INSERT, UPDATE, and DELETE operations cause pages to split and data to become scattered.
Types of fragmentation:
Impact:
Fragmentation is commonly monitored using system views like sys.dm_db_index_physical_stats.
Index maintenance is done using REBUILD and REORGANIZE operations.
Index REORGANIZE
Index REBUILD
Example:
ALTER INDEX ALL ON Employees REBUILD;
ALTER INDEX ALL ON Employees REORGANIZE;
Choosing the right method helps maintain optimal query performance.
An execution plan is a roadmap created by the SQL Server query optimizer that shows how a query will be executed.
It includes:
Types:
Execution plans help identify performance bottlenecks and inefficient queries.
FeatureEstimated Execution PlanActual Execution PlanDataUses estimated statisticsUses real runtime dataExecutionQuery is not executedQuery is executedAccuracyApproximatePrecisePerformance impactNo impactMay affect performance
Estimated plans are useful for safe analysis, while actual plans provide real-world insights.
Query optimization is the process of improving query performance by helping SQL Server choose the most efficient execution plan.
Optimization techniques include:
The SQL Server Query Optimizer automatically optimizes queries, but developers can guide it through better design and indexing.
Parameter sniffing occurs when SQL Server creates an execution plan based on the first parameter values passed to a stored procedure and reuses it for subsequent executions.
Problem:
Solutions:
Parameter sniffing is a common cause of intermittent performance issues.
Implicit conversion
Explicit conversion
Example:
CAST(Salary AS VARCHAR)
CONVERT(INT, '123')
Explicit conversion is preferred for performance and clarity.
TRY…CATCH is a structured error handling mechanism in T-SQL that captures runtime errors.
Example:
BEGIN TRY
INSERT INTO Employees VALUES (1, 'Aman');
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
Benefits:
TRY…CATCH improves robustness and reliability of SQL code.
Error handling in T-SQL ensures that errors are detected, logged, and managed without crashing applications.
Techniques include:
Effective error handling:
It is essential in production-grade SQL code.
A cursor is a database object that allows row-by-row processing of query results.
Example:
DECLARE emp_cursor CURSOR FOR
SELECT Name FROM Employees;
Key characteristics:
Cursors should be avoided when possible and replaced with set-based queries, but they are useful in complex row-level logic scenarios.
Cursors should generally be avoided because they process data row by row, whereas SQL Server is optimized for set-based operations.
Disadvantages of cursors:
Cursors are slower because:
Best practice:
Cursors should only be used when row-by-row logic is unavoidable.
Dynamic SQL is SQL code that is constructed and executed at runtime instead of being statically defined.
Example:
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Employees WHERE DepartmentID = 1';
EXEC(@sql);
Use cases:
Risks:
Dynamic SQL should be used carefully and securely.
sp_executesql is a system stored procedure used to execute dynamic SQL with parameters.
Example:
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Employees WHERE DepartmentID = @DeptId';
EXEC sp_executesql
@sql,
N'@DeptId INT',
@DeptId = 2;
Advantages over EXEC:
sp_executesql is the recommended approach for executing dynamic SQL.
SET NOCOUNT ON disables the message that reports the number of rows affected by a T-SQL statement.
Example:
SET NOCOUNT ON;
Benefits:
It is commonly used in:
Using SET NOCOUNT ON is a best practice in production code.
The EXISTS operator checks for the existence of rows returned by a subquery.
Example:
SELECT Name
FROM Employees e
WHERE EXISTS (
SELECT 1 FROM Orders o WHERE o.EmployeeID = e.ID
);
Key points:
EXISTS is often preferred for existence checks.
FeatureEXISTSINEvaluationStops at first matchEvaluates full listPerformanceBetter for large subqueriesSlower for large listsNULL handlingHandles NULL safelyCan behave unexpectedlyUse caseCorrelated subqueriesSmall static lists
Use EXISTS for large or correlated datasets and IN for small, fixed lists.
A trigger is a special type of stored procedure that automatically executes in response to DML or DDL events on a table or database.
Common trigger events:
Triggers are used to:
Triggers operate implicitly, without user invocation.
SQL Server supports several types of triggers:
Triggers should be used carefully due to performance and complexity concerns.
An AFTER trigger executes after the triggering DML operation has completed successfully.
Example:
CREATE TRIGGER trg_AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO AuditLog VALUES ('Insert occurred');
END;
Key characteristics:
AFTER triggers are ideal for auditing and validation.
An INSTEAD OF trigger executes in place of the triggering operation, overriding the default behavior.
Example:
CREATE TRIGGER trg_InsteadOfDelete
ON Employees
INSTEAD OF DELETE
AS
BEGIN
UPDATE Employees SET IsActive = 0 WHERE ID IN (SELECT ID FROM deleted);
END;
Use cases:
INSTEAD OF triggers provide full control over DML behavior.
SQL Server architecture is a layered architecture designed to efficiently process queries, manage memory, ensure concurrency, and guarantee data durability.
At a high level, SQL Server consists of two major layers:
This layer is responsible for query parsing, optimization, and execution.
Key components:
This layer manages data storage, retrieval, transactions, and recovery.
Key components:
Together, these layers ensure performance, reliability, scalability, and data integrity.
System databases are core databases required for SQL Server to function.
Each system database plays a critical operational role, and corruption can impact the entire instance.
TempDB is a shared system database used by all sessions and workloads.
Internally, TempDB stores:
Key internal behaviors:
Best practices:
TempDB performance is often a bottleneck in high-concurrency systems.
SQL Server stores data in data files (.mdf, .ndf) and log files (.ldf).
Storage hierarchy:
Data is stored in 8 KB pages, and pages are grouped into extents. SQL Server reads and writes data only in page units, not rows.
Internally:
This design enables efficient I/O and memory management.
Types of extents:
Pages and extents form the foundation of SQL Server’s storage engine.
Internally, clustered tables provide better read performance, while heaps are sometimes used for staging tables.
Although often confused, latches and locks serve different purposes.
In short:
SQL Server supports multiple lock types to control concurrency:
Locks are dynamically escalated to balance performance and concurrency.
A deadlock occurs when two or more sessions block each other indefinitely, each holding resources the others need.
Example:
SQL Server automatically:
Resolution strategies:
Deadlocks indicate design or concurrency issues.
Isolation level defines how transaction visibility and concurrency are handled.
It controls:
Common isolation levels:
Each level balances data accuracy vs performance. Choosing the right isolation level is critical for high-concurrency systems.
Transaction isolation levels control how and when changes made by one transaction become visible to other transactions. They balance data consistency vs concurrency.
SQL Server supports the following isolation levels:
Choosing the correct isolation level is critical for system scalability and correctness.
Snapshot isolation is an isolation level that provides transaction-level consistency using row versioning instead of locking.
How it works:
Key characteristics:
ALLOW_SNAPSHOT_ISOLATION to be enabledLimitations:
Snapshot isolation is ideal for high-concurrency read-heavy systems.
Row versioning is a concurrency control mechanism where SQL Server stores previous versions of modified rows in TempDB.
Used by:
How it works:
Benefits:
Trade-offs:
Row versioning is a cornerstone of modern SQL Server concurrency management.
Blocking occurs when one session holds a lock that another session needs, causing the second session to wait.
Common causes:
Troubleshooting steps:
sys.dm_exec_requests)Blocking is expected in transactional systems, but excessive blocking indicates design issues.
Wait stats represent the time SQL Server spends waiting for resources rather than doing useful work.
Categories of wait types:
Why wait stats matter:
Wait stats are one of the most reliable performance diagnostic tools in SQL Server.
Performance bottlenecks are identified using a systematic approach, not guesswork.
Key tools and techniques:
Approach:
Effective troubleshooting focuses on root cause, not symptoms.
Query Store is a built-in SQL Server feature that captures query execution history, plans, and runtime statistics over time.
It stores:
Benefits:
Query Store is essential for modern performance monitoring and tuning.
Query Store helps by providing historical visibility into query performance.
Key advantages:
Use cases:
Query Store transforms tuning from reactive troubleshooting to proactive optimization.
Plan cache stores compiled execution plans so SQL Server can reuse them without recompiling queries.
Benefits:
Plans are cached for:
Plan reuse is critical for high-throughput systems.
Plan cache bloat occurs when the cache is filled with too many single-use or inefficient plans.
Common causes:
Consequences:
Mitigation strategies:
Plan cache health is crucial for predictable SQL Server performance.
Recompilation occurs when SQL Server discards an existing execution plan and generates a new plan for a query or stored procedure. While compilation is expensive, recompilation is sometimes necessary to ensure optimal performance.
Common reasons for recompilation:
Impact:
Recompilation is a trade-off between plan accuracy and CPU cost. It should be controlled, not eliminated entirely.
An index seek occurs when SQL Server can efficiently navigate an index to retrieve specific rows.
An index scan occurs when SQL Server reads all or most of the index.
Index seeks are preferred, but index scans are not always bad—especially for analytical queries.
A covering index is a non-clustered index that includes all columns needed by a query, allowing SQL Server to satisfy the query without accessing the base table.
Example:
CREATE NONCLUSTERED INDEX idx_cover
ON Orders (CustomerID)
INCLUDE (OrderDate, Amount);
Benefits:
Covering indexes are especially valuable in high-frequency OLTP queries.
OLTP systems focus on high-volume, short transactions.
Index design principles:
Focus:
Index design for OLTP is about balance between read speed and write cost.
OLAP systems focus on large-scale reporting and analysis.
Index design principles:
Focus:
OLAP indexing prioritizes analytical efficiency over transactional speed.
A partitioned table divides a large table into smaller, manageable partitions based on a partition key, while still appearing as a single logical table.
Benefits:
Partitioning is commonly used for very large tables.
Internally, table partitioning uses:
Execution behavior:
Partitioning does not automatically improve performance—it must be used strategically.
SQL Server Agent is a job scheduling and automation service.
Used for:
Components:
SQL Server Agent is essential for automated database operations in production environments.
SQL Server supports several backup types to protect data:
A proper backup strategy balances recovery time and storage usage.
Backup TypeWhat it ContainsRecovery UsageFullEntire databaseBaseline restoreDifferentialChanges since last fullFaster restoreLogAll transactions since last log backupPoint-in-time recovery
Restore sequence:
A well-designed backup strategy ensures minimal data loss and fast recovery.
Point-in-time recovery is a data recovery technique that allows a database to be restored to a specific moment in time, typically just before a failure, data corruption, or accidental data modification occurred.
How it works:
Use cases:
Example workflow:
Point-in-time recovery minimizes data loss (RPO) and is critical for mission-critical systems.
High Availability (HA) and Disaster Recovery (DR) are related but serve different purposes.
High Availability
Disaster Recovery
Key difference:
A complete strategy often includes both HA and DR.
Always On Availability Groups (AGs) provide high availability and disaster recovery at the database level.
Key concepts:
Features:
AGs are widely used for enterprise-grade availability and scalability.
Database mirroring is a deprecated high availability feature that maintains a hot standby copy of a database.
Components:
Modes:
Limitations:
Mirroring is still seen in legacy systems.
Replication is a data distribution technology that copies data from one database to another.
Main types:
Replication is used for data distribution, reporting, and scale-out scenarios.
Log shipping is a disaster recovery solution that involves automatically backing up transaction logs from a primary database and restoring them on a secondary server.
Components:
Process:
Characteristics:
Log shipping is ideal for cost-effective DR solutions.
SQL Server security follows a layered architecture:
Security is enforced at server, database, and object levels.
Types of roles:
This separation ensures least-privilege access and scalability.
Sensitive data is protected using multiple techniques:
Security should be implemented defense-in-depth, not relying on a single feature.
Production troubleshooting requires a methodical, low-impact approach.
Steps:
Key principles:
Effective troubleshooting is a core skill of senior SQL Server professionals.
