SQL Server Tour--Nineth stop look at these composite indexes designed by the company's DBAs

Source: Internet
Author: User

Original: SQL Server Tour--Nineth stop look at these composite indexes designed by the company's DBAs

This is the last topic of the index, index coverage, of course, the better way to learn is to look at the masters of the design of the index, to see what nutrients can be extracted from the things, let us see

Look at one of the core orders tables in the database.

  

One: View the schema of the table

<1> See the approximate schema information for this table first

1 --View schema information for a table2 SELECTC.column_id,c.name,t.name fromSys.columns asC3 JOINsys.types T4  onc.system_type_id=t.system_type_id5 WHEREC.object_id=object_id(' O_Orders') 6 ORDER  byc.column_id

There are about 89 fields from this order table ... Still a lot of, there may be too many historical reasons, there is a question here, for so many fields plus a variety of types, how to plan

Good single-column index and composite index ... Let's take a look at how these experts are designed.

<2> Composite Index

First of all, because my permissions are limited, DBCC IND,PAGE and other commands are not possible, so I have no ability to determine whether the following index is an include index or a composite index, so this is called a unified

Composite Index Bar.

1 SELECT  from WHERE object_id = object_id ('o_orders')

As you can see from the above, there are 9 nonclustered indexes, 1 clustered indexes, and then you can check several indexes by Show_statistics to see which fields are associated and find the two indexes.

Covers up to 6 columns, such as index "Idx_order_status_2", "Ix_o_ordersuid".

DBCC show_statistics (o_orders,idx_order_status_2) DBCC Show_statistics (O_orders,ix_o_ordersuid)

From the fields associated with these two indexes, you can probably see two points of information:

①: These fields are small, char (1), and smallint,bit, which naturally represents less state.

②: Select several fields with fewer states in the table to make an index in combination with the frequency of access.

But think about it, although in principle it is not appropriate to build an index, but a field like "Order Status" (Orderstatus) is definitely a column that is frequently queried ... Since it's a frequent column,

It's definitely going to be a way to optimize, by building composite indexes that make it easier to hit index overlays in complex SQL.

such as the following:

1 SET STATISTICSIo on 2 SELECTorderstatus, Processstatus, sendticketcity, Flightagency, Eticket, OrderID3  fromDbo. OrdersWHEREOrderstatus='P'  andProcessstatus='1'  andSendticketcity=1

Then continue to select a few indexes to aim at ... In general, indexes that cover between 1 and 2 columns are called small indexes.

1 DBCC show_statistics (o_orders,idx_eid_orderdate) 2 DBCC Show_statistics (O_orders,ix_o_order_finishdate)

Through the above index can be seen, Eid and finishdate these two columns, a glance to know should be a unique relatively high column, and why to cover 2 columns, that this is based on business

and production of rolling data to determine, then what are the benefits of such an index? It's also easier to bump into an index link, which means that multiple indexes are going to be in the multi-criteria, and that each index contributes a few columns to just

Satisfy all columns in select ... such as the following.

 1  --  2  select   orderid,finishdate,prepaytype,eid,orderdate  3  from  dbo. O_orders where  eid '  

Well, as the friends of the park said, indexes are pay Paul, and each index you build needs to evaluate its pros and cons.

SQL Server Tour--Nineth stop look at these composite indexes designed by the company's DBAs

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.