SQL

How To Create Index Using Query In SQL

In this article, we will learn how to create and delete Indexes in SQL.

To create indexes in tables, the CREATE INDEX statement is used.

The users cannot see the indexes, they are just used to retrieve data from the database more quickly to speed up searches/queries.

Note: Updating a table with indexes takes more time because the indexes also need an update. So, only create indexes on columns that will be searched frequently against.

 

CREATE INDEX:

The CREATE INDEX statement is used to create an index on a table. (Duplicate values are allowed)

Syntax

CREATE INDEX INDEX_NAME 
ON TABLE_NAME (column1, column2, ...columnN);

Example

The subsequent statement would create an index named “IDXTitle ” on the “Title” column in the “Article” table (It allows duplicate values):

CREATE INDEX IDXTitle 
ON Article (Title);

 

CREATE UNIQUE INDEX:

The CREATE UNIQUE INDEX statement is used to create a unique index on a table. (Duplicate values are not allowed)

Syntax

CREATE UNIQUE INDEX INDEX_NAME 
ON TABLE_NAME (column1, column2, ...columnN);

Example

The subsequent statement would create an index named “IDXTitle” on the “Title” column in the “Article” table (It doesn’t allow duplicate values):

CREATE UNIQUE INDEX IDXTitle 
ON Article (Title);

 

DROP INDEX:

The DROP INDEX statement is used to delete an index in a table.

Syntax

DROP INDEX TABLE_NAME.INDEX_NAME;

Example

The subsequent statement would delete an index named “IDXTitle” of “Article” table:

DROP INDEX Article.IDXTitle;

 

Also, check How To Add Column With Default Value In SQL

Yasin Panwala

Yasin Panwala is a Web Developer and Author at TheCodeHubs. He has experience in Web Developing and Designing and also in Writing. He has got his skills in working on technologies like .NET Core, ADO.NET, AJAX, Angular, AngularJS, ASP.NET, ASP.NET MVC, Bootstrap, C#, CSS, Entity Framework, Express.js, GraphQL, HTML, JavaScript, JQuery, JSON, LINQ, Microsoft Office, MongoDB, MySQL, Node.js, PostgreSQL, SQL, SQL Server, TypeORM, TypeScript, Visual Basic .NET, Web API. He also got his skills in working with different integration and some known versioning tools. He is always ready to learn new things and he always tries his best on tasks that are assigned to him and gives the best possible outputs.

View Comments

Recent Posts

Testing hk

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Operation

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

TETS NEW

test

3 years ago