How to Create SQL Index

 Admin   
260 View    Jan 19 2020 12:59AM

 

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

It creates a relational index on a table or view also called a row store index because it is either a clustered or non clustered B-tree index. You can create a row store index before there is data in the table. Use a row store index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.

Note

SQL Data Warehouse and Parallel Data Warehouse currently do not support unique constraints. Any examples referencing Unique Constraints are only applicable to SQL Server and SQL Database.

 

 

 

 

 

Besides storing the index key values, the leaf nodes also store row pointers to the data rows that contain the key values. These row pointers are also known as row locators.

If the underlying table is a clustered table, the row pointer is the clustered index key. In case the underlying table is a heap, the row pointer points to the row of the table.

Simple examples:

-- Create a nonclustered index on a table or view

CREATE INDEX i1 ON t1 (col1);

 

-- Create a clustered index on a table and use a 3-part name for the table

CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);

 

-- Syntax for SQL Server and Azure SQL Database

-- Create a nonclustered index with a unique constraint

-- on 3 columns and specify the sort order for each column

CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

 

Syntax

CREATE INDEX index_name

ON table_name (column1column2, ...);

CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);

 

We will use the sales.customers from the sample database for the demonstration.

customers

 Using the SQL Server CREATE INDEX statement to create a nonclustered index for one column example

 

SELECT

    customer_id,

    city

FROM

    sales.customers

WHERE

    city = 'Atwater';