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.
- 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.
- 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.
- 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.