SQL Server Indexes

SQL Server Tutorials and Syntax
Post Reply
Bob547
Posts: 12
Joined: Sat Jun 22, 2019 7:05 pm

Mon Jun 24, 2019 1:26 pm

SQL Server Indexes

The below will show you how to create, rename and drop indexes in SQL Server with syntax and examples.

What is an Index in SQL Server?
An index is a performance-tuning method of allowing faster retrieval of records.

Create an Index
Syntax
The syntax for creating an index in SQL Server (Transact-SQL) is:

Code: Select all

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
  ON table_name ( column1 [ASC | DESC ], ...  column_n [ ASC | DESC ] )
  [ INCLUDE ( column1, ... column_n ) ]
  [ WHERE condition ]
  [ WITH ( PAD_INDEX = { ON | OFF }
         | FILLFACTOR = fillfactor
         | SORT_IN_TEMPDB = { ON | OFF }
         | IGNORE_DUP_KEY = { ON | OFF }
         | STATISTICS_NORECOMPUTE = { ON | OFF }
         | STATISTICS_INCREMENTAL = { ON | OFF }
         | DROP_EXISTING = { ON | OFF }
         | ONLINE = { ON | OFF }
         | ALLOW_ROW_LOCKS = { ON | OFF }
         | ALLOW_PAGE_LOCKS = { ON | OFF }
         | MAXDOP = max_degree
         | DATA_COMPRESSION = { NONE | PAGE | ROW }
            [ ON PARTITIONS ( { number | range } ]
  [ ON partition_scheme ( column )
  | ON filegroup
  | ON default_filegroup ]
  [ FILESTREAM_ON { filegroup | partition_scheme };
 


UNIQUE
This is Optional. Indicates that the combination of values in the indexed columns must be unique.

CLUSTERED
This is Optional. Indicates that the logical order determines the physical order of the rows in the table.

NONCLUSTERED
This is Optional. Indicates that the logical order does not determine the physical order of the rows in the table.

index_name
The name of the index to create.

table_name
The name of the table or view on which the index is to be created.

column1, ... column_n
The columns to base the index.

ASC | DESC
The sort order for each of the columns.

INCLUDE ( column1, ... column_n )
Optional. The columns that are not key columns to add to the leaf level of the nonclustered index.

WHERE condition
Optional. The condition to determine which rows to include in the index.

ON partition_scheme ( column )
Optional. Indicates that the partition schema determines the filegroups in which the partitions will be mapped.

ON filegroup
Optional. Indicates that the index will be created on the specified filegroup.

ON default_filegroup
Optional. Indicates the default filegroup.

FILESTREAM_ON { filegroup | partition_scheme }
Optional. Indicates where to place the FILESTREAM data for a clustered index.

Index Example
How to create an index in SQL Server:

Code: Select all

CREATE INDEX Customers_idx
  ON Customers (First_name);
 

In the above example, we have created an index on the Customers table called Customers_idx. It consists of only one field which is the First_name field.

It is also possible to create an index with more than one field as in the example below:

Code: Select all

CREATE INDEX Customers_idx
  ON contacts (First_name, Surname_name);

In the above example, we have created an index on the Customers table called Customers_idx but this time, it consists of the First_name and Surname_name fields.

As we have not specified 'ASC | DESC' to each of the columns, the index is created with each of the fields in ascending order. We could modify our example and change the sort orders to descending as follows:

Code: Select all

CREATE INDEX Customers_idx
  ON Customers (First_name DESC, Surname_name DESC);

This CREATE INDEX example will create the Customers_idx index with the First_name sorted in descending order and the Surname_name sorted in descending order.

UNIQUE Index Example
Next, let's look at an example of how to create a unique index in SQL Server:

Code: Select all

CREATE UNIQUE INDEX Customers_uidx
  ON contacts (First_name, Surname_name);

This example would create an index called Customers_uidx on that contacts table that consists of the First_name and Surname_name fields, but also makes sure that the there are only unique combinations of the two fields.

You could change this example further to make the unique index also clustered so that the physical order of the rows in the table is determined by the logical order of the index.

Code: Select all

CREATE UNIQUE CLUSTERED INDEX Customers_uidx
  ON contacts (First_name, Surname_name);

This example creates an index called Customers_uidx that is a unique index based on the First_name and Surname_name fields and the index is also clustered which changes the physical order of the rows in the table.

Rename an Index
The syntax for renaming an index in SQL Server (Transact-SQL) is:

Code: Select all

sp_rename 'table_name.old_index_name', 'new_index_name', 'INDEX';
table_name
The name of the table where the index has been created.

old_index_name
The name of the index that you wish to rename.

new_index_name
The new name for the index.

Code: Select all

sp_rename 'customers.customers_idx', 'customers_index_name', 'INDEX';
In the above example, we're renaming the index on the customers table called customers_idx to customers_index_name.

how to drop an Index
The syntax for dropping an index in SQL Server is:

Code: Select all

DROP INDEX table_name.index_name;
table_name
The name of the table where the index has been created.

index_name
The name of the index to drop.

The below is an example of how to drop an index in SQL Server:

Code: Select all

DROP INDEX Customer.Customer_idx;
In the above example the index 'Customer_idx' is being dropped.
Post Reply

Social Media