October 16, 2016 at 3:34 am
Comments posted to this topic are about the item Subqueries in VALUES()
October 17, 2016 at 12:03 am
It's a bloody shame that any of them work. It's just another thing for people to mess up things with, just like many folks already do with sub-queries in the SELECT list, correlated or not.
And, yeah... I got the answer right because I'm used to fixing similar problems with subqueries in the SELECT list. :sick:
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2016 at 3:01 am
I'd certainly be a little disappointed in anyone that did that rather than using a suitably named pre-extracted variable. Still, probably not crime of the century, just an educational opportunity...
October 17, 2016 at 3:56 am
In this case multiple rows are not returned in sub-queries because of the fact EmployeeID is PK of the table. All queries will work.
Igor Micev,My blog: www.igormicev.com
October 17, 2016 at 5:01 am
Igor Micev (10/17/2016)
In this case multiple rows are not returned in sub-queries because of the fact EmployeeID is PK of the table. All queries will work.
Look more carefully: two of these won't work. Queries that return multiple columns can't be bracketed and treated as a nameless unbracketed list of scalar values, which blows query 4 out of the water. A bracketed query represents either a single scalar or a transient table or view which has to be aliased to be used, not a bracketed list of scalars, so query 3 can't work.
Tom
October 17, 2016 at 5:18 am
Nice query, but one only has to check query 5 and query 4 to get the right answer, since only one answer option has 5 working and 4 failing. Since 5 obviously works and 4 doesn't that one answer option has to be the correct one.
However, I regard is as a almost always silly to write
INSERT INTO X VALUES (<list including bracketed single value statements>)
instead of
INSERT INTO X <SELECT statement delivering a rowset compatible with table X>
so the question includes 3 examples of not best practise.
But from a performance point of view, this not best practise is certainly no worse that using "a suitably named pre-extracted variable" for each required value, as suggested by call.copse, and certainly better than that from a performance point of view in any case where multiple rows are to be inserted and perhaps even for single row insert when two of the required list of acalar values have to come from different rows within a single table (not necessarily a base table).
Tom
October 17, 2016 at 7:49 am
TomThomson (10/17/2016)
Nice query, but one only has to check query 5 and query 4 to get the right answer, since only one answer option has 5 working and 4 failing. Since 5 obviously works and 4 doesn't that one answer option has to be the correct one.
Interesting approach to the solution. Good logic for taking tests with taking multiple choice questions.
October 17, 2016 at 8:35 am
Should also be noted that this is for SQL version greater than SQL Server 2005 which according to the microsoft documentation at https://msdn.microsoft.com/en-us/library/ms174335(v=sql.90).aspx states:
expression
Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.
that is, "SELECT" cannot exist inside of the VALUES clause. OR I am misreading that documentation. Also, does anybody still use SQL 2005 (apart from where I work...)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 18, 2016 at 2:40 am
TomThomson (10/17/2016)
Nice query, but one only has to check query 5 and query 4 to get the right answer, since only one answer option has 5 working and 4 failing. Since 5 obviously works and 4 doesn't that one answer option has to be the correct one.However, I regard is as a almost always silly to write
INSERT INTO X VALUES (<list including bracketed single value statements>)
instead of
INSERT INTO X <SELECT statement delivering a rowset compatible with table X>
so the question includes 3 examples of not best practise.
But from a performance point of view, this not best practise is certainly no worse that using "a suitably named pre-extracted variable" for each required value, as suggested by call.copse, and certainly better than that from a performance point of view in any case where multiple rows are to be inserted and perhaps even for single row insert when two of the required list of acalar values have to come from different rows within a single table (not necessarily a base table).
Fair enough, and I often would use INERT INTO. Sometimes using a VALUES clause makes sense though and from a readability / maintainability perspective you can then indicate intent through the naming of your variable. I'm developer mind you and for such queries I don't tend to need super performance, of course there are exceptions...
October 18, 2016 at 10:25 am
Thank you for the question. First time in a long time where the discussion responses were more interesting than determining the correct answer to the question - good job Steve 😎
October 25, 2016 at 2:44 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 25, 2016 at 9:19 am
Actually doing something like this to write an itvf. Can't capture a value to a variable in this case.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply