June 28, 2018 at 12:16 am
I told the all the possible answers, she dint like it. I even asked what exactly you are looking for, what are you trying to accomplish? She kept saying she wanted ordered results without ORDER BY clause. She said it was her favorite interview question 🙂 This was the first question, after 15 minutes of back and forth, i said we have to move on further when she ended the interview abruptly. Yes, i would not have taken the position even if she offered me one. Btw, it was for facebook.
June 28, 2018 at 3:33 am
Yes. It can actually be done rather reliably but MS will never support it. You need to do several things...
1. The table must have a Clustered Index that is keyed in the order of the return you want. Doing the same thing with a non-clustered index is not reliable because you could end up with a "merry-go-round" return. It's just the nature of non-clustered indexes.
2. You must not have a JOIN of any kind in the query.
3. You must have exclusive use of the table.
4. You must use a committed transaction isolation level.
5. You must use MAXDOP 1 to prevent parallelism, which could mess up the sort order.
6. You must use and index hint to force the usage of the Clustered Index. A more narrow non-clustered index will come into play if the criteria is right for it unless you specify the index hint to force the usage of the Clustered Index.
7. You must write the query in such a fashion that it will cause a range scan if not a full index scan.
8. The table should not be partitioned.
9. I don't know if compression will cause a problem or not but would recommend against it until you test it on multiple tables.
10. It has to be a rowstore table (Clustered Index).
11. The Clustered Index must be unique or ties could swap the rest of the return.
Your forgot couple more things:
12. The Clustered index must have 0% fragmentation;
13. No other tables must use extents used by the table;
14. All the pages used by the table must be in consequitive extents.
Should I mention - you have no control over extents placement?
And even then - there is no guarantee the engine will read the pages in their order of placement.
The last pages of the clustered index may be already in the buffer after the latest update/insert, so they will be returned first.
BTW, MAXDOP 1 cannot guarantee that other queries executed on the server in the same time won't affect the order of the pages being read. You need to :
15. have the server in SINGLE USER mode on top of it.
_____________
Code for TallyGenerator
June 28, 2018 at 5:16 am
ravikanth_b - Thursday, June 28, 2018 12:16 AMI told the all the possible answers, she dint like it. I even asked what exactly you are looking for, what are you trying to accomplish? She kept saying she wanted ordered results without ORDER BY clause. She said it was her favorite interview question 🙂 This was the first question, after 15 minutes of back and forth, i said we have to move on further when she ended the interview abruptly. Yes, i would not have taken the position even if she offered me one. Btw, it was for facebook.
I don't know how far you want to go with that, but it would be interesting to challenge her with this question on LinkedIn.
You can find her on LinkedIn, right?
With the right stuff carefully planted on the right spots of the fan - you could even get to the point when her position would be on offer.
I'd be thrilled to learn from the best.
_____________
Code for TallyGenerator
June 28, 2018 at 6:45 am
Jeff Moden - Wednesday, June 27, 2018 11:20 PMHeh... do you know how to get the current date and time using T-SQL??? 😀
Which one do you want? Server local or UTC?
June 28, 2018 at 8:54 am
ravikanth_b - Thursday, June 28, 2018 12:16 AMI told the all the possible answers, she dint like it. I even asked what exactly you are looking for, what are you trying to accomplish? She kept saying she wanted ordered results without ORDER BY clause. She said it was her favorite interview question 🙂 This was the first question, after 15 minutes of back and forth, i said we have to move on further when she ended the interview abruptly. Yes, i would not have taken the position even if she offered me one. Btw, it was for facebook.
Since she said the answer was "database agnostic", it would be interesting to find out what she thought (or possibly KNEW) the answer might be. If for no other reason, I probably wouldn't have told her that we need to move on even if I no longer wanted the job for some reason. I'd have said something like "Interesting... The cool part about database engines is the possibilities that exist that this wonderful community of professionals has been known to ferret out. Please forgive my ignorance on the subject but it's generally accepted that the only way to reliably guarantee the order of the return despite any possible changes to the underlying table schema or the indexes on that table is to actually use ORDER BY and let the engine determine if it will actually be used by the engine at compile time or not. How is it possible to guarantee a properly sorted return in such an engine agnostic fashion? What magic have you come up with that would allow such a thing?"
If you think about such a response, it demonstrates...
1. That you're patient.
2. That you have the necessary humility to understand that, as good as you may be, there are possibilities and that, like anyone else, you don't know everything (except for the possible mistakes of relying on the order of a heap or clustered index in the absence of parallelism, I can think of no way to accomplish such a thing in a guaranteed manner that would be database {engine} agnostic and know of no one else that would think it is possible) .
3. A willingness to learn even in (especially in) the face of adversity.
4. That you respect the knowledge of the person asking the question even if they may be or at least seem to be incorrect.
5. That you respect authority even if you think it may be incorrect (this doesn't mean following blindly... it means that you understand that you have to work on a team and that the leader of the team may not always have the time to explain the method or the madness. It's still our jobs to advise such leaders of the possible mistake but how that is done will make or break the team. Having a poison between the leader and anyone on the team is totally counter productive for the company that provides a paycheck to both).
6. It also provides the interviewer (whom you'd likely be working for or with) with the opportunity to demonstrate how they treat people and what their attitude is.
Last but not least, you might actually get an answer to a very interesting problem whether it was born in an interview or not. 😀
On the "respect authority" thing, I have some severe disagreement with some of the things that my management chain wants done (and some have nothing to do with SQL Server) and I let them know why I disagree and what the possible ramifications are. Ultimately, though, it's their shop and not mine and, provided that their wants or needs are ethical, moral, and will cause no harm to the data or the people that data may be about, I'll let them do it their way and will enthusiastically support them as if it were my own idea. A great example of this is that I know what the performance ramifications are of using XML for certain things and I've proven it to the people that want to use XML in such a fashion many times. While it's unfortunate that they don't understand the ramifications of the future,. it's their shop and they've made that decision. My job is to be ready to help them when their methods actually do become a problem because, ultimately, it's not about me or them or who is right or wrong... the only concern should be "what's good for the company".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2018 at 11:48 am
I spent more than 15 years working on Oracle/SQL Server/MySQL and Big Data/NoSQL in architecture and engineering. I myself interviewed may be over 100 candidates in my career and hired many phenomenal people. I invest at least 30 minutes a day to learn something new. This has been my habit for me for a long time, it is like brushing my teeth like a clock work. But if someone asks me a stupid question like to rate myself, i'd almost always say 6/10 because I think every time i learn new thing i realize the how much i don't know. Once i say i am 8 or 9, i am done learning (IMHO). The other day one interviewer asked me how do I build a SQL if I do not have GROUPING SETS feature. I explained and demonstrated how it works, he is not satisfied. Same guy asked me to rewrite an analytical function using "normal" SQL. We can play these games all day long but the point is to see how a person is approaching a problem rather than getting the answer right. In one interview i got syntax wrong for WINDOWING clause ROWS UNBOUNDED PRECEEDING and i told the interviewer i'd rather know how the feature and mechanism works than remembering syntax, i said I can always google it 🙂 . When i am working with Oracle, SQL Server, Java, Scala, python etc at the same time, i sometimes mess/mix up the syntaxes 🙂 Needless to say, i did not get the job because she got ticked off by my answer, she told me the same in the interview feedback. For the original question, i explained to interviewer how data is stored and retrieved in Oracle and SQL Server and under the hood optimizer behavior but she was not interested. I do not have a problem and I am humble enough learning the right answer because it is a very interesting question but disqualifying a candidate because of this question is outright stupid IMHO. The only hint I got from my wrangling match is "can you try and use MIN/MAX function?".
Enough said about this, just trying to vent 🙂
June 28, 2018 at 12:27 pm
ravikanth_b - Thursday, June 28, 2018 11:48 AMI spent more than 15 years working on Oracle/SQL Server/MySQL and Big Data/NoSQL in architecture and engineering. I myself interviewed may be over 100 candidates in my career and hired many phenomenal people. I invest at least 30 minutes a day to learn something new. This has been my habit for me for a long time, it is like brushing my teeth like a clock work. But if someone asks me a stupid question like to rate myself, i'd almost always say 6/10 because I think every time i learn new thing i realize the how much i don't know. Once i say i am 8 or 9, i am done learning (IMHO). The other day one interviewer asked me how do I build a SQL if I do not have GROUPING SETS feature. I explained and demonstrated how it works, he is not satisfied. Same guy asked me to rewrite an analytical function using "normal" SQL. We can play these games all day long but the point is to see how a person is approaching a problem rather than getting the answer right. In one interview i got syntax wrong for WINDOWING clause ROWS UNBOUNDED PRECEEDING and i told the interviewer i'd rather know how the feature and mechanism works than remembering syntax, i said I can always google it 🙂 . When i am working with Oracle, SQL Server, Java, Scala, python etc at the same time, i sometimes mess/mix up the syntaxes 🙂 Needless to say, i did not get the job because she got ticked off by my answer, she told me the same in the interview feedback. For the original question, i explained to interviewer how data is stored and retrieved in Oracle and SQL Server and under the hood optimizer behavior but she was not interested. I do not have a problem and I am humble enough learning the right answer because it is a very interesting question but disqualifying a candidate because of this question is outright stupid IMHO. The only hint I got from my wrangling match is "can you try and use MIN/MAX function?".Enough said about this, just trying to vent 🙂
Heh... understood, especially on the venting.
I was once asked on an interview where's the one place you wouldn't want to call a stored procedure even if you could, which implies that there's some place where you can't call a stored procedure, as well. To make a much longer story shorter, it turns out that the interviewer wanted the word "function". I didn't pass the interview because, contrary to popular belief, you can, in fact, call a stored procedure from a function using OPENROWSET. The stupid part was just as you experienced... the whole interview was based only on that question.
In a similar fashion at another interview, they wanted to know how I would resolve the issue of producing a zero sum aggregation for missing dates. I used a CROSS JOIN as a "Pseudo Cursor" to generate the necessary constrained (TOP) "presence of rows" along with the proper use of ROW_NUMBER() with an embedded (SELECT NULL) as the operand for the ORDER BY. I was told that the use of a CROSS JOIN was a bad thing to do and that using TOP in the CTE was as bad as using TOP in a view. I offered to explain and demonstrate the incredible performance and the total lack of READs compared to the Calendar Table method they were apparently seeking and they said they had to move on because there were other candidates waiting.
I was pretty happy that both of those interviewers turned their hands at how difficult it might be to work for them. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply