A brief discussion on SQL Server focused forced index query condition and Columnstore index_mssql

Objective

In this section, we'll talk about indexing knowledge, followed by date types in data types, short content, in-depth understanding.

Force Index query criteria

We also spoke a little bit about forcing the index query, and we'll go through the whole story in this section.

(1) SQL Server uses default index

Use TSQL2012
go
SELECT * 
sales.orders

I'm not going to bother with that. Use the clustered index created by the default primary key to execute the query execution plan.

(2) SQL Server uses mandatory indexing

Use TSQL2012
go
SELECT custid to 
sales.orders with (INDEX (Idx_nc_custid))

(3) SQL Server uses inline query criteria

Use TSQL2012
go
SELECT custid from 
sales.orders with (INDEX (Idx_nc_custid))
  INNER JOIN Sales.orderdetails with (INDEX (pk_orderdetails) in 
  Sales.OrderDetails.orderid = Sales.Orders.orderid Go

(4) SQL Server uses option

The third of us to use inline queries is better, and we can use option at the end of the table connection to force the index, because it involves table joins and we use another function hints. This hints is used in join hints, Query hints, and Table hints. If we specify to use hints it is obvious that the query plan will be overwritten because the SQL Server defaults to using the optimal query on the query, and the hints will probably overwrite the optimal query, so it is generally not recommended, only for the explicit knowledge that this would be better than the default query plan, with rich experience in SQL The server consumer may be a good solution. But for option we can use, option is only available on SQL Server 2008+, the third forced use of inline query hints can be done with option combined with hints.

Use TSQL2012
go
SELECT custid from sales.orders as so
  INNER JOIN sales.orderdetails as SOD
  on Sod.orderid = 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 this better than using the optimal execution query plan by default.

Columnstore Index

The column storage index appears on SQL Server 2012+, as we simply said in this section, the reasonable use of column storage indexes for large data can improve query performance, but by looking for a large amount of data found in SQL Server There are a number of limitations and problems with the use of column storage indexes in 2012, and the problem with SQL Server 2014+ is greatly improved, so it is not recommended that you use column storage indexes in SQL Server 2012, which you can use on SQL Server 2014+.

When we create an index in SQL Server 2012, we will be prompted with Columnstore index and nonclustered Columnstore index. There are two types of data stored in a database. One is row storage, the other is column storage, where the row store stores all row data on the page, and the column store stores all the data on the same page, so using column storage to find the data is faster than finding all the data on the entire row. This, of course, means that the increase in lookup speed leads to higher requirements for hardware-driven configurations, and that the indexes in the column store are compressed, so it requires more memory to use the column storage. Column Storage index stores each column of data in each set of separate disk pages, instead of storing multiple rows per page, it differs from row storage storage data in the following figure

Here about the example is no longer given, is to do a basic understanding of it.

Summarize

In this section, we mainly talk about enforcing index conditions for querying, and when it is not an optimal solution to use the default creation index for a query plan, you can try using a forced index to compare to find a better solution. Short content, deep understanding, we'll see you in the next section.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!