SQL prompt tutorial: top without order by in select statement (bp006)
2020-11-10 19:10:11 【roffey】
SQL Prompt It's a practical SQL Grammar tip tool .SQL Prompt According to the object name of the database 、 Syntax and code snippets are retrieved automatically , Provide users with appropriate code selection . Automatic script settings make code easy to read -- Especially useful when developers are not familiar with scripts .SQL Prompt Install and use , It can greatly improve the coding efficiency . Besides , Users can also customize according to their needs , Make it work the way you want it to .
stay SELECT In the sentence , One should always be ORDER BY Clause and this TOP Clauses are used together , To specify which trades have been subjected to TOP The filter affects . If you need to implement an application widget paging solution , Sending blocks or data to the client “ page ”, So users can scroll through the data , It's better , Easier to use OFFSET-FETCH In the festival ORDER BY section , instead of TOP Clause .
SQL Prompt（BP006） Medium “ Best practices ” Code analysis rules include an avoidance of TOP stay SELECT A suggestion used in a statement without a suggestion .
use TOP Restricted line
TOP It's not standard SQL, But it's intuitive . If you just want to get some presentation routines from the table source , So it's easy to use TOP No ORDER BY The keyword of the clause . A single table is likely to conform to the order of the clustered indexes , But because of parallelism , Even that doesn't guarantee .
If we go beyond querying a single table and making some joins , that “ natural ” The order is less obvious . Maybe you are in AdventureWorks in , Just five clients , Any five clients and their addresses . It's perfectly legal to do this , But if you later forget the reason for doing this , It's a little dangerous .
SELECT TOP 5 Person.Title, Person.FirstName, Person.MiddleName, Person.LastName, Address.AddressLine1, Address.AddressLine2, Address.City, Address.PostalCode, AddressType.Name FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;
detailed list 1
You will get the desired results , It's just the first five customers returned by the query . The order I get is Sales.Customer The order of the clustered indexes of the table , The order is PK_Customer_CustomerID From the lowest customer_id People who （ Not the store ） At the beginning . Different execution strategies may change this . You can't guarantee a definite result . If you just want to get samples during development , It might be nice , But in the production system , What you really want is the top five customers , And determine the address according to its ranking order , These rankings are made up of certain attributes （ For example, how much ） decision . You really need that ORDER BY.
In short ,SQL A table cannot guarantee the consistency of its inherent order . You may have set up a PRIMARY KEY, Give your table values some basic order , But it doesn't guarantee consistency .SQL Server Reserves the right to introduce any optimization required during the creation of an execution plan to return results , Even if it means passing the results in a different order . In short , Unless you pass ORDER BY The statement specifies it explicitly , Otherwise, you cannot guarantee that the results will be returned in the expected order .
therefore , We went back to the perfectly reasonable request , That is, developers must be able to query the representative row samples in the query . What to do ？
SET ROWCOUNT and TABLESAMPLE： Do they help ？
There was a time , We have to use the SET ROWCOUNT Statement to limit the number of rows returned . One disadvantage of this is that the query optimizer cannot create a valid plan based on the number of rows requested , Because of this ROWCOUNT It's a conversation or process / Trigger range settings , Not visible to the query optimizer in the query .
Again , You may forget that you have set up ROWCOUNT And ignore “ Cancel settings ” it . Another drawback is that you can't pass values to variables .TOP Better , Because it works at the statement level , And you can pass line values or percentages as variables or expressions .
You may think you can use this TABLESAMPLE Clause reliably fetches a finite number of rows from a table . The only problem is that it doesn't work as advertised , Even if it does work as advertised , You can only work on a watch , Instead of all kinds of surface sources .
SELECT * FROM Sales.Customer TABLESAMPLE SYSTEM (5);
detailed list 2
This should be from FROM The number of rows returned by a table in the clause is limited to the number of samples or PERCENT Row number . A quick test will show you why no one uses it .
DROP TABLE IF EXISTS #Result; CREATE TABLE #Result (TheOrder INT IDENTITY, TheRowsReturned INT); GO INSERT INTO #Result (TheRowsReturned) SELECT Count(*) FROM Sales.Customer TABLESAMPLE(200 ROWS); GO 30 SELECT #Result.TheOrder, #Result.TheRowsReturned FROM #Result;
detailed list 3
Use TOP…ORDER BY Get meaningful table samples
up to now , because TABLESAMPLE Damaged , So it's embarrassing to get samples from the table .
SELECT TOP 5 * FROM Sales.Customer
detailed list 4
Why is this embarrassing ？ detailed list 4 Will return you five elements , But you may not be able to rely entirely on the returned lines , Even though it may be in order PRIMARY KEY, Because we're just accessing a table . however ,sales.customer It's a little “ skill ” surface , Because it uses polymorphic associations , also 19820 The front of the line 700 It's a shop , Not people . therefore , detailed list 4 A very non representative example of this table might be given , Because you can easily get an incorrect impression of the data in the table , Think the customer is a store , And most of them are people ！
What most developers want to see is a few rows of the table they are investigating , It's random , But if you want to extract examples in random order , It must be made clear that .
SELECT TOP 5 * FROM Sales.Customer ORDER BY NewId()
detailed list 5
This will return five lines in random order , But more resources are needed to return the results . If you're not dealing with “ skill ” surface , Just don't care about the order , And you need to point that out in the code , be SQL Server Will accept any system function , for example @@version or host_name(), even to the extent that ORDER BY（SELECT NULL）. stay SQL Server Rejection requires Windows Window function in the case of code , You usually see this technique ORDER BY. It means “ That's true. , That's true. , But I did it on purpose ”.
SELECT TOP 10 * FROM Sales.Customer ORDER BY @@identity
detailed list 6
If you are happy with the records you have obtained through the use of TOP No, ORDER BY, Well, it's better to be completely clear , And point out that , You really want it to be PRIMARY KEY site
SELECT TOP 5 * FROM Sales.Customer ORDER BY Customer.CustomerID;
detailed list 7
take TOP And ORDER BY Combined to report queries
TOP Independent for reporting purposes . Managers like the list of top customers and top salesmen . At this point , The ORDER BY Parts become crucial .
SELECT TOP 10 Person.BusinessEntityID, Sum(SalesOrderHeader.TotalDue) AS expenditure FROM Sales.SalesPerson INNER JOIN Sales.SalesOrderHeader ON SalesPerson.BusinessEntityID = SalesOrderHeader.SalesPersonID INNER JOIN Person.Person ON SalesPerson.BusinessEntityID = Person.BusinessEntityID GROUP BY Person.BusinessEntityID ORDER BY Sum(SalesOrderHeader.TotalDue) DESC;
detailed list 8
This provides you with the top ten salesmen .
We might think that the report doesn't really tell us who the salesperson is , So we adjusted it .
SELECT SalesPerformance.SalesValue, Coalesce(Person.Title + ' ', '') + Person.FirstName + Coalesce(' ' + Person.MiddleName, '') + ' ' + Person.LastName + Coalesce(' ' + Person.Suffix, '') AS SalesPerson FROM ( SELECT TOP 10 SalesPerson.BusinessEntityID AS salesPerson, Sum(SalesOrderHeader.TotalDue) AS SalesValue FROM Sales.SalesPerson INNER JOIN Sales.SalesOrderHeader ON SalesPerson.BusinessEntityID = SalesOrderHeader.SalesPersonID INNER JOIN Person.Person ON SalesPerson.BusinessEntityID = Person.BusinessEntityID GROUP BY SalesPerson.BusinessEntityID ORDER BY Sum(SalesOrderHeader.TotalDue) DESC ) AS SalesPerformance(SalesPerson, SalesValue) INNER JOIN Person.Person ON SalesPerformance.SalesPerson = Person.BusinessEntityID ORDER BY SalesPerformance.SalesValue DESC
detailed list 9
Why do we need a second ORDER BY Well ？ original SQL Is a summary query , We need the front 10 The total sales amount is , So we have to impose an order on it . This is not passed in a fixed order to an external query with the person's name added . To determine the order of external queries , It will also require an explicit ORDER BY Clause . Sometimes it's called “ Presentation ORDER BY” or “ Presentation sorting ”.
Use FETCH-OFFSET instead of TOP
Of course , A better way is ORDER BY take SQL Server 2012 And optional in later versions OFFSET–FETCH Clause is used with TOP. It has more uses , It's also standard ANSI I SQL. This is a AdventureWorks The longest service time 20 Employees .
SELECT Employee.JobTitle, Employee.HireDate, Coalesce(Person.Title + ' ', '') + Person.FirstName + Coalesce(' ' + Person.MiddleName, '') + ' ' + Person.LastName + Coalesce(' ' + Person.Suffix, '') AS Name FROM HumanResources.Employee INNER JOIN Person.Person ON Person.BusinessEntityID = Employee.BusinessEntityID ORDER BY Employee.HireDate ASC OFFSET 0 ROWS FETCH FIRST 20 ROWS ONLY;
detailed list 10
Now? , With the help of ORDER BY…OFFSET…ROWS FETCH FIRST…ROWS ONLY, You can provide a way to scroll or page through the hall of fame .
take TOP And INSERT,UPDATE,MERGE or DELETE Use it together
You are not encouraged not to use TOPwith and ORDER BY, This is actively prohibited in some cases , It seems strange . also SELECT Statement ,DELETE,INSERT,MERGE and UPDATE Every sentence has a TOP Clause . Compared with SELECT, You can't be associated with ORDER BY Clause . Let's look at this example .
DROP TABLE IF EXISTS #tempCustomer; --in case it exists SELECT Customer.CustomerID, Customer.PersonID, Customer.StoreID, Customer.TerritoryID, Customer.AccountNumber, Customer.rowguid, Customer.ModifiedDate INTO #tempCustomer FROM Sales.Customer --just for the test UPDATE TOP (10) #tempCustomer SET #tempCustomer.AccountNumber = Replace(#tempCustomer.AccountNumber, 'AW', 'PF') OUTPUT Deleted.CustomerID, Deleted.AccountNumber AS before, Inserted.AccountNumber AS after
detailed list 11
Now try to add a ORDER BY Clause ！ It won't allow . As described in the document ：
“ In the line of reference TOP Express the use of INSERT,UPDATE,MERGE or DELETE Not set in any order ”.
No , You have to do something similar .
UPDATE #tempCustomer SET #tempCustomer.AccountNumber = -- Replace(#tempCustomer.AccountNumber, 'AW', 'PF') OUTPUT Deleted.CustomerID, Deleted.AccountNumber AS before, Inserted.AccountNumber AS AFTER FROM ( SELECT TOP 10 CustomerID FROM #tempCustomer ORDER BY #tempCustomer.CustomerID DESC ) AS ordered WHERE #tempCustomer.CustomerID = ordered.CustomerID GO
detailed list 12
Again ,INSERT Statement . We can't use it to TOP Insert rows in a meaningful chronological order . As the book says ：
“TOP When used with INSERT, The referenced rows are not in any order , And it's time to ORDER BY Clause cannot be specified directly in this statement .”
If it is necessary to do so , You have to TOP And ORDER BY Use with the clauses specified in the sub selection statement .
DELETE There is one TOP Clause , But we can't use it either . What if you want to clear the old purchase order details ？ You need to be sure to clear the oldest first . We can't ORDER BY stay delete Put in a sentence , But we don't have to .
Let's set up the test .
DROP TABLE IF EXISTS #tempPurchaseOrderDetail; --in case it exists SELECT POD.PurchaseOrderID, POD.PurchaseOrderDetailID, POD.DueDate, POD.OrderQty, POD.ProductID, POD.UnitPrice, POD.LineTotal, POD.ReceivedQty, POD.RejectedQty, POD.StockedQty, POD.ModifiedDate INTO #tempPurchaseOrderDetail FROM Purchasing.PurchaseOrderDetail AS POD
detailed list 13
Now? , We delete the ten oldest purchase order details .
DELETE FROM #tempPurchaseOrderDetail OUTPUT Deleted.DueDate, Deleted.LineTotal, Deleted.PurchaseOrderID WHERE PurchaseOrderDetailID IN ( SELECT TOP 10 PurchaseOrderDetailID FROM #tempPurchaseOrderDetail ORDER BY DueDate ASC ); GO
detailed list 14
that ,TOP If you can't use , or DELETE, What's the point of having this filter ？ ok , actually , It can be used without eventually deleting a specific recordset in a specific order .INSERTMERGEUPDATE
If you need to delete many lines from the production system on a regular basis, for example , Then use TOP Without a filter it will ORDER BY Save lives . Deletion will be recorded , It may also lead to lock escalation . I used to have to design a system , The system regularly starts from SQL Server Clear a million rows from the database . The best way to eat an elephant is to bite a lot of things continuously , It's not a single bite .
We can easily illustrate this , Even before you had a working system , You won't see the advantages of it , Especially in deleting , to update , The system that needs access to the table when inserting or merging . Again , We'll use a temporary table to illustrate this , To avoid interference AdventureWorks Normal operation of .
DROP TABLE IF EXISTS #tempPurchaseOrderDetail; --in case it exists SELECT POD.PurchaseOrderID, POD.PurchaseOrderDetailID, POD.DueDate, POD.OrderQty, POD.ProductID, POD.UnitPrice, POD.LineTotal, POD.ReceivedQty, POD.RejectedQty, POD.StockedQty, POD.ModifiedDate INTO #tempPurchaseOrderDetail FROM Purchasing.PurchaseOrderDetail AS POD --we delete rows successively DECLARE @rowcount INT = 1 WHILE @rowcount > 0 BEGIN DELETE TOP (200) FROM #tempPurchaseOrderDetail WHERE #tempPurchaseOrderDetail.DueDate < DateAdd(YEAR, -2, GetDate()) SELECT @rowcount = @@RowCount END
detailed list 15
In the past , I've found that large-scale operations like this usually benefit from chunking , The size of chunks is a matter of fine tuning the operating system to make it right . For a job like this , stay TOP There is no clause ORDER BY in DELETE,INSERT or UPDATE You can make large-scale changes , One step in a short time is very valuable , A transaction processing system that works on a hard disk
TOP Statement SQL Server Clause SELECT Very useful and intuitive , But it allows you to leave out the associated ORDER BY Clause , To clarify your idea ：TOP In what way ？ After all , Your TOP Ten songs are not the loudest ten songs , It's not the top ten songs . In terms of record sales , They are the top 10 most popular records . You may accidentally get the right results in your development work , But in production , workload , Server and data size can cause queries to be optimized in very different ways , So different results .
A more general way to deal with things like this , I suggest using ORDER BY... OFFSET...FETCH stay SQL Server 2012 The grammar introduced in , Because it's more flexible and compliant . remember , Is better than TOP Filters are much harder .
- C++ 数字、string和char*的转换
- Won the CKA + CKS certificate with the highest gold content in kubernetes in 31 days!
- C + + number, string and char * conversion
- C + + Learning -- capacity() and resize() in C + +
- C + + Learning -- about code performance optimization
C + + programming experience (6): using C + + style type conversion
Latest party and government work report ppt - Park ppt
Online ID number extraction birthday tool
Field pointer? Dangling pointer? This article will help you understand!
GVRP of hcna Routing & Switching
- LeetCode 91. 解码方法
- Seq2seq implements chat robot
- [chat robot] principle of seq2seq model
- Leetcode 91. Decoding method
- HCNA Routing＆Switching之GVRP
- GVRP of hcna Routing & Switching
- HDU7016 Random Walk 2
- [Code+＃1]Yazid 的新生舞会
- CF1548C The Three Little Pigs
- HDU7033 Typing Contest
- HDU7016 Random Walk 2
- [code + 1] Yazid's freshman ball
- CF1548C The Three Little Pigs
- HDU7033 Typing Contest
- Qt Creator 自动补齐变慢的解决
- HALCON 20.11：如何处理标定助手品质问题
- HALCON 20.11：标定助手使用注意事项
- Solution of QT creator's automatic replenishment slowing down
- Halcon 20.11: how to deal with the quality problem of calibration assistant
- Halcon 20.11: precautions for use of calibration assistant
- "Top ten scientific and technological issues" announced| Young scientists 50 ² forum
- Reverse linked list
- JS data type
- Remember the bug encountered in reading and writing a file
- Singleton mode
- 在这个 N 多编程语言争霸的世界，C++ 究竟还有没有未来？
- In this world of N programming languages, is there a future for C + +?
- js Promise
- js 数组方法 回顾
- ES6 template characters
- js Promise
- JS array method review
- 【Golang】️走进 Go 语言️ 第一课 Hello World
- [golang] go into go language lesson 1 Hello World