How to Create Implicit and Explicit Index in SQL

In this article I am going to explain how to create implicit and explicit index in SQL.
  • 5028

Introduction

Index in SQL is used to quickly access the data from table or view. Indexes are created on table or view. Database have large amount of data like thousands of tables and view. When we try to access this data it takes more time.

SQL provide a way known as index through we can access a specific data quickly. CREATE INDEX statement is used to create a index.

Type of index in SQL

  • Clustered index
  • Non clustered index
  • Unique index
  • Columnstore index

What is Implicit Index

Implicit Index are created when a column is explicitly defined with UNIQUE Constraint and PRIMARY KEY constraint.

What is Explicit Index

Explicit Index are created when we use CREATE INDEX statement.

Statement that Explicit index

USE vipendra;

GO

IF EXISTS (SELECT name FROM sys.indexes

            WHERE name = N'non_cl_index_student')

    DROP INDEX non_cl_index_student ON Purchasing.ProductVendor;

GO

CREATE INDEX IX_ProductVendor_VendorID

    ON dbo.studentinfo (s_id);

GO


© 2019 DotNetHeaven. All rights reserved.