While going through interview , My Interviewer asked me a question about Denormalization of tables and its usage in applications.

On guess, I answered, Yes it might be. Being denormalized , you have all columns in a single table where you do not require any kind of joins so your biggest plus point will be performance if proper look up conditions are given. And you can use a denormalized table in Reportings.

Is it correct, what I answered?

  • 3
    This is not a very useful question as stated because it seems to be asking for opinions, about projects, and it's about an interview you had - a good answer that is useful to you would have to know something about the interviewer and what they were trying to learn about you. This doesn't make for a very useful post for anyone else on this site, unless they are also interviewing for the same position with the same person. – Aaron Bertrand Oct 11 '13 at 16:21
  • Typically a 3NF DB doesn't slow down lookups much but sometimes it can be reasonable to denormalize. A phrase I've heard, and like to repeat, is "Normalize until it really hurts, then de-normalize until it works" – Matthew Oct 11 '13 at 16:22
  • 1
    I'm not going to iron fist this one but when it gets closed I suggest you re-word it to ask about pros and cons of denormalization without mentioning interviews / projects or whether your answer was "correct." Or just read about this information. All 4 links were in the top 10 Google results for "denormalization in SQL Server" BTW. – Aaron Bertrand Oct 11 '13 at 16:25
  • @AaronBertrand , thats what I was looking for. I had this article once, but was not getting where I had read that. Thanks for reminding. – Amit Ranjan Oct 11 '13 at 16:33
  • 1
    There is a good question buried in there. The problem is that right now it's stated as asking about the topic as a whole, about which I'm sure entire books have been written (AKA it really "does depend"), so it's not a good fit for this format. If the question was about a much more specific situation, we could certainly tackle that. – Jon Seigel Oct 11 '13 at 16:49

Actually the performance can be slower when you have to update the data, which is why denormalization is not normally used as default in OLTP systems.

OLAP - reporting - uses them as well as precalculated aggregates because updates do often not happen in real time but batched and query performance can be paramount.

But for anything transactional - denormalization is not advisable as default approach in application development.

By the way, unless you do reporting denormalization can make your data access slower because while you do not have a join, the table may be a LOT bigger, thus requiring more RAM or more IO budget to answer the same query.

  • 6
    Looks like your z and y keys have got muddled! – Martin Smith Oct 11 '13 at 16:27
  • No totlly. switching german and polsh keyboards at the moment every couple of minutes. – TomTom Oct 11 '13 at 16:32
  • Why all the downvotes? This is a good summary of some of the reasons to use or avoid denormalization. – Jon of All Trades Oct 11 '13 at 17:22
  • 2
    @JonofAllTrades Yeah that is weird. There's nothing wrong with this answer other than the y-z key-switch. Which is obviously a typo problem and easily fixed. – RBarryYoung Oct 11 '13 at 17:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.