Helps to maximize the use of clustered indexes, the most powerful and useful type of index available to SQL Server. The more data is separated into multiple tables because of normalization, the more clustered indexes become available to help speed up data access. Helps to reduce the total number of indexes in your database. Denormalization is the process of selectively taking normalized tables and re-combining the data in them in order to reduce the number of joins needed them to produce the necessary query results. While denormalization can boost join performance, it can also have negative effects.
Redundant data can lead to data anomalies and bad data.
In many cases, extra code will have to be written to keep redundant data in separate tables in synch, which adds to database overhead. As you consider whether to denormalize a database to speed joins, be sure you first consider if you have the proper indexes on the tables to be joined. It is possible that your join performance problem is more of a problem with a lack of appropriate indexes that it is of joining too many tables. Before you decide to denormalize a properly normalized database, be sure you thoroughly consider all of the implications and test performance both before and after you denormalize to see if your efforts have really bought you anything. Your first goal in the design process should be to normalize your data.