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

Indexes Introduction

What is an Index?

An Index in SQL Server is a database object used to improve the speed of data retrieval operations. Indexes help SQL Server find records faster without scanning the entire table.

An index works similar to an index in a book. Instead of checking every page to find a particular topic, we can use the index to quickly locate the required information.

Why are Indexes Used?

Indexes are mainly used to improve the performance of SELECT queries and make data retrieval faster.

  • Indexes improve query performance.
  • They help SQL Server find records quickly.
  • They reduce the amount of data SQL Server needs to scan.
  • Indexes are useful for columns frequently used in WHERE clauses.
  • They can improve JOIN and ORDER BY operations.
  • Indexes can also improve searching and sorting operations.

Simple Example

Suppose we have a Students table:

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

Suppose the table contains thousands of student records and we frequently search students based on the City column.

SELECT *
FROM Students
WHERE City = 'Pune';

Creating an index on the City column can help SQL Server find the required records more efficiently.

Creating an Index

A non-clustered index can be created using the CREATE INDEX statement.

CREATE INDEX IX_Students_City
ON Students(City);

Here:

  • IX_Students_City is the name of the index.
  • Students is the table name.
  • City is the column on which the index is created.

Types of Indexes

SQL Server supports different types of indexes. The commonly used types are:

  • Clustered Index
  • Non-Clustered Index

In the upcoming topics, we will learn about both clustered and non-clustered indexes in detail.

Clustered Index

A Clustered Index determines the physical order of data in a table.

A table can have only one clustered index because the physical data can be stored in only one order.

CREATE CLUSTERED INDEX IX_Students_StudentId
ON Students(StudentId);

Non-Clustered Index

A Non-Clustered Index is a separate structure that stores indexed column values along with references to the actual rows in the table.

A table can have multiple non-clustered indexes.

CREATE NONCLUSTERED INDEX IX_Students_City
ON Students(City);

Index and SELECT Query

Consider the following query:

SELECT StudentId,
       StudentName,
       City,
       Age
FROM Students
WHERE City = 'Pune';

If an appropriate index exists on the City column, SQL Server may use the index to locate matching records more efficiently.

Important Points

  • Indexes are mainly used to improve data retrieval performance.
  • Indexes are useful for frequently searched columns.
  • A table can have only one clustered index.
  • A table can have multiple non-clustered indexes.
  • Indexes require additional storage space.
  • Too many indexes can affect INSERT, UPDATE and DELETE performance.

When Should We Use Indexes?

Indexes should be considered when a table contains a large amount of data and specific columns are frequently used for searching, filtering, joining or sorting.

  • Columns used frequently in WHERE conditions.
  • Columns frequently used in JOIN conditions.
  • Columns frequently used for sorting.
  • Columns frequently used for searching.
Remember

Indexes can significantly improve SELECT query performance, but they also require storage and can increase the cost of INSERT, UPDATE and DELETE operations. Therefore, indexes should be created carefully.

Summary

Indexes are database objects used to improve the performance of data retrieval operations in SQL Server. They help SQL Server find records faster instead of scanning the entire table.

The two commonly used types of indexes are Clustered Index and Non-Clustered Index. A table can have one clustered index and can have multiple non-clustered indexes.

In the next topic, we will learn about Clustered Index in detail.