Today I was asked to answer some pre-interview questions in an email. This is a little different as the interviewer must assume I have google at my fingertips and could easily find the answers. I’m assuming the interviewer is asking for something more here.
I like “tricky” questions and trying to understand the underlying motivation and meaning for asking such a question. What is the interviewer trying to get at?
Since I would have had to type the answers anyway I’ve decided to put them into a blog post.
- Do you understand and/or have you worked in a clustered environment with SQL Server?
- Can you write an example of cursor query syntax?
- Explain the difference b/t a unique key and a primary key
- When writing a report for a customer, what methods would you use to come up with the business requirements?
1. Do you understand and/or have you worked in a clustered environment with SQL Server?
No, I have never worked in a clustered environment but have a basic understanding of why you’d use clustering and some common clustering techniques.
2. Can you write an example of cursor query syntax?
Off the top of my head, no, and here is why:
I’m a strong follower of the mentality that most if not all RDBMS database technologies are written with set based operations in mind and cursors violate a set based theory by facilitating sequential row by row access. The query execution engines, IO strategies, and database design principals are all based off of the theory of sets of data. Using cursors and a more iterative row by row approach solves problems in a way RDBMS’s are not meant to solve them.
Whenever I’ve been presented with a large data processing problem that made cursors seem like a perfect solution for I’ve always either taken the time to really optimize a set based solution, usually resulting in reading less rows and incurring less hardware resource costs, or bringing the large data set into a language like C# to process. So while I know what cursors do and have seen them in action I could not write the syntax without looking it up on BOL ( Books Online ) or MSDN because I’ve always recognized and sought the benefits of other solutions.
However I do realize that certain security scenarios, and maybe database level reporting scenarios, mean cursors are necessary and would feel comfortable working with them. My lack of in depth experience with them could be attributed to having an environment where hooking up a remote C# application or using something like xp_cmdShell to execute an .exe was always a possibility to process data in a more efficient way.
3. Explain the difference b/t a unique key and a primary key?
In conversation the two terms can often be used interchangeable without too much confusion. By definition a primary key must be unique so the two terms distinct definitions can be blurry. This is especially the case in single database server scenarios.
However in a clustered environment however these terms do take on different meanings. The key difference is that a primary key would be referring to a table only constraint that ensures data integrity in a single table while a unique key would refer to a cluster wide key so when a table is distributed across multiple machines you can still ensure data integrity and uniqueness of rows.
Usually you’d see the term primary key used in with some kind of auto incrementing column, usually an int that is managed by Sql Server. In a clustered environment usually you would see GUIDs being used or a separate key assigning service located in a place that all clustered servers can access. In that situation a primary key asserts data integrity constraints in a single instance of a table while a unique key would ensure data integrity constraints across the clustered environments tables.
4. When writing a report for a customer, what methods would you use to come up with the business requirements?
When a customer asks for trivial report functionality such as “Give me the sum of all orders entered by Jimmy” I don’t think there is any particular method for ensuring you’ve captured the requirements. Most of the time I’m already writing the SQL in my head by the time the customer finishes their statement: Select sum(price) from orders where orders.customername = ‘Jimmy’ group by price
For non-trivial and non-intuitive reports such as “I need to know the increase in average orders placed in July in response to our marketing efforts, including the months before and after our promotions, and an easy way to quantify the increase as well as the expected amounts compared to the last 2 years” my opinion is that you need an extremely iterative approach to accurately meeting the customers requirements.
When I say iterative approach I mean that I would try and include the customer during every stage of development of the report from initial alpha preview to final design and asking for feedback at as many stages as possible. I would try and keep the feedback cycle as short as possible and try not to over engineer or complicate the design at the beginning of the report’s development.
Many times the customer does not know what they want or how to explain it well to the developer making the report. This is usually because the customer has limited technology experience and may assume something she thinks “all programs ( or reports ) do” also the customer might not include various business rules or “insider information” that may only exist unconsciously in her head.
For example the customer did ask for a sales comparison, but did they ask for a graph or chart? Do they assume that any comparison type report? They may ask for a product summary for a promotion but they may intuitively know to exclude garden products for a winter promotion which a developer may not immediately grasp.
The most widely used solution to this problem is to be incessantly focused on getting an understanding of the most minute details and try ask as many questions as possible. Does the report need a chart? Bar or Line Chart? Are we excluding certain product categories? Are we using this report for future promotions? Should this report work with older promotions? Are we looking for percentage changes or actual sales figure changes or both?
In my opinion this is often not enough and must be supplemented with incremental feedback. The risk of missing a the target requirements or not including a feature often means spending a lot of time back tracking to implement those features and could lead to missing deadlines.
Overall it has been my own experience and the experiences of other authors and bloggers I’ve read that a projects initial requirements rarely reflect the final state of the software. Therefore although asking for details mitigates the risk for a project or a solution to not be reflective of the customers needs and requirements. You can never ensure, regardless of a technique, that your able to craft the perfect set of requirements. In my opinion iterative development and short feedback cycles are the the best way you can guarantee your final product or report matches your customers expectations without wasting time on backtracking or writing features your customer doesn’t need.
The story starts last week when the reddit user posted a 
