June 18, 2021 at 4:51 pm
Jeff Moden wrote:Developers routinely look to the DBA for advice about data modeling (often times after it's been deployed to production), but I don't get questions on the job about GETDATE() - because that's just too easy to Google.
It's easy to google, it's also very easy for developers to not appreciate the fact that getdate() includes a timestamp.
Ok... just to be sure... would you actually want to hire someone that claims to know T-SQL if you ask them how to get the current date and time using T-SQL and they HAVE to Google it?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2021 at 4:59 pm
I don't like the "what's a clustered index?" or other trivia questions. Not because the knowledge isn't important, but it wastes time. As Eric noted, I want to ask questions to see how they solve problems, think, ask me followups, etc. I can get to the point where I not only know if they can define a CI, but also if they know how to use it or when it matters.
Time is precious, so if you want exam answers, send them a timed quiz through the recruiter or drop them in a room. But really, I don't want to grade that crap. I want to know if this person is someone that will add value to the environment. Ask them about real situations or problems. See if they teach you anything or approach a problem in a better way. See if they bother to ask me for more information.
June 18, 2021 at 5:01 pm
If you want to have some fun with a Performance Tuning "Expert", provide them with the following questions... (there's more... these are a bit more advanced that "What's a Clustered Index"?
There's a table which contains both a PatientID and a DocumentID as well as other columns. A composite Clustered Index has been created on the PatientID,DocumentID combination.
3. What is the primary cause of fragmentation on an Insert-only Clustered Index that's keyed on a Random GUID column? Explain.
4. You have a Clustered Index based on an "ever-increasing unique value". The table has rows that are Inserted and then processed and Updated about an hour later. The table is experiencing a lot of fragmentation. What should you set the FILL FACTOR to and why?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2021 at 5:26 pm
I don't like the "what's a clustered index?" or other trivia questions. Not because the knowledge isn't important, but it wastes time. As Eric noted, I want to ask questions to see how they solve problems, think, ask me followups, etc. I can get to the point where I not only know if they can define a CI, but also if they know how to use it or when it matters.
Time is precious, so if you want exam answers, send them a timed quiz through the recruiter or drop them in a room. But really, I don't want to grade that crap. I want to know if this person is someone that will add value to the environment. Ask them about real situations or problems. See if they teach you anything or approach a problem in a better way. See if they bother to ask me for more information.
Yes, I totally agree. Time is precious. But if you don't ask the basic questions, you may be in for a nasty surprise after you hire the person and that's some really precious time.
Anyway, I'm not trying to convince you folks because "A man forced against his will is of the same opinion still". I'm just telling you how I interview Developers, DBAs, and "Tuning Experts" and have found that asking the basics very quickly mark the candidates' position on the Kruger-Dunning curve for me. If they get through the basics, and it's stunning how many don't, then I'll start with the other things that you speak of.
Asking the basics is also time well spent in evaluating their demeanor and other soft-skills, especially if it makes them a little uncomfortable.
And, just think about how much time asking "What's a Clustered Index" save me on that interview I spoke of with that supposed "Tuning Expert". 😀 Asking the basics will very quickly tell you the rest of the story and act as a segue into whether or not you want to waste any more time on the candidate or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2021 at 5:46 pm
Ask them about real situations or problems. See if they teach you anything or approach a problem in a better way. See if they bother to ask me for more information.
I agree, if someone has the right attitude they can fill in the details they don't know at present. If someone says they don't use some particular technique, then you want to know if they can provide a reasonable explanation of why that technique is inappropriate - as Dijkstra observed, it's not enough to not use GOTOs you have understand why they are a bad thing.
It's why I am very keen on understanding fundamentals, they help you to cut through the implementation noise to the core of the matter. Understanding the relational model is a far more long lasting skill than understanding the particular syntactic quirks of T-SQL - which you can always look up if needs be.
June 18, 2021 at 5:50 pm
would you actually want to hire someone that claims to know T-SQL if you ask them how to get the current date and time using T-SQL and they HAVE to Google it?
Well they need to know what the date and time are and happen to have forgotten the syntax - or they may be and excellent DBA but most of their experience is with Oracle or DB2 - 95% of the knowledge is transferable apart from the names of functions and the syntactic quirks of their scripting languages.
June 18, 2021 at 6:09 pm
would you actually want to hire someone that claims to know T-SQL if you ask them how to get the current date and time using T-SQL and they HAVE to Google it?
Well they need to know what the date and time are and happen to have forgotten the syntax - or they may be and excellent DBA but most of their experience is with Oracle or DB2 - 95% of the knowledge is transferable apart from the names of functions and the syntactic quirks of their scripting languages.
CURRENT_TIMESTAMP is an ISO Standard supported by Oracle and DB2 😛 so that's not an excuse.
June 18, 2021 at 6:30 pm
will 58232 wrote:would you actually want to hire someone that claims to know T-SQL if you ask them how to get the current date and time using T-SQL and they HAVE to Google it?
Well they need to know what the date and time are and happen to have forgotten the syntax - or they may be and excellent DBA but most of their experience is with Oracle or DB2 - 95% of the knowledge is transferable apart from the names of functions and the syntactic quirks of their scripting languages.
CURRENT_TIMESTAMP is an ISO Standard supported by Oracle and DB2 😛 so that's not an excuse.
+1000 there.
When I ask the question, I'm hoping that an almost burped and relatively inimaginative "GETDATE()" isn't the answer. I'm looking for someone that realizes that an interview is just like a job... it's a chance to communicate and it's a chance to explain options and maybe even teach but certainly do a little mentoring if necessary. What I'd like to see is the first thing that someone starts to answer the question is "It Depends... do you want local time which would be covered by <insert one or more answers here>, something based on UTC which would be covered by <insert one or more answers here>, something that is easy to migrate to other DBMS systems which would be covered by <insert one or more answers here>. Also, is there a memory restriction or a any restrictions on the level of temporal resolution and do you have a requirement where the use of direct date math is a must or not"?
Yeah... I've had people tell me that if I had asked a more imaginative question, so would be the answers. That would be a failure on my part because a couple of the things that I look for in candidate responses, especially for senior positions, actually are imagination and creativity.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2021 at 6:31 pm
CURRENT_TIMESTAMP is an ISO Standard supported by Oracle and DB2 so that's not an excuse.
Which would explain why they don't know about getdate() - thanks by the way, I prefer to use the standard rather than the MS specific things if I can - hence coalesce rather than isnull etc.
June 18, 2021 at 6:34 pm
Anyone yet to take me up on explaining why a relational DBMS is called "relational"? It's a pretty fundamental question for a DBA.
June 18, 2021 at 6:39 pm
Anyone yet to take me up on explaining why a relational DBMS is called "relational"? It's a pretty fundamental question for a DBA.
It just because data is related to other data.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2021 at 7:02 pm
It just because data is related to other data.
Codd called his invention relational because it is based on the mathematical construct of a relation. This represents a relation between sets. Take the sets {a, b, c}, {d, e}, a relation between the two sets is: {(a, d), (a, e), (b, d), (b, e), (c, d), (c, e)}. You might note that the relation itself is a set (and therefore cannot contain duplicates). Relations are (roughly speaking) called tables and views in SQL - rather misleading names. Codd's innovation was the introduction of named attributes rather than the meaning being dependent on position.
The candidate might not know that, but you might be interested in whether they said "that's just a load of academic nonsense" or if you have awakened their curiousity.
I mentioned closure. In arithmetic plus is closed over integers, the result of the addition of two integers is always another integer. Likewise in the relational model, the result of any relational operation (restriction, product, join, projection, union, intersect, minus, divide) is always another relation - logically there is no difference between a query and a table. This is one of the fundamental attributes of the relational model that makes it so powerful. Also you might note that a stored procedure doesn't return a relation that can be reused in this way, and therefore SPs aren't part of an RDBMS.
June 18, 2021 at 7:06 pm
any restrictions on the level of temporal resolution
Is there ever a business case for having the time expressed down to microseconds? I recommend reading Flash Boys - A Wall Street Revolt by Michael Lewis to answer that question.
June 18, 2021 at 7:34 pm
It just because data is related to other data.
Codd called his invention relational because it is based on the mathematical construct of a relation. This represents a relation between sets. Take the sets {a, b, c}, {d, e}, a relation between the two sets is: {(a, d), (a, e), (b, d), (b, e), (c, d), (c, e)}. You might note that the relation itself is a set (and therefore cannot contain duplicates). Relation are (roughly speaking) called table and views in SQL - rather misleading names. Codd's innovation was the introduction of named attributes rather than the meaning being dependent on position.
The candidate might not know that, but you might be interested in whether they said "that's just a load of academic nonsense" or if you have awakened their curiousity.
I mentioned closure. In arithmetic plus is closed over integers, the result of the addition of two integers is always another integer. Likewise in the relational model, the result of any relational operation (restriction, product, join, projection, union, intersect, minus, divide) is always another relation - logically there is no difference between a query and a table. This is one of the fundamental attributes of the relational model that makes it so powerful. Also you might note that a stored procedure doesn't return a relation that can be reused in this way, and therefore SPs aren't part of an RDBMS.
I read all of that a long time ago (and actually pointed a colleague to Codd's old paper on the subject about two weeks ago) . Despite the apparent complexity at the mathematical level that a lot of people delve into and sometimes even making a living, it still boils down to data related to other data. 😀
While I mostly agree with you at the root level about the point you make about stored procedures, I do remember an interview that I "failed" because I knew too much. It seems that the DBA was "too busy" to conduct an interview for the assistant DBA that he needed so desperately and so tasked someone else to conduct the interview. I blasted through all of the questions and the interviewer was thrilled. He said "Just one final question... what is the one place where you wouldn't want to use a stored procedure even if you could"?
What they were very specifically looking for was a Function... the reason why I got it wrong was because I HAVE actually used stored procedures in a function (the ol' OpenRowset trick) and for very good reasons. The DBA doing the interview didn't know you could do that and so I failed his "test". I didn't find that out until a couple of years later when I happened to overhear a couple of folks talking at a table in a bar that I frequented about database stuff and heard them mention the name of the company they worked for, which was the company that I interviewed at. When I introduced myself and explained how I knew a little about the company, the guy that seemed to be the "leader of the pack" for that group shook his head an apologized profusely. He said that I and one other fellow were the only ones that had correctly answered all of the other questions AND had also said they couldn't think of a place where they couldn't use a stored procedure. The test had been dismantled, only in-person interviews by a "qualified" DBA were now being conducted, they had to get rid of that other DBA, and they fear that they missed out on some great candidates because someone "didn't spend the time".
My point is that "learned opinions" and "rules" and a whole lot of "Best Practices" are just guides. If someone can explain something good that even violates those things, I'm all ears. And that's how I try to interview. Show me your stuff because I know for sure that "Great minds don't think alike or they'd be average". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2021 at 7:48 pm
Despite the apparent complexity at the mathematical level
The mathematical level is much simpler than SQL. The mathematical definition of a function is much simpler than the programming language definition - I understand what a mathematical function is - I remain unclear what one is in any programming language other than a strictly functional one.
I'm not a mathematician by the way - all the mathematics behind the relational model is simple enough for any person to understand - even someone like myself, whose first degree is in English Language and Literature.
A question from the Oracle SQL Fundamentals I Exam Guide:
4. An entity-relationship diagram shows data modelled into (choose the best answer)
A. Two-dimensional tables
B. Multidimensional tables
C. Hierarchical structures
D. Object-oriented structures
In an interview, should you give the right answer or the one expected by Oracle? If the interviewer doesn't understand why Oracle are wrong - perhaps you don't want the job.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply