For those who don’t know, SNAFU is a character in military patriotic cartoons created by Americans during the war. This gouging, due to natural idiocy, falls into catastrophic situations all the time and, as a rule, dies at the end of the series. True, in the next series he is again alive - in this sense, he can be considered the distant progenitor of Kenny from South Park.When recruiting people for the SQL server developer position, I was often submissive to how they answered questions. I was ready to tell them YES if I had not been saved by a small one-line task that my colleague suggested. It is amazing how much this task can produce in a single SQL string. And now the candidate already walks rake enthusiastically. A rake, as you will see, there are many. Of course, not a single person collected ALL possible rakes. But to show them all, I needed SNAFU.So, the task:SELECT * FROM Events where DT='2020/02/03'
- What did the author of the code want to do?
- Why can this not work as I wanted?
- How to fix it?
My conversation with SNAFU will go further.SNAFU: Obviously, the query selects data for one day.Me: And what's wrong with that?SNAFU: Well, there is an asterisk, it would be better to indicate the fields ...Me: And what is stored in the table, what do you think? And what type of field is DT?SNAFU: Events ... Time of events ... So datetimeMe: Well?SNAFU: (after deliberation) aah, so if there’s time, then the notes will be read only after midnightI: excellentWe have overcome the first two points and move on to the third.Me: How to fix it?SNAFU: where convert (varchar, DT, 102) = '2020/02/03' (Rake # 1 worked)I:But doesn’t it bother you that this can prevent SQL from using the DT index if it is?SNAFU: where DT between '2020/02/03' and '2020/02/04' (Rake # 2 worked)Me: Does it bother you that the data for the next number (midnight) will also be included here?SNAFU: where DT between '2020/02/03' and '2020/02/03 23:59:59' (Rake # 3 worked)I: Would you like to deal with a bank in which there is a second when there is money, which you contributed will be lost?SNAFU: where DT between '2020/02/03' and '2020/02/03 23: 59: 59.999' (Rake # 4 worked)I: Do you know that 23: 59: 59.999 will be rounded again until midnight?SNAFU:Exactly, there the granularity is 13-16ms. Then: where DT between '2020/02/03' and '2020/02/03 23: 59: 59.986' (Rake # 5 worked)Me: That is, if DT becomes smalldatetime or datetime2, will everything stop working?
I do not know why the transition to the moment below is so difficult. In reality, more than half of the candidates are stupidly desperate. Sometimes you have to prompt. But our SNAFU tortured a solution:SNAFU: where DT> = '2020/02/03' and DT <'2020/02/04'Me: Finally.SNAFU wipes sweat from his forehead. Now is the time for a control shot.Me: And what else is really bad here?SNAFU is shaking. His hands are shaking. The gaze wanders.Me: Is it February 3 or March 2?It seems to me that such tasks give understanding much faster than the idiotic "test tasks". In this case, SNAFU may be a good guy, but he worked little with temporal data, and it is better not to let him into the financial sector.