SQL Server in the eyes of programmers-what does non-clustered index bring to us ?,

SQL Server in the eyes of programmers-what does non-clustered index bring to us ?,
Zookeeper

I will not talk about some database concepts, so I can only use some practices to understand the meaning of these concepts. The use cases in the following application scenarios are virtualized and used only for personal research.

Programmers should be curious, though this is a database problem.

Application scenarios

There is a Product table with the following fields:

Data adding script:

Begin trandeclare @ index intset @ index = 0 while (@ index <1000000) begininsert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State]) values ('I am test title 1 ', 'I am testing remark 1 I'm testing remark 1 I'm testing remark 1 I'm testing remark 1 I'm testing remark 1 I'm testing remark 1', 1, GETDATE (), 0) insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State]) values ('I am test title 2 ', 'test remarks 2 I test remarks 2 I test remarks 2 I test remarks 2 I test remarks 2 ', 1, GETDATE (), 1) insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State]) values ('I am test title 3',' I am Test Remarks 3', 3, GETDATE (), 1) insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State]) values ('I am test title 4 ', 'test remarks 4 I test remarks 4 I test remarks 4 I test remarks 4 I test remarks 4 I test remarks 4 ', 4, GETDATE (), 1) set @ index = @ index + 1 endcommit

4 million of data is inserted in the Product table. To stay close to our actual production environment, data is inserted differently.

For general application environment queries, sometimes we perform a where query for a field, and sometimes query with another field. At this time, how can we create indexes for these two fields? Or do you not need to create it? Are two instances created separately? Or create a combination? To be honest, I may see that the database experts will smile, but as a programmer, I really don't know about this. There is no way to explain the search information, you can only try it on your own. We will perform the following query operations for ProviderID and State, including separate queries and combined queries. Then we will create indexes for these two fields in the Product table to see what is the difference? What is the difference between queries based on different indexing methods? Let's have a look. Technical Improvement visit www.cgzhw.com game programming network very good technical site.

Problem Analysis

I will explain the above analysis. First, there are two types of queries:

There are three types of non-clustered indexes:

For this application scenario and the above analysis, we will get 3x2 6 results. In fact, what I want to know most is the third one below, that is, creating a composite field index, will the query of a single field affect? In turn, will the creation of an index for a single field affect the query of combined fields? Of course, you can see the execution result after you try it.

Execution result

Test script:

Declare @ begin_date datetimedeclare @ end_date datetimeselect @ begin_date = getdate () select * from [dbo]. [Product] where... select @ end_date = getdate () select datediff (MS, @ begin_date, @ end_date) as 'time/millisecond'

In order to get close to the test result, it is too troublesome to execute the statement three times each time and then take the average value. Here we will directly paste the execution result.

If you do not create an index, create the index field ProviderID.

Create index fields ProviderID and State

Result Analysis

Although the test is incomplete, the result is messy (the index is created, but the performance will be reduced ?), Is there a problem with the data I inserted? Or is there a problem with creating an index? Or is there a problem with my character ??? Wait for the advice of the database...