How to create an index

Through the previous two articles we basically learned how to combine the execution plan to analyze the performance of a SQL statement, since there is a performance problem has been found, the next is how to optimize the query to improve query performance, and the most important weapon to optimize the query is to create an index, This article summarizes how to create indexes to improve query speed, mainly from the following aspects.

1. Understanding Indexes
2. Syntax for creating indexes
3. Combining examples to demonstrate how to create an index

Understanding Indexes

1, what is the index?

In SQL Server, an index is an enhanced presence, which means that the functionality of SQL Server is not affected, even without an index. An index is a structure (b-tree) that sorts the values of one or more columns in a database table, using an index to quickly access specific information in a database table.

2, why should I use an index?

is to improve query performance.

3, what are the indexes?

There are mainly clustered indexes (CLUSTERED index) and nonclustered indexes (nonclustered index), which means that the physical and logical order of one or more columns is the same, and that a database table can have only one clustered index, and we typically have the primary key ( Usually self-increment int) is set to a clustered index. Nonclustered indexes can have more than one, and nonclustered indexes do not alter the physical structure of the database tables.

Syntax for creating indexes

1, create the index, the SQL syntax is as follows.

--Build index if not EXISTS (SELECT * from sysindexes WHERE id=object_id (' sales.orders ') and Name= ' idx_nc_orderdate ')    create Nonclustered INDEX idx_nc_orderdate on Sales.orders (OrderDate); GO

2, delete the index, the SQL syntax is as follows.

--Drop Index if EXISTS (SELECT * from sysindexes WHERE id=object_id (' sales.orders ') and Name= ' idx_nc_orderdate ')    Idx_nc_orderdate on Sales.orders; GO

Combining examples to demonstrate how to create an index

The table structure is as follows:

Now the need is to query all orders from July 1, 2006 to July 31, 2007, and sort by the RequiredDate field, query the SQL code as follows:

Use TSQLFundamentals2008; go--query for all orders from July 1, 2006 to July 31, 2007 and Sort by RequiredDate field select Orderid,orderdate,requireddate,shippeddate,shipname, ShipAddress from Sales.orderswhere orderdate>= ' 20060701 ' and orderdate< ' 20070801 ' ORDER by RequiredDate;

Note that this table has no index except for the clustered index of the primary key OrderID. Execute a query to view the execution plan, such as.

As you can see from the execution plan above, it is time consuming to sort and aggregate index lookups. First look at the sort, because is by requireddate sort, so the RequiredDate field to establish a single field index, the code is as follows.

IF not EXISTS (SELECT * from sysindexes WHERE id=object_id (' sales.orders ') and Name= ' idx_nc_requireddate ')    CREATE NON CLUSTERED INDEX idx_nc_requireddate on Sales.orders (RequiredDate); GO

Then, because the where condition is scoped by the OrderDate field, a single field index is established for the OrderDate field, as shown in the code below.

IF not EXISTS (SELECT * from sysindexes WHERE id=object_id (' sales.orders ') and Name= ' idx_nc_orderdate ')    CREATE nonclu Stered INDEX idx_nc_orderdate on Sales.orders (OrderDate); GO

Because the field to query is orderid,orderdate,requireddate,shippeddate,shipname,shipaddress, we can create a composite field index for these fields, as shown in the code below.

IF not EXISTS (SELECT * from sysindexes WHERE id=object_id (' sales.orders ') and Name= ' Idx_orders_orderid_orderdate_ ShipAddress ')    CREATE nonclustered INDEX idx_orders_orderid_orderdate_shipaddress on sales.orders (OrderID, orderdate,requireddate,shippeddate,shipname,shipaddress); GO

Finally, we rerun the query again to see the execution plan, such as.

As you can see from the execution plan, the latter has become a nonclustered index query, indicating that the index we created is working. Whether to create a single field index, a combined field index or two to create, this should be tested repeatedly in the actual project.

Resources

1,careyson's T-SQL query Advanced--Understanding the concepts of indexes in SQL Server, principles, and other

2, the Pastoral cricket programmer in the eyes of the SQL server-execution plan teaches me how to create an index?

How to create an index