In SQL Server, you can create both clustered and non-clustered indexes using the CREATE INDEX statement. The syntax for creating clustered and non-clustered indexes is slightly different. Below are examples of how to create both types of indexes in SQL Server:
When you create a PRIMARY KEY constraint, a clustered index on the column is automatically created.Creating a Clustered Index:
A clustered index determines the physical order of data in the table. Only one clustered index can be created per table because the data rows are physically organized based on the clustered index key.
Syntax for creating a clustered index in SQL Server:
CREATE CLUSTERED INDEX index_name
ON table_name (column1, column2, ...);
Example:
Let's say we have a table named Employees, and we want to create a clustered index on the EmployeeID column:
CREATE CLUSTERED INDEX idx_Employees_EmployeeID
ON Employees (EmployeeID);
A non-clustered index creates a separate structure that points to the data rows in the table. Unlike a clustered index, you can have multiple non-clustered indexes on a single table.
Syntax for creating a non-clustered index in SQL Server:
CREATE NONCLUSTERED INDEX index_name
ON table_name (column1, column2, ...);
Example:
Let's say we have a table named Customers, and we want to create a non-clustered index on the LastName column:
CREATE NONCLUSTERED INDEX idx_Customers_LastName
ON Customers (LastName);
In both examples, replace index_name with the desired name for the index, table_name with the name of the table on which you want to create the index, and (column1, column2, ...) with the columns you want to include in the index. For a non-clustered index, SQL Server creates a separate structure to store the index, whereas for a clustered index, the data rows themselves are organized based on the index key.
Remember that creating indexes should be based on the specific needs of your queries and workload. It's essential to consider the frequency and nature of data retrieval operations to determine which columns would benefit from indexes. Indexing too many columns or over-indexing can impact performance during data modification operations, so it's essential to strike a balance between query performance and data modification efficiency.
