SQL CREATE INDEX Statement
This tutorial will explain about the INDEX in SQL and how to use them with the examples.
Indexes are used to improve performance and also normalize the data. Having correct indexes on the tables would speed up query execution.
One of the downside of having indexes on the table, it would increase the insert/update record duration. Therefore it is important we define proper indexes (depending on the table schema).
Lets create a sample table work on it
CREATE TABLE Users (
UserID INT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(50) NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY (UserID)
);
Lets create the index on FirstName column
CREATE INDEX IX_FirstName ON Users (FirstName);
This will improve when you search on FirstName column
Lets create the index on FirstName column
CREATE UNIQUE INDEX IX_Email ON Users (Email);
This will improve when you search on Email column and prevents entering duplicate value
Lets create the index on FirstName and LastName column
CREATE INDEX IX_FirstNameLastName ON Users (FirstName, LastName);
This will improve when you search on FirstName and LastName columns together
DROP INDEX Statement
The following statement will remove the index from the table
DROP INDEX IX_FirstNameLastName ON Users;