Whether you're a beginner or an experienced SQL Server developer, writing optimized SQL queries and stored procedures is essential for building high-performance applications. Poorly written queries can lead to slow response times, high CPU usage, excessive memory consumption, and unnecessary disk I/O.
In this article, we'll explore practical techniques to optimize SQL queries and stored procedures, helping you improve database performance and prepare for SQL Server interviews.
Why SQL Query Optimization Matters
As applications grow, databases often become the biggest performance bottleneck. Optimized SQL queries provide several benefits:
Faster query execution
Reduced CPU and memory usage
Lower disk I/O
Better scalability
Improved user experience
Reduced server costs
Let's look at some proven optimization techniques.
1. Create Proper Indexes
Indexes help SQL Server locate data quickly without scanning the entire table.
Before (Table Scan)
SELECT *
FROM Employees
WHERE Department = 'IT';
If the Department column is not indexed, SQL Server performs a table scan.
Create an Index
CREATE INDEX IX_Employees_Department
ON Employees(Department);
After creating the index, SQL Server can perform an Index Seek, which is significantly faster.
2. Avoid Using SELECT *
Although SELECT * is convenient, it retrieves every column from the table—even when you don't need them.
Avoid
SELECT *
FROM Employees;
Use
SELECT EmployeeId, Name, Salary
FROM Employees;
Benefits
Reduces network traffic
Improves query performance
Lowers memory usage
3. Filter Data with WHERE Clause
Always retrieve only the rows you actually need.
Avoid
SELECT *
FROM Orders;
Use
SELECT OrderId, CustomerId
FROM Orders
WHERE OrderDate >= '2026-01-01';
Filtering reduces unnecessary data processing.
4. Don't Use Functions in WHERE Clauses
Applying functions on indexed columns prevents SQL Server from using indexes efficiently.
Avoid
SELECT *
FROM Employees
WHERE YEAR(HireDate) = 2025;
Use
SELECT *
FROM Employees
WHERE HireDate >= '2025-01-01'
AND HireDate < '2026-01-01';
This allows SQL Server to utilize indexes effectively.
5. Prefer EXISTS Over IN
For large datasets, EXISTS often performs better than IN.
Instead of
SELECT *
FROM Customers
WHERE CustomerId IN
(
SELECT CustomerId
FROM Orders
);
Use
SELECT *
FROM Customers C
WHERE EXISTS
(
SELECT 1
FROM Orders O
WHERE O.CustomerId = C.CustomerId
);
6. Use JOINs Instead of Nested Subqueries
JOINs are generally more efficient and easier to read.
SELECT E.Name
FROM Employees E
INNER JOIN Departments D
ON E.DepartmentId = D.DepartmentId
WHERE D.DepartmentName = 'IT';
7. Choose Appropriate Data Types
Selecting the correct data type reduces storage requirements and improves performance.
Avoid
Salary VARCHAR(100)
Use
Salary DECIMAL(18,2)
Always use the smallest suitable data type.
8. Avoid Cursors
Cursors process rows one at a time, making them slow for large datasets.
Avoid
DECLARE EmployeeCursor CURSOR
FOR
SELECT EmployeeId
FROM Employees;
Use Set-Based Operations
UPDATE Employees
SET Salary = Salary + 1000
WHERE Department = 'IT';
Set-based operations are much faster and more scalable.
9. Retrieve Only Required Rows
Instead of loading an entire table:
SELECT *
FROM Employees;
Retrieve only what you need.
SELECT TOP 10 *
FROM Employees;
10. Implement Pagination
For applications displaying large datasets, pagination improves performance.
SELECT *
FROM Employees
ORDER BY EmployeeId
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;
This is commonly used in web applications with paging.
11. Avoid Leading Wildcards
Avoid
WHERE Name LIKE '%John';
Use
WHERE Name LIKE 'John%';
Leading wildcards prevent SQL Server from efficiently using indexes.
12. Avoid DISTINCT When Unnecessary
DISTINCT requires SQL Server to eliminate duplicate rows, which adds extra processing.
Only use it when duplicates actually exist.
13. Prefer UNION ALL Over UNION
UNION removes duplicate rows, requiring additional sorting.
SELECT Name FROM A
UNION ALL
SELECT Name FROM B;
Use UNION ALL whenever duplicate removal isn't required.
14. Use Parameterized Stored Procedures
Avoid dynamic SQL whenever possible.
CREATE PROCEDURE GetEmployee
@EmployeeId INT
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeId = @EmployeeId;
END
Benefits include:
Better security
Reusable execution plans
Improved performance
15. Minimize Dynamic SQL
Dynamic SQL increases complexity and can affect execution plan reuse.
Prefer static SQL unless dynamic behavior is absolutely necessary.
16. Keep Transactions Short
Long-running transactions hold locks longer, increasing blocking.
Good Example
BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary + 1000;
COMMIT;
Complete transactions as quickly as possible.
17. Use SET NOCOUNT ON
Add this to the beginning of stored procedures.
SET NOCOUNT ON;
It prevents unnecessary row count messages from being returned to the client, reducing network traffic.
18. Avoid Nested Loops in T-SQL
Instead of processing records row by row, use joins.
SELECT *
FROM Employees E
JOIN Departments D
ON E.DepartmentId = D.DepartmentId;
Set-based operations are almost always more efficient.
19. Analyze the Execution Plan
SQL Server's Execution Plan helps identify performance bottlenecks.
Look for:
Table Scan
Index Scan
Index Seek
Key Lookup
Missing Index suggestions
Expensive operators
Understanding execution plans is one of the most valuable SQL optimization skills.
20. Keep Statistics Updated
Statistics help SQL Server choose the best execution plan.
EXEC sp_updatestats;
Or update a specific table.
UPDATE STATISTICS Employees;
21. Rebuild or Reorganize Indexes
Over time, indexes become fragmented.
Rebuild
ALTER INDEX ALL
ON Employees
REBUILD;
Reorganize
ALTER INDEX ALL
ON Employees
REORGANIZE;
Regular index maintenance improves query performance.
22. Best Practices for Stored Procedures
When writing stored procedures:
Use parameters
Return only required columns
Avoid unnecessary business logic
Keep procedures focused
Use proper indexing
Use
SET NOCOUNT ONWrite readable and maintainable SQL
Example:
CREATE PROCEDURE GetEmployees
@Department VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT EmployeeId,
Name,
Salary
FROM Employees
WHERE Department = @Department;
END
Common SQL Performance Interview Questions
Q: What is the difference between a Clustered Index and a Non-Clustered Index?
A Clustered Index determines the physical order of data in a table, while a Non-Clustered Index stores key values separately with pointers to the actual data.
Q: What is an Execution Plan?
An Execution Plan shows how SQL Server executes a query and helps identify performance bottlenecks.
Q: What is the difference between an Index Seek and a Table Scan?
An Index Seek uses an index to locate data efficiently, whereas a Table Scan reads every row in the table.
*Q: Why should you avoid SELECT ?
Because it retrieves unnecessary columns, increasing I/O, memory usage, and network traffic.
Q: Why use SET NOCOUNT ON?
It suppresses row count messages, reducing unnecessary network communication.
Q: How do you identify slow queries?
Use:
Execution Plans
SET STATISTICS IO ONSET STATISTICS TIME ONQuery Store
SQL Server Dynamic Management Views (DMVs)
Final Thoughts
SQL optimization is not just about making queries faster—it's about designing efficient, scalable, and maintainable database solutions. By following these best practices, you can significantly improve application performance, reduce resource consumption, and build reliable enterprise applications.
Whether you're preparing for SQL Server interviews or working on production systems, mastering these optimization techniques will make you a more effective database developer.
If you found this article helpful, share it with your fellow developers and follow the blog for more tutorials on SQL Server, Azure DevOps, .NET, C#, Angular, and Full Stack Development.
