SQL index

In SQL, an index is a database object that improves the speed of data retrieval operations on a table. Indexes are created on columns to help locate data without searching every row in a table, similar to an index in a book. While they speed up read operations, indexes can slow down write operations like INSERT, UPDATE, and DELETE since the index also needs to be updated.

Basic Syntax:

CREATE  CLUSTERED INDEX  index_name

ON  table_name (column_name);

Types of Indexes in SQL:

1. Clustered Index:

• Sorts and stores the data rows in the table or view based on the key values of the index.
• A table can have only one clustered index since the data rows can only be sorted in one order.
• The physical order of the rows matches the index order.

2. Non-Clustered Index:

   • Creates a separate structure from the data rows, with pointers back to the original rows.
   • A table can have multiple non-clustered indexes.
   • The physical order of the data in the table does not change.

3. Unique Index:

   • Ensures that all values in the indexed column are unique.
   • It is either clustered or non-clustered, but the unique constraint guarantees uniqueness.

4. Composite Index:

   • An index on multiple columns.
   • Useful for queries that filter on multiple columns simultaneously.

5. Full-Text Index:

   • Used for full-text searches in SQL Server, enabling efficient search of text-based data.
   • It allows searching large text fields for words or phrases.

6. Filtered Index:

   • A non-clustered index that includes rows that meet a certain condition helps reduce the index's size and improve query performance for specific queries.

7. Bitmap Index:

   • Primarily used in data warehouses or environments with read-heavy workloads.
   • Stores the index in the form of bitmaps and is efficient for columns with low cardinality (few unique values).

8. XML Index:

   • An index specifically designed to optimize XML data retrieval in SQL Server.

When and Why to Use an Index:

1. Frequent Search on a Specific Column (e.g., EmployeeID)

If you often run queries like:

SELECT * FROM Employee WHERE EmployeeID = 103;

Here, you are searching by EmployeeID. To speed up this search, you can create a Clustered Index on the EmployeeID column. This would allow the database to directly jump to the correct row without scanning every row.

Clustered Index:

CREATE  CLUSTERED INDEX  IX_EmployeeID 

ON  Employee(EmployeeID);

Now, when you search for a specific EmployeeID, the query performance improves because the data is physically sorted based on this column.

Why?

When you know that a particular column will be used frequently in searches (e.g., EmployeeID), a Clustered Index is ideal because it sorts the data in the table itself.

2. Filtering or Sorting Data by a Column (e.g., Department)

If you frequently run queries like:

SELECT  *  FROM  Employee  WHERE  Department = 'IT';

Non-Clustered Index:

CREATE  NONCLUSTERED  INDEX IX_Department 

ON  Employee(Department);

3. Prevent Duplicate Values (e.g., Email or National ID)

If you have a column like Email that should have unique values, and you're frequently searching by it:

SELECT * FROM Employee WHERE Email = 'jane.smith@example.com';

You can create a Unique Index on the Email column to ensure all values are unique and to optimize searches on this column.

Unique Index:

CREATE  UNIQUE  INDEX IX_Email 

ON Employee(Email);

This ensures that no duplicate email addresses are inserted and also speeds up searches by Email.

Why?

Use a Unique Index when a column requires uniqueness and you frequently search by that column (like Email or NationalID).

4. Composite Index for Multiple Columns

If you often search based on multiple columns together, like:

SELECT * FROM Employee WHERE Department = 'HR' AND HireDate > '2020-01-01';

You can create a Composite Index on both Department and HireDate to optimize this kind of query.

Composite Index:

CREATE  NONCLUSTERED  INDEX IX_Dept_HireDate 

ON  Employee(Department, HireDate);

This index is helpful when you frequently run queries that filter or sort by multiple columns together.

Summary:

Use indexes when you need to optimize search, filter, or sort operations on large tables.

Clustered indexes are best for primary keys or columns used for frequent lookups.

Non-clustered indexes work well for columns used in filters and sorts.

Unique indexes ensure data uniqueness and improve search performance on unique columns.

Composite indexes optimize queries on multiple columns.

Benefits of Indexes:

• Improves query performance by reducing the number of rows scanned.
• Enhances searching, sorting, and filtering operations.

Drawbacks of Indexes:

• Takes up additional disk space.
• Slows down data modification operations like INSERT, UPDATE, and DELETE.

Indexes are crucial for optimizing database performance, but they should be used wisely, considering both the read and write performance impacts.