One large table or split into two smaller tables?

Is there any performance benefit to split开发者_开发技巧ting a large table with roughly 100 columns into 2 separate tables? This would be in terms of inserting, deleting and selecting tasks? I'm using SQL Server 2008.

If one of the fields is a CLOB or BLOB and you anticipate it holding a huge amount of data and you won't need that field very often and the result set will transmitted over a long pipe (like server to a web-based client), then I think putting that field in a separate table would be appropriate.

But just returning 100 regular fields probably won't tax your system so much as to justify a separate table and a join.

The only benefit you might see is if a number of columns are only occasionally populated. In which case putting those into their own table and only adding a row when there is data might make sense in terms of overall row overhead and, depending on the number of rows, overall page count for the table(s). That said, this is one of the reasons they introduced sparse columns in SQL Server 2008.

For the maintenance and other overhead of managing two tables instead of one (especially given that people can act on individual tables if they choose), it's unlikely it would be worth it.

Can you describe what type of entity needs to have over 100 columns? Perhaps the data model is just wrong in the first place.

I would say no as it would take more execution time to join the 2 tables whenever you wanted to do something.

I depends if you use these fields in the same time in your application.

These kind of performance improvements are really bad : you make your source code impossible to understand. If you have performance trouble with this table, add something (like a table containing the 15 fields you'll use in a request that'll updated via trigger), don't modify your clean solution.

If you don't have performance problem, don't do anything, you'll see later !