sql index types code example

Example 1: what are all the different types of indexes in sql

"There are two types of Indexes in SQL Server

1.	Clustered Index

A clustered index defines the order in which data 
is physically stored in a table. Table data can 
be sorted in only way, therefore, there can be only 
one clustered index per table. In SQL Server, 
the primary key constraint automatically creates a 
clustered index on that particular column.

2.	Non-Clustered Index

A non-clustered index doesn’t sort the physical 
data inside the table. In fact, a non-clustered 
index is stored at one place and table data is 
stored in another place. This is similar to a 
textbook where the book content is located in o
ne place and the index is located in another. 
This allows for more than one non-clustered index per table."

Example 2: sql indexes

CREATE INDEX
Creates an index named ‘idx_test’ on the first_name and surname columns of
the users table. In this instance, duplicate values are allowed.
CREATE INDEX idx_test
ON users (first_name, surname);
CREATE UNIQUE INDEX
Creates an index named ‘idx_test’ on the first_name and surname columns of
the users table. In this instance, duplicate values are allowed.
CREATE UNIQUE INDEX idx_test
ON users (first_name, surname);
DROP INDEX
Creates an index named ‘idx_test’ on the first_name and surname columns of
the users table. In this instance, duplicate values are allowed.
ALTER TABLE users
DROP INDEX idx_test;