Adding a row to a column set

  • looks like something else i need to read up on more,
    cheers

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Carlo Romagnano - Tuesday, September 11, 2018 2:55 AM

    Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM

    Queries 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/

  • edwardwill - Tuesday, September 11, 2018 1:02 AM

    Stewart "Arturius" Campbell - Tuesday, September 11, 2018 12:43 AM

    ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AM

    Shouldn'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/

  • Sean Lange - Tuesday, September 11, 2018 7:08 AM

    edwardwill - Tuesday, September 11, 2018 1:02 AM

    Stewart "Arturius" Campbell - Tuesday, September 11, 2018 12:43 AM

    ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AM

    Shouldn'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.

  • edwardwill - Tuesday, September 11, 2018 7:10 AM

    It 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/

  • edwardwill - Tuesday, September 11, 2018 2:48 AM

    Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM

    Queries 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.

  • Sean Lange - Tuesday, September 11, 2018 7:16 AM

    edwardwill - Tuesday, September 11, 2018 7:10 AM

    It 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.

  • edwardwill - Tuesday, September 11, 2018 7:21 AM

    Sean Lange - Tuesday, September 11, 2018 7:16 AM

    edwardwill - Tuesday, September 11, 2018 7:10 AM

    It 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/

  • 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.

  • edwardwill - Tuesday, September 11, 2018 2:48 AM

    Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM

    Queries as written return no data whatsoever, so none of the answers is correct, surely?

    That's the option I was looking for.

    Same here 🙂

  • ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AM

    Shouldn'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

  • Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM

    Queries 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.

  • mike.sortino - Tuesday, September 11, 2018 7:20 AM

    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.

    The sparse columns are not returned. The result set has 3 columns, not 6

  • 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 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply