Denormalized table designs in normalized database?

Question

There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, Datetime and float. When asked for a reason for such denormalized table, we were told thats how the record record sets were provided by vendors and hence the mapping table has to be designed in that approach. In addition, table will be used for cross joining across other normalized tables and table will potentially grow to larger size. Currently there are few tables (with 375 columns) with similar design and average of 3 million rows or more on the same database. These existing denormalized tables are partitioned by date. No performance issues on these existing tables as they are not being heavily used yet.

Questions:

1. Other than saying it is a bad design, do you see any obvious issues with such design that can convince the developers to reconsider their designs strategy in a normalized environment. Of course Normalized versus Denormalized have their own pros and cons.
2. In your experience, have you seen any common real time performance issues with such columnar tables such as locking, takes longer for DML etc? The developers even mentioned about considering MongoDB as their alternative if SQL Server shows any performance issues.
3. Do you have some high level performance issues that could possible occur if such tables are designed more often in OLTP. Please note that the company is flexible with adding CPU or memory if needed for any IO or CPU issues.

I know in SQL 2012,there is columnstore index but haven't explored yet.

Answer 1

Some issues to bring up:

• For OLTP, inserts will be really slow in a table that wide

• You will be wasting a lot of space by repeating redundant information

• Columnstore is a non-modifiable index type so you can't use it in an OLTP environment

• You greatly complicate referential integrity controls this way. You can't just make foreign keys to make sure you are getting valid values for fields.

• Indexing will be a nightmare

The real issue here is the developers not understanding design.

Keeping the client data in it's native format is FINE. I do this kind of thing for a living, and I get tables with 500+ fields all the time. The way to handle it is to separate your RAW data from your BUILT data.

If the client gives you a massively wide table, you need to normalize it yourself to make a usable data set. There's nothing stopping you from creating a process that breaks out that data into appropriate tables.