INNER JOIN

Question

INNER JOIN

March 12, 2013 at 7:14 am

This was removed by the editor as SPAM

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

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

honza.mf SSCertifiable Points: 5519 More actions · Answer 1

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)

12013-03-12 10:29:41.9830000 7111 146637

12013-03-12 10:35:13.303000023179331321

12013-03-12 10:35:21.6170000 586 8312

12013-03-12 10:35:46.4770000 1731 24861

12013-03-12 10:38:40.373000012169173897

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

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

theboyholty Ten Centuries Points: 1050 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

(Bob Brown)

SSCrazy

Points: 2705

March 12, 2013 at 5:53 am

#1595923

Great question and easy. Thanks.

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 11

+1

😀

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

Dave62 SSCertifiable Points: 7459 More actions · Answer 12

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 More actions · Answer 13

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: 19799 More actions · Answer 14

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/