TOP vs Max/Min: Is there a difference?

  • Mike Byrd

    Ten Centuries

    Points: 1285

    Create code for Sales.SalesOrderHeaderBig is

    USE AdventureWorks2012Big
    GO
    CREATE TABLE Sales.SalesOrderHeaderBig(
     SalesOrderID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     RevisionNumber tinyint NOT NULL,
     OrderDate datetime NOT NULL,
     DueDate datetime NOT NULL,
     ShipDate datetime NULL,
     [Status] tinyint NOT NULL,
     OnlineOrderFlag dbo.Flag NOT NULL,
     SalesOrderNumber  AS (isnull(N'SO'+CONVERT(nvarchar(23),SalesOrderID,(0)),N'*** ERROR ***')),
     PurchaseOrderNumber dbo.OrderNumber NULL,
     AccountNumber dbo.AccountNumber NULL,
     CustomerID int NOT NULL,
     SalesPersonID int NULL,
     TerritoryID int NULL,
     BillToAddressID int NOT NULL,
     ShipToAddressID int NOT NULL,
     ShipMethodID int NOT NULL,
     CreditCardID int NULL,
     CreditCardApprovalCode varchar(15) NULL,
     CurrencyRateID int NULL,
     SubTotal money NOT NULL,
     TaxAmt money NOT NULL,
     Freight money NOT NULL,
     TotalDue  AS (isnull((SubTotal+TaxAmt)+Freight,(0))),
     Comment nvarchar(128) NULL,
     rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
     ModifiedDate datetime NOT NULL,
     CONSTRAINT PK_SalesOrderHeaderBig_SalesOrderID PRIMARY KEY CLUSTERED
     (SalesOrderID ASC));
    GO

    CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderBig_rowguid ON Sales.SalesOrderHeaderBig
     (rowguid ASC)
    GO
    CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderBig_SalesOrderNumber ON Sales.SalesOrderHeaderBig
     (SalesOrderNumber ASC)
    GO
    CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderBig_CustomerID ON Sales.SalesOrderHeaderBig
     (CustomerID ASC)
    GO
    CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderBig_OrderDate ON Sales.SalesOrderHeaderBig
     (OrderDate ASC)
    GO
    CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderBig_SalesPersonID ON Sales.SalesOrderHeaderBig
     (SalesPersonID ASC)
    GO

    Mike Byrd

  • steve.powell 14027

    SSC Veteran

    Points: 239

    Lynn Pettis - Tuesday, December 18, 2018 2:09 PM

    I can actually say that I have never used ORDER BY ordinal position in any code I have written.
    If I want to order by a column or set of columns I will explicitly specify the column(s).

    I would love to say I haven't used the ordinal position for ORDER BY, but I found instances (some linked tables and files for example) where I couldn't identify the column name so had to sort by a known data type (specifically a date) that I knew was in position x. For those instances, it's useful, but they are the exception, so yes, it needs to stay but used with caution.

  • This was removed by the editor as SPAM

  • rchantler

    SSCrazy

    Points: 2116

    Sean Lange - Tuesday, December 18, 2018 2:39 PM

    rchantler - Tuesday, December 18, 2018 2:16 PM

    Jeff Moden - Tuesday, December 18, 2018 1:12 PM

    Sean Lange - Tuesday, December 18, 2018 10:20 AM

    neil-560592 - Tuesday, December 18, 2018 4:56 AM

    Just tried:

    select EntityID, secondcol, thirdcol
    from dbo.Entity
    order by 1+2

    Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.

    It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.

    But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist.  My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use. 

    If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀

    Does deprecation ever happen?  I thought SQL collations were going to be deprecated years ago.  (maybe they have been??) in favor of Windows collations.

    Sure stuff gets deprecated every release. Sadly most of them get removed, which they should. I have never heard that sql collations would be deprecated. Where did you read that? I highly doubt that would happen anyway. SQL Server now runs on Linux. If they used on Windows collations instead of their own that would prove to be nearly impossible.

    If you google [deprecate SQL collation] you will get links, some of which point to SQL Server  Central about this.  These are from 10+ years ago.  One of those provides a further link to Microsoft, but upon current examination that page does not support the idea of deprecating SQL collations.  I think this idea was "in the wind" about 10 years ago but may have been abandoned.  As you say, Linux is a good argument for the continued use of SQL collations.

Viewing 4 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply