Understanding SQL Server and PostgreSQL Indexes with Examples

1. Clustered Index

A clustered index sorts and stores the rows of data in a table based on the key values. Each table can have only one clustered index.

Syntax:

CREATE CLUSTERED INDEX index_name ON table_name (column1, column2, ...);

Example:

CREATE CLUSTERED INDEX IX_Customers ON Customers (CustomerID);

This creates a clustered index on the CustomerID column of the Customers table, improving the performance of queries that filter by CustomerID.

2. Non-Clustered Index

Unlike clustered indexes, non-clustered indexes store a copy of the indexed column data with pointers to the actual data in the table.

Syntax:

CREATE NONCLUSTERED INDEX index_name ON table_name (column1, column2, ...);

Example:

CREATE NONCLUSTERED INDEX IX_Customers_LastName ON Customers (LastName);

This index will help optimize queries searching for LastName in the Customers table.

3. Unique Index

A unique index ensures that all values in the indexed column are distinct.

Syntax:

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Example:

CREATE UNIQUE INDEX IX_Customers_Email ON Customers (Email);

This ensures that no two customers can have the same email address.

4. Full-Text Index

Full-text indexes in SQL Server enable advanced search capabilities for string data.

Syntax:

CREATE FULLTEXT INDEX ON table_name (column1, column2, ...) KEY INDEX PK_IndexName;

Example:

CREATE FULLTEXT INDEX ON Documents (Title, Body) KEY INDEX PK_DocumentID;

This index enables full-text search on the Title and Body columns of the Documents table.

1. B-Tree Index

The default index type in PostgreSQL, suitable for most equality and range queries.

Syntax:

CREATE INDEX index_name ON table_name (column1, column2, ...);

Example:

CREATE INDEX idx_customers_name ON customers (name);

2. Hash Index

Hash indexes are used for equality comparisons, but are less common than B-Tree indexes in PostgreSQL.

Syntax:

CREATE INDEX index_name ON table_name USING HASH (column);

Example:

CREATE INDEX idx_customers_hash ON customers USING HASH (customer_id);

3. Unique Index

Ensures the uniqueness of the indexed column.

Syntax:

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Example:

CREATE UNIQUE INDEX idx_unique_email ON customers (email);

4. GIN Index

Generalized Inverted Index (GIN) is used for indexing arrays, JSON, and full-text search.

Syntax:

CREATE INDEX index_name ON table_name USING GIN (column);

Example:

CREATE INDEX idx_products_tags ON products USING GIN (tags);

5. GiST Index

Generalized Search Tree (GiST) indexes support various query types, including full-text search and spatial data.

Syntax:

CREATE INDEX index_name ON table_name USING GIST (column);

Example:

CREATE INDEX idx_location_gist ON locations USING GIST (coordinates);

6. BRIN Index

Block Range INdex (BRIN) is useful for very large tables where creating traditional indexes is inefficient. It works well with ordered data.

Syntax:

CREATE INDEX index_name ON table_name USING BRIN (column);

Example:

CREATE INDEX idx_logs_time_brin ON logs USING BRIN (timestamp);