T-SQL Why division by zero does not generated error in the order by clause

Question

I have found some statements in one procedure that I firstly thought are incorrect but after tested them I have convinced myself that they are working fine. What I am not able to understand is way.

I have a simple table with record id, pts and ptsOf columns:

DECLARE @DataSource TABLE
(
     RecordID TINYINT
    ,Pts   INT
    ,PtsOf INT
)

INSERT INTO @DataSource
VALUES (1,5,5)
      ,(1,7,8)
      ,(1,3,5)
      ,(2,5,0)

and what I need is to calculated the total score for each record using the following formula:

SUM(pts)/SUM(ptsOf) * 100

So, the statement above will generated the following error because for the last record I will have 5/0:

Msg 8134, Level 16, State 1, Line 21 Divide by zero error encountered.

But the statement that I have found has check for division by zero only in the select clause and no such in the order by clause:

 SELECT  RecordID
         ,CAST(CAST(SUM(Pts) AS decimal) / CASE SUM(PtsOf) WHEN 0 THEN NULL ELSE SUM(PtsOf) END * 100 AS decimal(18, 0))
 FROM @DataSource
 GROUP BY RecordID
 ORDER BY RecordID,  CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0)) ASC

Why the calculation in the order by clase does not generated error?

Below is the full example:

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         RecordID TINYINT
        ,Pts   INT
        ,PtsOf INT
    )

    INSERT INTO @DataSource
    VALUES (1,5,5)
          ,(1,7,8)
          ,(1,3,5)
          ,(2,5,0)

     SELECT  RecordID
             ,CAST(CAST(SUM(Pts) AS decimal) / CASE SUM(PtsOf) WHEN 0 THEN NULL ELSE SUM(PtsOf) END * 100 AS decimal(18, 0))
     FROM @DataSource
     GROUP BY RecordID
     ORDER BY RecordID,  CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0)) ASC

SET NOCOUNT OFF
GO

Answer 1

I believe that the second clause in the ORDER BY is simply ignored in this particular case. After all if you do this:

DECLARE @t TABLE(i INT PRIMARY KEY, x UNIQUEIDENTIFIER);

INSERT @t VALUES(1,NEWID()),(2,NEWID()),(3,NEWID()),(4,NEWID());

SELECT i, x FROM @t ORDER BY i, x;

x is not considered in the ORDER BY, and why should it be? The first entity in the ORDER BY clause already dictates the order, and the second clause can't change it. Since you're grouping by RecordID, SQL Server is smart enough to realize that the first element in the ORDER BY is unique, so it doesn't need to consider the second. I can't prove that, and I can make it fail when the second element is actually much more clear to SQL Server by using a constant, e.g.:

ORDER BY RecordID, CONVERT(1/0);

But when the output of the column is not easily known to SQL Server, and it can't do anything useful with the output anyway, it does the right thing (IMHO) and discards the expression without fully evaluating it and causing the runtime error. You can also make the error return if you don't first order by a column that is guaranteed to be unique:

ORDER BY CAST(CAST(SUM(Pts) AS decimal) / SUM(PtsOf) * 100 AS decimal(18, 0));