INNER JOIN

Question

INNER JOIN

Viewing 15 posts - 31 through 45 (of 57 total)

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

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 1

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 🙁

Thanks for the question.

Thanks Hugo, for the wonderful explanation as always.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

yes, I learned different new things from the feedback and interaction of the people......

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

thanks,

Yes, I will keep posting more new questions as its just a start for the way to MVP :-):-P

Thanks to all for your support

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

Sathishk Right there with Babe Points: 738 More actions · Answer 4

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
Rich Weissler (3/12/2013)
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!

+1

Its an excellent question Kapil; got to learn something new today... such type of questions and arguments are always needed to avoid/resolve any real-world issues...

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

Kingston Dhasian (3/12/2013)
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.

And nobody asks points back three pages!

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

mtassin SSC-Insane Points: 23099 More actions · Answer 6

Kingston Dhasian (3/12/2013)
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.

The "correct" answer is wrong... and I picked the "correct" answer. the post I quoted actually had the correct answer, which was one of the choices. I don't deserve that point.

--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]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

Hugo Kornelis (3/12/2013)
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.

Thanks Hugo for the feedback on my question.. Its always been a good for me to learn new things from you...

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 8

kapil_kk (3/12/2013)
yes, I learned different new things from the feedback and interaction of the people......

That's the spirit Kapil. This happens with every one when you post your first QOTD. Believe me or not this happened with me as well 🙂

~ 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

manik_anu SSCrazy Points: 2377 More actions · Answer 9

Nice question... If we change the last date and time into HH:SS:mm Then it will fetch all 100 records.....

I reached 100 points and question also fetching 100 records.... 😉

Manik

Learn fast as possible*******

Manik
You cannot get to the top by sitting on your bottom.

Venkat Jonnalagadda Mr or Mrs. 500 Points: 510 More actions · Answer 10

As soon as I saw the options,

My BRAIN: Its easy! There could be some rows returned based on the sysdatetime() entry like in case of '2013-03-12 11:25:21.290000' which remains same for datetime(truncation) and datetime2(rounding)

My GUT: Nah! Whats the chance!! May be the question isn't considering it at all...May be the question is just about ???

I know I am screwed once I start to think about the question and not the answer...:w00t:

I am awful at guessing answers since my childhood...never good with these multiple choice...

___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

Lokesh Vij (3/12/2013)
kapil_kk (3/12/2013)
yes, I learned different new things from the feedback and interaction of the people......
That's the spirit Kapil. This happens with every one when you post your first QOTD. Believe me or not this happened with me as well 🙂

:-):-P

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

Revenant SSC-Forever Points: 42467 More actions · Answer 12

kapil_kk (3/12/2013)
Lokesh Vij (3/12/2013)
kapil_kk (3/12/2013)
yes, I learned different new things from the feedback and interaction of the people......
That's the spirit Kapil. This happens with every one when you post your first QOTD. Believe me or not this happened with me as well 🙂
:-):-P

Keep them comin', Kapil. And thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Actually I rather like this question. It opens up an interesting bag of worms. Hugo's explanation shows people what they have to do to avoid silly errors (not mix the two types, not use getdate() with datetime2 or sysdatetime() with datetime), and that's probably news to almost everyone; Kapil's explanation covered part of that, and was correct as far as that went, so I don't think it was as bad as Hugo suggested - although anythiong explaining a wrong answer as right is certainly not perfect - and wouldn't want to discourage Kapil from submitting a few more questions like this.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 14

vk-kirov (3/12/2013)
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

Up to several minutes??!! On a Core i5??

What on earth have you done to it to make it go that slow? Or is it a massive load in the background that's causing it?

On my rather ancient (vintage 2006) core 2 T7200 laptop it runs in typically 80 ms, and I've run your script dozens of times and haven't yet seen it take as much as a second. Most i5 processors are quite a bit faster than core2 T7200 and even the slowest i5 (i5-520UM?) should only be twice as slow as my machine. Of course I have no backround load to worry about on my machine, as it's just a personal toy, so that might explain the difference.

Tom

Gillingham_Dave Newbie Points: 8 More actions · Answer 15

Ok I'm new to this stuff but when I ran the code to confirm, it returned 100 rows.

Select *

From #Test

Go

Row FirstDate LastDate

2013-03-12 18:27:09.7532013-03-12 18:27:09.7584698

2013-03-12 18:27:09.7602013-03-12 18:27:09.7594698