Forum Discussion
Using "isnull" in "order by" affects the result Why ?
I can't understand why the queries in this example produce different results.
drop table #test
GO
CREATE TABLE [#test]( [bName] [nvarchar](64) NULL,[seqNum] [int] NULL)
insert #test values ( 'Name1', 1),('1', 998),('2', 999)
declare @s1 nvarchar(200)
set @s1='='
select @s1=@s1+' '+ bName from #test order by seqNum
print @s1
set @s1='='
select @s1=@s1+' '+ bName from #test order by isnull(seqNum,999)
print @s1
set @s1='='
select top 3 @s1=@s1+' '+ bName from #test order by isnull(seqNum,999)
print @s1
Result : = Name1 1 2
= 2
= Name1 1 2
= 2
= Name1 1 2
------------------------------------
Microsoft SQL Server Developer (64-bit), Windows 10 Pro, Version 15.0.2104.1
1 Reply
NovakGA , you have a "quirky" query (that term do really exists).
The ORDER BY is a post-processor command and comes after building the result set; it has more or less effect on the result or lets say, it's more random.
See
SQL Server Logical Query Processing Order: Explained! - Simple SQL Tutorials
SQL SERVER - Logical Processing Order of the SELECT Statement - SQL Authority with Pinal Dave
Query Processing Architecture Guide - SQL Server | Microsoft Learn
