Friday, October 27, 2006

Halloween SQL Teaser

Try to guess without running the code which of the following 3 queries will return a value for maxvalue that is not null
Is it A, B or C?

--A
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT @maxvalue = MAX(crdate)
FROM sysobjects WHERE ID = -99
SELECT 'A' ,@maxvalue maxValue
GO

--B
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT TOP 1 @maxValue = crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'B' ,@maxvalue maxValue
GO

--C
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SET @maxvalue = (SELECT TOP 1 crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'C' ,@maxvalue maxValue
GO

Let me know if you were correct. I must admit I saw this code on Simon Sabin's blog
To understand why this happens read this Tony Rogerson article: T-SQL Value assignment SET vs SELECT

So what does this have to do with Halloween? Nothing really.

2 comments:

Ramnath said...

Hi,

We came to know about your wonderful website from blogexplosion. You have a useful blog with all the little infos on sql. Many of your entries are really interesting and I have bookmrked your site for referance.

Here is an invitation for you to participate at Webtips (http://webtips.blogsome.com), a place to share and discuss creativity and innovations, not to mention the little discoveries on web development and mastering. Its an upcoming blog community and we feel we have a long way to go. We are inviting you to contribute a weekly post at Webtips in the field of sql queries. If you feel you too have something to contribute and be a Proud Contributory Author (PCA) please join us. We look forward for your contributions.

If you have any queries please contact us at rabanerjee@gmail.com

Best regards,
Ramnath Asish Banerjee
(WebTips Admin)

Rob Farley said...

*phew* I got it right. :)