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

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