September 11, 2018 at 6:57 am
looks like something else i need to read up on more,
cheers
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
September 11, 2018 at 7:05 am
Carlo Romagnano - Tuesday, September 11, 2018 2:55 AMGiles Sutcliffe - Tuesday, September 11, 2018 2:20 AMQueries as written return no data whatsoever, so none of the answers is correct, surely?You aren't correct! The query returns an empty resultset with the columns and types defined in the first answer.
On the contrary, Giles said is returns no data which is correct. It is an empty result set which means no data was returned. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2018 at 7:08 am
edwardwill - Tuesday, September 11, 2018 1:02 AMStewart "Arturius" Campbell - Tuesday, September 11, 2018 12:43 AMildjarn.is.dead - Tuesday, September 11, 2018 12:37 AMShouldn't the query read 'WHERE uc.UserID = 8'?That thought did occur to me as well.
It is still a good question, despite that.....How can it be a good question if it's a terrible question?
I would argue that it isn't a terrible question. It does have a typo but since none of the answers were that it returns no rows it wasn't a huge leap to assume Steve meant UserID instead of UserConfigKey. Or since there was only a single row in the table no where clause is even needed to demonstrate what he was getting at.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2018 at 7:10 am
Sean Lange - Tuesday, September 11, 2018 7:08 AMedwardwill - Tuesday, September 11, 2018 1:02 AMStewart "Arturius" Campbell - Tuesday, September 11, 2018 12:43 AMildjarn.is.dead - Tuesday, September 11, 2018 12:37 AMShouldn't the query read 'WHERE uc.UserID = 8'?That thought did occur to me as well.
It is still a good question, despite that.....How can it be a good question if it's a terrible question?
I would argue that it isn't a terrible question. It does have a typo but since none of the answers were that it returns no rows it wasn't a huge leap to assume Steve meant UserID instead of UserConfigKey. Or since there was only a single row in the table no where clause is even needed to demonstrate what he was getting at.
It might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.
September 11, 2018 at 7:16 am
edwardwill - Tuesday, September 11, 2018 7:10 AMIt might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.
The correct answer was given. If you simply either remove the where clause entirely or change it to UserID = 8 then correct answer is exactly what is returned.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2018 at 7:20 am
edwardwill - Tuesday, September 11, 2018 2:48 AMGiles Sutcliffe - Tuesday, September 11, 2018 2:20 AMQueries as written return no data whatsoever, so none of the answers is correct, surely?That's the option I was looking for.
Me too, and it would not be the first time Steve tricked us like that. However, I still do not see the difference between option 1 and option 2.
September 11, 2018 at 7:21 am
Sean Lange - Tuesday, September 11, 2018 7:16 AMedwardwill - Tuesday, September 11, 2018 7:10 AMIt might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.The correct answer was given. If you simply either remove the where clause entirely or change it to UserID = 8 then correct answer is exactly what is returned.
I realise it's only a bit of fun and there's no jeopardy, but the relaxed attitude towards correctness hereabouts is slightly alarming.
September 11, 2018 at 7:43 am
edwardwill - Tuesday, September 11, 2018 7:21 AMSean Lange - Tuesday, September 11, 2018 7:16 AMedwardwill - Tuesday, September 11, 2018 7:10 AMIt might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.The correct answer was given. If you simply either remove the where clause entirely or change it to UserID = 8 then correct answer is exactly what is returned.
I realise it's only a bit of fun and there's no jeopardy, but the relaxed attitude towards correctness hereabouts is slightly alarming.
Understand what you mean. In any other spot around this site you will find there is no such thing as being relaxed about correctness. But the QOTD is very laid back and often a bit off. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2018 at 8:25 am
As others have said running the SQL as written returns nothing, so option one is wrong. This part makes it wrong "and the Options column with 3 XML nodes."
Why don't we put these questions out as temp tables instead of permanent tables?
CREATE TABLE #UserConfig
( UserConfigKey INT IDENTITY(1,1) NOT NULL CONSTRAINT UserConfigPK PRIMARY KEY
, UserID INT
, IsActive BIT SPARSE
, IsSubscriber BIT SPARSE
, DefaultQuantity INT SPARSE
, Options XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
;
--We run this query
INSERT #UserConfig
(
UserID
, IsActive
, IsSubscriber
, DefaultQuantity
)
VALUES
(8, 1, 1, 5)
;
--and then this
SELECT
*
FROM #UserConfig AS uc
WHERE uc.UserConfigKey = 8
;
SELECT
*
FROM #UserConfig AS uc
WHERE uc.UserID = 8
;
Results as the question is written is the first result, I just wouldn't have assumed Steve meant to use USERID. If that was the case, why even include the .UserConfigKey column?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 11, 2018 at 8:30 am
edwardwill - Tuesday, September 11, 2018 2:48 AMGiles Sutcliffe - Tuesday, September 11, 2018 2:20 AMQueries as written return no data whatsoever, so none of the answers is correct, surely?That's the option I was looking for.
Same here 🙂
September 11, 2018 at 11:42 am
ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AMShouldn't the query read 'WHERE uc.UserID = 8'?
Yup seems like it. This returned the rows for me.
SELECT
*
FROM dbo.UserConfig AS uc
WHERE uc.UserID = 8
;
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 11, 2018 at 12:20 pm
Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AMQueries as written return no data whatsoever, so none of the answers is correct, surely?
That's not necessarily true, but there is a typo there with the implication that there's only one row.
September 11, 2018 at 12:22 pm
mike.sortino - Tuesday, September 11, 2018 7:20 AMMe too, and it would not be the first time Steve tricked us like that. However, I still do not see the difference between option 1 and option 2.
The sparse columns are not returned. The result set has 3 columns, not 6
September 11, 2018 at 10:19 pm
I like this question. If you include XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
This will not include the actual columns that have SPARSE as the type definition it seems.
But if you remove the XML COLUMN_SET FOR ALL_SPARSE_COLUMNS. You get all the columns returned.
This could be a way of Steve saying here is another reason to not use select *
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply