当前位置:网站首页>SQL prompt tutorial: top without order by in select statement (bp006)

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 .

Click to download SQL Prompt Official version

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

Summary

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 .

Trial download >>>

SQL Prompt Use the tutorial >>>


Want to buy SQL Prompt The original authorization , Or for more product information, please click 【 Consult online customer service 】

版权声明
本文为[roffey]所创,转载请带上原文链接,感谢