Hugo Kornelis (1/3/2011)
john.moreno (1/3/2011)
Hugo Kornelis (1/3/2011)
After reading your reply, I started to doubt, so I copied the script, pasted it in SSMS, changed it to work for SQL Server 2005Which is a bit of a problem -- 5% of the respondents said zero, zero. I image that most if not all of them were thinking 2000/2005, where that would be the correct answer. I noticed the syntax, but picked the right answer because I remembered someone pointing out this as a new feature in 2008, if not for that I would have thought it was a trick question and picked zero, zero.
Though I agree that it would have been better to include the version in the question text, or to use a more backward compatible syntax, I don't thnik this should affect the result of the question much. It's 2011 already; the first CTP for SQL Server 2011 has been out for quite some time already - I think it's safe to assume version 2008 when nothing is mentioned explicitly.
And this gets into what I didn't like about this question - it was needlessly tied to a specific version of SQL. One simple change (replace the insert values (),(),() with separate insert statements) would have made this question applicable to versions back to SQL 2000. A second set of minor changes (replace the table variable with a temporary table; replace the nvarchar with varchar) would have made this question applicable to versions back to SQL 6.5 - if not even earlier. (And yes - I did just try this code on versions 6.5 and 2000!) It wouldn't have compromised the point that the question was making at all.
I think that it's upon the QotD authors to attempt to write their questions to apply to as many versions of sql as possible (obviously, when writing about features specific to one version, this wouldn't apply). This question should have been applicable as written to at least SQL 2000.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes