INNER JOIN

  • honza.mf

    SSCertifiable

    Points: 5519

    vk-kirov (3/12/2013)


    Evgeny Garaev (3/12/2013)


    Right answer is wrong in this case. Beacause some rows could be returned. This query will prove my words:

    Very good script, although it produces a huge amount of recordsets.

    This is an improved version...

    I changed the script to have some stat:

    SET NOCOUNT ON

    declare @start datetime2, @count int, @attempts int

    set @start = sysdatetime()

    set @count = 5

    set @attempts = 0

    WHILE @count>0

    BEGIN

    set @attempts += 1

    CREATE TABLE #TEST

    (

    FirstDate DATETIME,

    LastDate DATETIME2

    )

    DECLARE @i int

    SET @i = 100

    WHILE @i > 0

    BEGIN

    INSERT #TEST values (sysdatetime(), sysdatetime())

    SET @i = @i - 1

    END

    SELECT distinct a.FirstDate, b.LastDate

    INTO #t

    FROM #TEST a

    INNER JOIN #TEST b

    on a.FirstDate = b.LastDate

    IF @@ROWCOUNT>0

    BEGIN

    SELECT Count(*), LastDate, @attempts, datediff (ms, @start, sysdatetime()) FROM #t GROUP BY LastDate

    --BREAK

    set @count -= 1

    set @start = sysdatetime()

    set @attempts = 0

    END

    DROP TABLE #TEST, #t

    END

    with results on our develoment server (Sorry, friends)

    1 2013-03-12 10:29:41.9830000 7111 146637

    1 2013-03-12 10:35:13.3030000 23179 331321

    1 2013-03-12 10:35:21.6170000 586 8312

    1 2013-03-12 10:35:46.4770000 1731 24861

    1 2013-03-12 10:38:40.3730000 12169 173897

    The probability is low (hundreds or thousands of attempts) but nonzero.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • theboyholty

    Ten Centuries

    Points: 1050

    All those who said it was easy didn't think about it deeply enough.

    The question, although technically incorrect, raises questions that the vast majority of us wouldn't have considered so that makes it a valid question because it has taught most of us something today, albeit inadvertantly.

  • PMwar

    SSC Veteran

    Points: 299

    db4breakfast (3/12/2013)


    Evgeny Garaev (3/12/2013)


    Right answer is wrong in this case. Beacause some rows could be returned. This query will prove my words:

    end

    +2 😎

    I agree. So what I learned from this question is: a datetime can be equaled to datetime2 in a rare occasion.

    +3;-)

    PMWar

  • Thomas Abraham

    SSChampion

    Points: 10761

    Knew that none of the given answers was correct. So, just picked one to see what the"official" answer was. As others, most completely Hugo, have said, the query can return some rows.

    I appreciate the effort in the question, but think it could have been better implemented.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Great question and easy. Thanks.

  • Mike Is Here

    Hall of Fame

    Points: 3348

    First, Like others have said the question is subjective.

    Second, The answer of "Dates which get matched will be returned" is always correct because that is what an inner join does. So that answer is inclusive whether or not any rows are returned.

    Edit: You usually know if there is a problem with the question if you post before 8am EST and end up on Page 3.

  • Rich Weissler

    Hall of Fame

    Points: 3286

    mbova407 (3/12/2013)


    First, Like others have said the question is subjective.

    Second, The answer of "Dates which get matched will be returned" is always correct because that is what an inner join does. So that answer is inclusive whether or not any rows are returned.

    That was the logic I applied. *shrug*

  • kapil_kk

    SSC-Insane

    Points: 21316

    Hi all,

    Actually I read an article with a name Difference between Datetime and Datetime2 and surprised with the functioning of that so then I think of implementing that thing as my First QOTD.

    But as with the responses I got I am getting different different answer that astonished me, I have never thought that these things will get happen and my question seems to be to bad for all 🙁

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Rich Weissler

    Hall of Fame

    Points: 3286

    Actually I'd argue that it's still an excellent question, and certainly promoted learning. The only issue was a 'distractor' that was actually more correct than the intended correct answer. Personally, I still like the question quite a lot, and hope to see more!

  • honza.mf

    SSCertifiable

    Points: 5519

    kapil_kk (3/12/2013)


    Hi all,

    Actually I read an article with a name Difference between Datetime and Datetime2 and surprised with the functioning of that so then I think of implementing that thing as my First QOTD.

    But as with the responses I got I am getting different different answer that astonished me, I have never thought that these things will get happen and my question seems to be to bad for all 🙁

    Congratulations to your first QotD.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    +1

    😀

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • This was removed by the editor as SPAM

  • Dave62

    SSCertifiable

    Points: 6705

    kapil_kk (3/12/2013)


    Hi all,

    Actually I read an article with a name Difference between Datetime and Datetime2 and surprised with the functioning of that so then I think of implementing that thing as my First QOTD.

    But as with the responses I got I am getting different different answer that astonished me, I have never thought that these things will get happen and my question seems to be to bad for all 🙁

    No worries, your question has prompted learning so by that measure it is a good one. I look forward to seeing more!

    P.S.

    The last question of mine that posted a few weeks ago was very well received. I think every post in the discussion was positive. I kind of think it was a failure though because there was not one educational post. By that measure the question seemed pointless. So you have to decide if you want all positive posts with no learning or a little controversy with a lot of learning.

    Enjoy!

  • mtassin

    SSC-Insane

    Points: 23099

    mbova407 (3/12/2013)


    First, Like others have said the question is subjective.

    Second, The answer of "Dates which get matched will be returned" is always correct because that is what an inner join does. So that answer is inclusive whether or not any rows are returned.

    Technically I got this answer right.... but I think it should be flagged as wrong. The above shows it. If any datetime2 values match the datetime values we'll get a row back... the odds are small, but they do exist. I was looking at it and just thinking that the accruacy of the two would pretty much mean it never happens... but it can... just with very very very low frequency/odds.

    I want to give my point back...



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Kingston Dhasian

    SSCoach

    Points: 19794

    mtassin (3/12/2013)


    I want to give my point back...

    :w00t:

    Normally people ask for points, I think its first time somebody wants to give the points back.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 16 through 30 (of 58 total)

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