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

Index Performance

Learn how indexes improve query performance and how to maintain them effectively in SQL Server.

What is Index Performance?

Index performance refers to how effectively indexes help SQL Server find and retrieve data from a table.

Properly designed indexes can significantly improve SELECT query performance.

However, unnecessary or poorly designed indexes can increase storage usage and slow down INSERT, UPDATE and DELETE operations.

Why Do Indexes Improve Performance?

Without a suitable index, SQL Server may need to examine many or all rows in a table to find the required data.

This operation is commonly known as a Table Scan.

With a suitable index, SQL Server may be able to locate the required rows more efficiently.

Table Scan

A table scan occurs when SQL Server reads rows throughout a table to find matching records.

SELECT *
FROM Students
WHERE City = 'Pune';

If there is no useful index on the City column, SQL Server may need to scan a large portion of the table.

Index Seek

An Index Seek allows SQL Server to navigate directly to the relevant portion of an index instead of examining every row.

Index seeks are generally efficient when a suitable index exists and the query can use it effectively.

Example of Index Performance

Suppose we have the following table:

CREATE TABLE Students
(
    StudentId INT,
    StudentName VARCHAR(100),
    Course VARCHAR(100),
    City VARCHAR(50),
    Age INT
);

Create an index on City:

CREATE NONCLUSTERED INDEX IX_Students_City
ON Students(City);

Now execute:

SELECT *
FROM Students
WHERE City = 'Pune';

SQL Server can consider the City index when creating the execution plan for this query.

Execution Plan

SQL Server uses an Execution Plan to determine how a query should be executed.

The execution plan can show whether SQL Server is using operations such as:

  • Index Seek
  • Index Scan
  • Table Scan
  • Key Lookup

Viewing Execution Plan in SSMS

In SQL Server Management Studio (SSMS), you can enable the Actual Execution Plan before running a query.

Keyboard shortcut:

Ctrl + M

After enabling it, execute the query and open the Execution Plan tab to analyze how SQL Server processed the query.

Index Seek vs Index Scan

Index Seek Index Scan
Searches specific portions of an index. Reads a larger portion or the entire index.
Usually efficient for selective queries. Can be useful when many rows are required.
Typically accesses fewer rows. May process many rows.

Index Selectivity

Selectivity describes how effectively an index can distinguish between rows.

Columns with many distinct values often have higher selectivity than columns with only a few distinct values.

For example, a StudentId column usually has better selectivity than a Gender column.

Choosing Columns for Indexes

Good candidates for indexes are columns frequently used in:

  • WHERE conditions
  • JOIN conditions
  • ORDER BY
  • GROUP BY

However, indexes should be created based on actual query and workload requirements.

Covering Index

A covering index contains all the columns required by a query, allowing SQL Server to retrieve the required information directly from the index.

CREATE NONCLUSTERED INDEX IX_Students_City
ON Students(City)
INCLUDE(StudentName, Course);

In this example, City is the index key while StudentName and Course are included columns.

Problem with Too Many Indexes

Creating an index for every column is not recommended.

Each additional index requires storage and may need maintenance whenever data changes.

  • More storage usage
  • Slower INSERT operations
  • Additional UPDATE maintenance
  • Additional DELETE maintenance

Index Fragmentation

Index fragmentation can occur as rows are inserted, updated and deleted over time.

Fragmentation can affect the efficiency of index access, especially in heavily modified tables.

Checking Index Fragmentation

SQL Server provides sys.dm_db_index_physical_stats to inspect index physical statistics.

SELECT
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
);

Rebuilding an Index

An index can be rebuilt to reorganize its structure.

ALTER INDEX IX_Students_City
ON Students
REBUILD;

Rebuilding an index can be useful when significant fragmentation needs to be addressed.

Reorganizing an Index

SQL Server also supports index reorganization.

ALTER INDEX IX_Students_City
ON Students
REORGANIZE;

Reorganize is an online operation that incrementally defragments an index.

REBUILD vs REORGANIZE

REBUILD REORGANIZE
Recreates the index structure. Defragments the existing index incrementally.
Can be more resource intensive. Generally less resource intensive.
Useful when substantial maintenance is required. Useful for lighter fragmentation.

Index Performance Best Practices

  1. Create indexes based on actual query requirements.
  2. Avoid creating unnecessary indexes.
  3. Monitor query performance regularly.
  4. Review execution plans for important queries.
  5. Monitor index fragmentation where appropriate.
  6. Maintain indexes according to workload requirements.
  7. Consider covering indexes for frequently executed queries.

Important Points

  • Indexes can improve SELECT query performance.
  • Index Seek is generally efficient for selective searches.
  • Table Scan may occur when a suitable index is unavailable.
  • Too many indexes can negatively affect write operations.
  • Execution Plans help analyze query performance.
  • Index fragmentation should be monitored when appropriate.
  • REBUILD and REORGANIZE are available for index maintenance.

Summary

Indexes are an important part of SQL Server performance optimization. A properly designed index can reduce the amount of data SQL Server needs to examine when executing queries.

Execution plans can be used to understand how SQL Server accesses data and whether operations such as Index Seek, Index Scan or Table Scan are being used.

Indexes should be created and maintained carefully because they require storage and can increase the cost of data modification operations.