Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Performance Optimization

Learn how to improve SQL Server query performance, reduce execution time and optimize database operations.

What is Performance Optimization?

Performance Optimization is the process of improving SQL Server queries, indexes, database design and execution plans so that queries execute faster and use fewer system resources.

Why is SQL Performance Important?

A poorly optimized SQL query can take a long time to execute and can consume excessive CPU, memory and disk resources.

Proper optimization helps applications respond faster and allows SQL Server to handle more users and requests.

  • Faster query execution
  • Reduced CPU usage
  • Reduced disk I/O
  • Better application response time
  • Improved scalability
  • Efficient use of database resources

1. Use Proper Indexes

Indexes help SQL Server find rows quickly without scanning the entire table.

CREATE INDEX IX_Employees_Department
ON Employees(Department);

If a query frequently searches employees by Department, an index on the Department column can improve performance.

2. Avoid SELECT *

Avoid selecting unnecessary columns. Retrieve only the columns required by the application.

Not Recommended
SELECT *
FROM Employees;
Recommended
SELECT EmployeeId, EmployeeName, Department
FROM Employees;

3. Use WHERE Conditions Properly

Use appropriate filtering conditions so that SQL Server does not process unnecessary rows.

SELECT EmployeeId, EmployeeName
FROM Employees
WHERE Department = 'IT';

Filtering data as early as possible can reduce the amount of data SQL Server needs to process.

4. Avoid Functions on Indexed Columns

Applying functions directly to indexed columns can prevent SQL Server from efficiently using an index.

Example
SELECT *
FROM Employees
WHERE YEAR(HireDate) = 2025;

Depending on the query and indexes, this can make it harder for SQL Server to perform an efficient index seek.

Better Approach
SELECT *
FROM Employees
WHERE HireDate >= '2025-01-01'
  AND HireDate < '2026-01-01';

5. Use Execution Plans

An execution plan shows how SQL Server executes a query. It helps developers identify performance problems.

Execution plans can show operations such as:

  • Index Seek
  • Index Scan
  • Table Scan
  • Key Lookup
  • Sort
  • Hash Match
  • Nested Loops
Tip: In SQL Server Management Studio, enable the Actual Execution Plan using Ctrl + M.

6. Avoid Unnecessary JOINs

Joining unnecessary tables can increase query complexity and execution time.

SELECT 
    e.EmployeeName,
    d.DepartmentName
FROM Employees e
INNER JOIN Departments d
    ON e.DepartmentId = d.DepartmentId;

Only join tables that are actually required by the query.

7. Optimize JOIN Conditions

Make sure the columns used in JOIN conditions are properly indexed when appropriate.

SELECT
    e.EmployeeName,
    d.DepartmentName
FROM Employees e
JOIN Departments d
    ON e.DepartmentId = d.DepartmentId;

An appropriate index on DepartmentId can help SQL Server perform the join efficiently.

8. Use Stored Procedures Appropriately

Stored procedures can encapsulate database operations and provide a reusable way to execute commonly used logic.

CREATE PROCEDURE GetEmployeesByDepartment
    @Department VARCHAR(50)
AS
BEGIN

    SELECT EmployeeId,
           EmployeeName,
           Department
    FROM Employees
    WHERE Department = @Department;

END;

9. Maintain Statistics

SQL Server uses statistics to estimate how many rows a query will return. Updated statistics help the optimizer choose better execution plans.

UPDATE STATISTICS Employees;

10. Index Maintenance

Indexes can become fragmented over time. Index maintenance may help maintain efficient data access.

ALTER INDEX IX_Employees_Department
ON Employees
REORGANIZE;

For heavily fragmented indexes, rebuilding may be appropriate depending on the database environment and maintenance plan.

ALTER INDEX IX_Employees_Department
ON Employees
REBUILD;

SQL Server Performance Best Practices

Practice Benefit
Use proper indexes Faster data retrieval
Avoid SELECT * Reduces unnecessary data transfer
Use proper WHERE conditions Reduces rows processed
Check execution plans Helps identify bottlenecks
Maintain indexes Improves index efficiency
Update statistics Helps query optimizer
Avoid unnecessary JOINs Reduces query complexity

Important Points

  • Always analyze slow queries before changing indexes.
  • Too many indexes can slow down INSERT, UPDATE and DELETE operations.
  • Use execution plans to understand query behavior.
  • Keep statistics and indexes properly maintained.
  • Test optimization changes before applying them to production.

Summary

SQL Server Performance Optimization focuses on improving query execution speed and reducing unnecessary resource usage.

Proper indexing, optimized queries, execution plans, statistics maintenance, index maintenance and efficient database design are important for achieving good performance.

Model 19 Completed

You have completed the SQL Server Advanced SQL module: Advanced SQL Introduction, Temporary Tables, Table Variables, Dynamic SQL, Error Handling and Performance Optimization.