What is an index in a database? What types of indexes are there (clustered and non-clustered)? How do they differ? How many clustered and non-clustered indexes can be on a table?

An index in a database is a data structure that speeds up the search and sorting of records in a table, allowing quick access to the needed rows without scanning the entire table.

Types of indexes:

• Clustered Index: determines the physical order of data in the table. Usually, a table can have only one clustered index because data can be ordered in only one way. Queries using a clustered index retrieve data faster because they are physically stored in the required order.

• Non-Clustered Index: a separate structure that contains index keys and pointers to the physical data rows. A table can have multiple non-clustered indexes. They do not change the physical order of data but serve for quick searching.

Differences:

Characteristic	Clustered Index	Non-Clustered Index
Physical order	Determines the physical order	Does not affect the physical order
Number per table	Usually 1	Many (depends on the DBMS)
Structure	Data organized as an index	Separate structure with pointers

Number of indexes:

• Clustered index: 1 per table
• Non-clustered indexes: depends on the DBMS, usually from several to dozens

Example of creating indexes in SQL Server:

-- Creating a clustered index
CREATE CLUSTERED INDEX idx_clustered ON TableName(ColumnName);

-- Creating a non-clustered index
CREATE NONCLUSTERED INDEX idx_nonclustered ON TableName(OtherColumn);