My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/When is the MAX value not the TOP value
The new RSS feed is here
Have a look and try the following statements, and try and think what the expected results are. The key aspect is that the query does not match any rows, no object exists with id = -99
declare @maxValue datetime
set @maxValue = '1/1/1900'
select @maxvalue = max(crdate) from sysobjects where id = -99
select 'Using max' ,@maxvalue maxValue
go
declare @maxValue datetime
set @maxValue = '1/1/1900'
select top 1 @maxValue = crdate from sysobjects where id = -99 order by crdate desc
select 'Using top' ,@maxvalue maxValue
go
The results are as follows,
Test maxValue
--------- -----------------------
Using max NULL
Test maxValue
--------- -----------------------
Using top 1900-01-01 00:00:00.000
There are no rows returned so in the first instance @maxValue will NULL, however in the second the @maxValue will still be the default value
SELECT only assigns a value if a row is returned. With MAX a row is returned, no rows matching the criteria result in a row with a NULLvalue, however with TOP there are no rows so it can't return a row.
Depending on the behaviour you want you can achieve the NULL value with TOP if you use SET. This ensures you have consistency.
With SET if your subquery returns no rows then a NULL value will be assigned to your variable. i.e.
declare @maxValue datetime
set @maxValue = '1/1/1900'
SET @maxvalue = (select top 1 crdate from sysobjects where id = -99 order by crdate desc)
select 'Using top with SET' ,@maxvalue maxValue
go
Test maxValue
------------------ -----------------------
Using top with SET NULL
Tony has a post on the T-SQL Value assignment SET vs SELECT that deals with the other side of the situation when you have multiple rows in a sub query.
... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/When is the MAX value not the TOP value