SQL server-Focus Forced index query conditions and Columnstore indexes (ix)

Objective

In this section, we'll go through the indexing knowledge, followed by the date type in the data type, the short content, and the deep understanding.

Force Index query criteria

We've also talked a little bit about forcing index queries, and we're going to go through the whole story

(1) SQL Server uses the default index

* FROM sales.orders

I'm not going to do this anymore. Use the clustered index created by the default primary key to execute the query execution plan.

(2) SQL Server uses forced indexing

Use Tsql2012goselect CustID from Sales.orders with (INDEX (Idx_nc_custid))

(3) SQL Server uses inline query criteria

Use tsql2012goselect CustID from Sales.orders with (INDEX (Idx_nc_custid))    INNER JOIN sales.orderdetails with (INDEX (pk_orderdetails))     = Sales.Orders.orderidGO

(4) SQL Server uses the option

Our third use of inline queries is good, and we can use option at the end of a table connection to force the use of the index, as we will use another function hints when it comes to table joins. The Hints is used in join Hints, Query Hints, Table Hints. If we specify that using hints will obviously overwrite the query plan, because SQL Server will use the optimal query on the query by default, it will probably overwrite the optimal query by hints, so it is generally not recommended, and it is only better to know that it will be more experienced than the default query plan. Server users can be a good solution. But for option we can use, option is only available on SQL Server 2008+, the third mandatory use of inline query hints can be done by using option with hints.

Use tsql2012goselect CustID from Sales.orders    as So INNER JOIN sales.orderdetails as SOD    = so.orderid    OPTION (TABLE HINT (So,index (Idx_nc_custid )), TABLE HINT (SOD, INDEX (pk_orderdetails) )) GO

This is not recommended unless you explicitly do better than using the best execution query plan by default.

Columnstore Index

The Columnstore index appears on SQL Server 2012+, and we'll talk about it in this section, and using a Columnstore index for big data can improve query performance, but by looking at a large amount of data to discover what's in SQL Server There are a number of limitations to using Columnstore indexes in 2012 and many problems, and problems such as those on SQL Server 2014+ have been greatly improved, so it is not recommended to use Columnstore indexes in SQL Server 2012, which can be used on SQL Server 2014+.

When we create an index in SQL Server 2012, we are prompted to have COLUMNSTORE index and nonclustered COLUMNSTORE index. There are two types of data stored in a database, one is row storage, the other is Columnstore, the row store stores all row data on the page, and the Columnstore stores a column of all the data on the same page, so finding data using Columnstore is faster rather than finding all the data on the entire row. This, of course, means that the increase in the lookup speed results in a higher requirement for hardware-driven configurations, and that the indexes in the Columnstore are compressed, so more memory is needed to use the column store. The Columnstore index stores each column of data in each set of separate disk pages, rather than storing multiple rows on each page, and the row stores the stored data differently from the image from: sqlauthority.

The example here is no longer given, is to do a basic understanding of it.

Summarize

In this section we mainly talk about forcing the use of index conditions to query, when it is not optimal to use the default index to create the query plan, you can try to use forced indexes to compare to find a better solution. Short content, in-depth understanding, we'll see you next day.

SQL server-Focus Forced index query conditions and Columnstore indexes (ix)