Description: SQL Server focuses on mandatory Index query conditions and Columnstore Index. column is a row Index.

Description: SQL Server focuses on mandatory Index query conditions and Columnstore Index. column is a row Index.

Preface

In this section, we will introduce the indexing knowledge, the date type in the data type, the short content, and a deep understanding.

Mandatory index query Conditions

We have also discussed a bit of knowledge about forced index query. This section will introduce it in detail.

(1) SQL Server uses the default Index

USE TSQL2012GOSELECT * FROM Sales.Orders

In this case, I don't have to worry about it anymore. Use the clustered index created by the default primary key to execute the query execution plan.

(2) SQL Server uses a forced Index

USE TSQL2012GOSELECT custid FROM Sales.Orders WITH(INDEX(idx_nc_custid))

(3) SQL Server uses inline query Conditions

USE TSQL2012GOSELECT custid FROM Sales.Orders WITH (INDEX(idx_nc_custid))  INNER JOIN Sales.OrderDetails WITH (INDEX(PK_OrderDetails))   ON Sales.OrderDetails.orderid = Sales.Orders.orderidGO

(4) SQL Server uses OPTION

The third way we use inline queries is better. At the same time, we can use OPTION to force the index at the end of the table connection. Because table join is involved, we will use another function Hints. This Hints is used in Join Hints, Query Hints, and Table Hints. If Hints is specified, it will obviously overwrite the query plan, because the SQL Server will use the optimal query by default. At this time, Hints may overwrite the optimal query, therefore, it is generally not recommended to use it. It may be a good solution for SQL Server users who have a wealth of experience and know better than the default query plan. However, we can use OPTION only on SQL Server 2008 +. The third forced use of inline query prompt above can be achieved through OPTION combined with Hints.

USE TSQL2012GOSELECT 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 make it better than using the optimal query plan by default.

Columnstore Index

Column storage indexes appear on SQL Server 2012 +. In this section, we will briefly talk about this content. The rational use of column storage indexes for big data can improve query performance, however, by looking for a large amount of data, we found that there are many restrictions on using column-store indexes in SQL Server 2012 and there will be many problems, this problem has been greatly improved in SQL Server 2014 +, so we do not recommend using column storage indexes in SQL Server 2012, you can use it on SQL Server 2014 +.

When you create an Index in SQL Server 2012, the system prompts Columnstore INDEX and NONCLUSTERED COLUMNSTORE Index. There are two types of data stored in databases: Row-based storage and column-based storage. Row-based storage stores all row data on pages, column store stores all data in a column on the same page. Therefore, using column store to search for data is faster than searching for all data in the whole row, of course, this means that the increase in search speed leads to a higher requirement on the hardware driver configuration. The indexes in the column store are compressed, so the use of column store requires a larger memory. The column storage index stores each column of data in each group of independent disk pages, instead of storing multiple rows on each page. The difference between the column storage index and the Row Storage data is as follows:

The example is not provided here. It is a basic understanding.

Summary

This section describes how to forcibly use index conditions for queries. When using the default index creation for query planning, it is not the optimal solution, you can try to use a forced index for comparison to find a better solution. The short content and in-depth understanding will be discussed later in the next section.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!