July 11, 2011 at 11:04 am
Nice question and i got it right 🙂
Amol Naik
July 11, 2011 at 11:21 am
At first, I thought it was a question about the syntax introduced in SQL 2008 that allows you to insert multiple records with a single insert statement, like so:
Insert Into @Foo
Values('A'),
('A'),
('B'),
('C'),
('C'),
('C'),
('B'),
('A')
(This syntax gives the same result that a 'UNION ALL' insert statement would.)
But, when none of the answers seemed to fit my initial theory, I took a closer look and saw what was going on.
Great question...exactly the right amount of trickiness to make you take a hard look at things!
July 11, 2011 at 11:27 am
nice question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 11, 2011 at 1:03 pm
Great question! I was pretty sure that it would work, but decided to run it anyway. It was a good idea to specify server version because, when I ran the code in SQL 2000 it complained about this line: "declare @Foo as table (a char(1))". I am not sure why, because, I thought, table variables were introduced with version 2000.
July 11, 2011 at 1:14 pm
BenWard (7/11/2011)
nice to see people properly dry running it! when I was at college 5/6 years ago the teachers were allways banging on about dry running code and none of the other students could be bothered to work out how to do it.
Nice question, but it did seem to be more about UNIONS than if you can use Case and sum together.
Used to be a lot of QOTD authors and posters would call dry running code cheating.
When writing code around finances especially - bothering to think logically about your program before you just go ahead and compile it can mean the difference between everything running fine and your company getting investigated over tax problems!
Agreed. Being able to show that code was actually tested prior to release can also keep those investigations by the SEC, FDA, TSA, Homeland Security, etc... from having unhappy endings.
July 11, 2011 at 1:24 pm
Enigma475 (7/11/2011)
Great question! I was pretty sure that it would work, but decided to run it anyway. It was a good idea to specify server version because, when I ran the code in SQL 2000 it complained about this line: "declare @Foo as table (a char(1))". I am not sure why, because, I thought, table variables were introduced with version 2000.
They were intorduced in SQL 2000. Inserting a select list, or the output of a stored procedure into a table paramter was not supported in SQL 2000. That should not have caused your SQL 2000 server to error on the Parameter Declaration.
http://msdn.microsoft.com/en-us/library/aa258839(v=SQL.80).aspx
July 12, 2011 at 2:45 am
Good question, made me think as the obvious answer seemed to obvious... It's the little things that trip us up. 🙂
July 12, 2011 at 4:13 am
This question got me off guard! Temporarily forgot the difference between UNION and UNION ALL 🙂
Kwex.
July 12, 2011 at 5:27 am
Glad I read it carefully and considered UNION vs UNION ALL.
http://brittcluff.blogspot.com/
July 12, 2011 at 9:25 am
After a string of straight-forward tests of knowledge, I'd forgotten to consider the possibility of a "trick" question and skimmed through the UNIONs as mere test-bed setup for the "real" question on SUMming values filtered by case. Oh, well.
July 14, 2011 at 8:26 am
thanks for the good question but informative one
July 15, 2011 at 2:36 pm
thank you for the question, it is useful
Iulian
July 21, 2011 at 8:53 am
Yaaay.... got one right at last !
😀
July 21, 2011 at 9:18 am
david.moule (7/21/2011)
Yaaay.... got one right at last !😀
GO YOU!!
well done bud 🙂
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
August 5, 2011 at 1:28 am
Ahhh! What a trick question, I was so focused on the sum/case part so i didn't even see the UNION part. Personally i think that UNION should have been implemented the other way around, I think UNION should get all values when nothing is specified and you should need to specify UNION DISTINCT to get the distinct values. To many developers are using UNION where UNION ALL should have been used (because all records are distinct), and they loose performance without knowing why.
To bad its not possible to change the way UNION works! 🙁
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply