INNER JOIN

  • Comments posted to this topic are about the item INNER JOIN

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

  • Easy one for the day. Thanks Kapil:-)

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Nice and EZ

    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

  • SQLRNNR (3/11/2013)


    Nice and EZ

    +1

    😎

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

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

    while 1=1

    begin

    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

    FROM #TEST a

    INNER JOIN #TEST b

    on a.FirstDate = b.LastDate

    if @@ROWCOUNT>0 break;

    drop table #TEST

    end

  • Yes statistically low probability but rows *can* be returned if SYSDATETIME() and GETDATE() at a particular time are equal. The "correct" answer is not correct.

  • cppprogrammer (3/12/2013)


    Yes statistically low probability but rows *can* be returned if SYSDATETIME() and GETDATE() at a particular time are equal. The "correct" answer is not correct.

    +1. It might return a few rows if matched.


    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/

  • 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 which outputs only the last recordset containing matching FirstDate and LastDate:

    SET NOCOUNT ON

    WHILE 1=1

    BEGIN

    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 * FROM #t

    BREAK

    END

    DROP TABLE #TEST, #t

    END

    DROP TABLE #TEST, #t

    This script works up to several minutes on my local SQL Server (Core i5), finally displaying a result like this:

    FirstDate LastDate

    2013-03-12 10:56:26.983 2013-03-12 10:56:26.9830000

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

    --------------------------------------------------------------------------------------
    Hai Ton
    My Db4Breakfast blog.

  • Nice and easy start to the day - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • @evgeny Garaev. Nice attempt. Congrats. Today is yours.

  • The idea of this question is nice, but the execution is poor (i.e. "correct" answer is not correct) and the explanation is simply incorrect.

    As others have said, the chance is low, but this query might return a row. Maybe even a few. When I run the script and then look at the contents of the table, I see that sysdatetime() is not a true couinter of datetime2 values, it increases in leaps - 27 rows with LastDate = 2013-03-12 08:37:53.5392683, then 36 rows with 2013-03-12 08:37:53.5402684, etc. At one point, there may be a whole batch of rows with a value that happens to end in four zeroes.

    The explanation I expected would involve a link to http://msdn.microsoft.com/en-us/library/ms190309.aspx, about data type precedence. Without that, this behaviour is impossible to explain. Because of the rules of data type precedence, the datetime value (which has a lower precedence) gets implicitly converted to datetime2 (higher precedence). So the datetime value 2013-03-12 08:37:53.539 gets converted to datetime2 value 2013-03-12 08:37:53.5390000. Which, because of the way sysdatetime() works, does not occur in my data. (If sysdatetime() were more accurate, it's still not certain that there is a match, but the chance is a lot higher!).

    The reason I keep pounding those precedence rules is that there would definitely be matches if the data type conversion was done in the other direction, which can be seen by forcing it:

    SELECT DISTINCT a.FirstDate, b.LastDate

    FROM #TEST AS a

    INNER JOIN #TEST AS b

    ON a.FirstDate = CAST(b.LastDate AS datetime);

    This returns 4 rows on my data. Without the DISTINCT, it produces 9,232 rows.

    The explanation implies that sysdatetime() is not suitable for datetime and that getdate() should be used instead. This is nonsense. There is a tiny difference - based on my observations, it appears as if using sysdatetime results in actually rounding the datetime2 value to datetime, whereas getdate() truncates the time (always rounds down). So if exact timing matters, (a) you should not mix and match the two methods but stsick to the same one, and (b) you should probably reach out to a .Net datatype and use the .Net "Stopwatch" class, which is far more accurate than the T-SQL system functions.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • cppprogrammer (3/12/2013)


    Yes statistically low probability but rows *can* be returned if SYSDATETIME() and GETDATE() at a particular time are equal. The "correct" answer is not correct.

    +1

    Correct answer is "Dates which get matched will be retuned"

    e.g. 2013-03-12 09:03:50.9000000 is datetime2 and is the same as datetime 2013-03-12 09:03:50.900.

  • The No rows answer is a subset of Matching rows answer where number of matching rows is zero 😛



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

  • +1

Viewing 15 posts - 1 through 15 (of 57 total)

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