I have a question: write a query which involves complex calculation for that you need temporary object to hold the data which will be used for aggregations .this data has following nature.
it has 1 million rows
it has one unique column which can be used to identify the row
this data will be used only twice for calculations
Which one of the following table expressions is best suitable for this requirement?
a) table value user defined function b) common table expressions c) temporary table d) table variable.
Can you please tell which option need to choose in a,b,c,d and please tell me valid points on it.Thanks
I like the youtube serie of Brent Ozar "How to Think Like the SQL Server Engine". He explains very well what the pro's and con's of your options are.
Based on 2012 and your requirements I cannot tell you what the best options are. I don't know how much CPU and memory your server has etc etc. 1 million rows is much for Excell but for SQL it shouldn't be any issue and if it's only used twice...
Complex calculations means for me that I would prefer the best readable statement I can write so I can easily understand and support/explains it to the users and make documentation that is understood by co-workers and key-users.
Is this real world scenario you are facing at work or is this some school assignment?
This is definitely an interview or test question. I don't provide answers for such things but I will help you think about it cuz we're all in this together. This question is based on the characteristics of the items in the answers and it's an excellent question that covers a lot of ground that's actually very simple if you know which questions to ask about the answers (a test of if you know how to analyze things) and tests how well studied you are through documentation, forums, and experience.
First, the question clearly states that you need a "temporary object" to hold the data for aggregations. So the first thing is that you have to know which of the 4 given answers are temporary and which are not to begin the process of either elimination or qualification.
The next thing is they tell you how many times you need to query the data and "the same way" is kind of implied here. Is there anything in the answers that have a problem with such "reuse"? Eliminate those.
Last but not least is the information about the number of rows. Is there anything in the list of answers where the "Best Practice" (which also does have a bit of "science" to it in this case) is to use only for a limited number of rows? Eliminate those and the only thing remaining will be the correct answer.
Then ask yourself why you didn't know these things so that you could answer this question on your own and fix THAT problem so that you can be a better DBA or Developer in the future whether you get this job or pass this test, or not. And, no... that's NOT meant in a mean or snarky way. I'm trying to help you learn how to think and things to study for the next interview or test 
.
The bottom line is that you have to look at the answers and compare them to the qualifications in the question to see which answers can be qualified or disqualified. Again, but in a slightly different form, you have 3 basic questions to answer about 4 different items.
- Which of the items are or are not temporary?
- Which of the items are good or not good for reuse in a query?
- Which of the items are good or not good for use with larger numbers of rows?
Since they didn't say to select all that apply, that implies that there's only one correct answer and the process of elimination is the easiest. Any item that fails even one of the questions can easily be eliminated. There's no "judging" required.
If you don't actually know the answers to those 3 questions, then go look at the documentation on the 4 items in the answers (which also means trying to find people's opinions, which may not be in the official documentation) so you know them for the future.
1 Like
@srinivas ,
So, how did it go? Did you get the answer correct?