Let's talk about the "simple" select top "in SQL database-there may be details you have never noticed.

Start with Jerome Wong, a blog site.

He raised this question.

I have been writing SQL statements for several years and have never noticed this problem.

For example:

The data table is as follows:

Id empno name age

1 26929 Jerome 28
2 28394 Quince 27
3 20983 Green 30
4 27189 Mike 30
5 23167 arishy 30
6 26371 Yager 29

I wrote an SQL statement to get 3rd or 4 pieces of data and test paging.

Select Top 2 * from (Select Top 4 * from Member) M order by M. rowid DESC

The subquery in the middle section is executed:Select Top 4 * from Member

Obtained:

1 26929 Jerome 28
2 28394 Quince 27
3 20983 Green 30
4 27189 Mike 30

However, the results of the entire SQL statement are:

5 23167 arishy 30
6 26371 Yager 29

I really don't know how this happens. Please advise me.

 

Whether you are a newbie or an expertProgramWe often encounter details like this.

Next, I will make a series of analyses on the issues raised by the Jerome Wong netizens regarding select top (here I would like to thank the Jerome Wong netizens for proposing this question)

Preparations

 

Code

If Object_id ( ' Zhuisuo ' ) Is Not Null
Drop Table Zhuisuo
Go
Create Table Zhuisuo
(
ID Int Null ,
Name Varchar ( 20 ) Null
)
Insert Into Zhuisuo Values ( 1 , ' Recourse 1 ' )
Insert Into Zhuisuo Values ( 2 , ' Recourse 2 ' )
Insert Into Zhuisuo Values ( 3 , ' Recourse 3 ' )
Insert Into Zhuisuo Values ( 4 , ' Recourse 4 ' )
Insert Into Zhuisuo Values ( 5 , ' Recourse 5 ' )
Insert Into Zhuisuo Values ( 6 , ' Recourse 6 ' )
Insert Into Zhuisuo Values ( 7 , ' Recourse 7 ' )
Insert Into Zhuisuo Values ( 8 , ' Recourse 8 ' )
Insert Into Zhuisuo Values ( 9 , ' Recourse 9 ' )
Insert Into Zhuisuo Values ( 10 , ' Recourse 10 ' )
Go

 

 

Next, let's write two simple select statements.

Select Top 2 * From ( Select Top 4 * From Zhuisuo) m Order By M. ID Desc

SelectTop2*From(SelectTop4*FromZhuisuoOrderByIDASC) MOrderByM. IDDesc

The execution results will show that

Most people usually think that the execution results of these two statements will be the same.

How can this happen?

 

You can see clearly from this query plan

First, after scanning the zhuisuo table, first descending order (Top N sort) and then the first two rows in the range of four rows

The second method is to scan the zhuisuo table and then take four rows (Top N sort) in ascending order, and then take the four rows (Top N sort) in descending order)

Here we have to briefly talk about the table subqueries in SQL statements.

Table subquery, and the table that appears in the from clause is called a derived table.

The derived table is virtual and has not been physically embodied. That is to say, when the table is compiled

For example, (select Top 2 * from (select Top 4 * From zhuisuo) m order by M. id desc)

External queries and internal queries are merged and a plan is generated.

Now let's take a look at the above execution plan.

(Note: Order by is generally not allowed in a derived table unless top

That is to say, the Select Top 2 * from (select * From zhuisuo order by id asc) m order by M. id desc statement cannot be executed)

A derived table is a quasi-table to be referenced outside, and order by returns a cursor instead of a table. Therefore, it is restricted only when order by is used.
Why is it okay to use top plus order?
This is because top can select a specified number from the cursor returned by order by to generate a table and return

 

Next I will give an example of the top-level precautions.

1. Using Top to return random rows, many people will think of using the rand function to obtain such a statement.

Select Top 4 ID, name from zhuisuo order by rand ();

After multiple queries, you will be disappointed to find that it does not return random rows.

This is because each query calls it only once instead of once per line.

Now we can change rand to newid.

Select Top 4 ID, name from zhuisuo order by newid ();

Now you will get the desired result. Here we can realize that newid has better distribution characteristics.

 

2. Note that top is used in insert.

Insert top (4) into zhuisuo

Select * From zhuisuo order by ID DESC

Many netizens will explain how to insert the last four data entries in the zhuisuo table into the table.

However, after the execution is completed, you will be disappointed. The first four items are inserted.

The correct method for inserting the top part in flashback should be

Insert into zhuisuo
Select top (4) * From zhuisuo order by ID DESC

What are the differences between the two statements?

In fact, the above statement is not sorted (Top N sort)

 

3. What if I want to delete the last five data records in a data table?

Order by cannot be used when Delete and update use top.

Now we can solve this problem.

Delete Zhuisuo
Where ID In ( Select Top ( 5 ) ID From Zhuisuo Order By ID Desc )

Update Zhuisuo
Set Name = ' Recourse ' + Name
Where ID In ( Select Top ( 5 ) ID From Zhuisuo Order By ID Desc )

 

This is disguised to implement Top N sort update or delete data, but this is not the optimal method, because it also requires the root ID to match

In this case, we can use this method.

With Cte_del As
( Select Top ( 5 ) * From Zhuisuo Order By ID Desc )
Delete From Cte_del

With Cte_del As
( Select Top ( 5 ) * From Zhuisuo Order By ID Desc )
Update Cte_del Set Name = ' Recourse ' + Name

 

 

4. In addition to these features, top has more functions. For example, I used Top N sort and apply to answer a netizen's question.

How to query a user's reading records with a accuracy rate greater than 60% in the last month, and display only the one with the highest accuracy rate per day

Here, I will only mention a few interesting details about apply. In the future, I will write them in a casual manner.

 

The last part is the procedure for using the serial number to represent the logical query processing.

 

ArticleI wrote it here today. Have you learned it?