Of late, I have been using Twitter quite frequently, and I am gradually discovering its usefulness. I received a Direct Message (or DM in terms of twitter) asking if I can comment on the effect of normalization on Index and its performance in one twit! Now honestly speaking, this was new for me. I never expected to be quizzed like this. If you are using Twitter, then you must be aware that one twit contains only 140 characters. I was supposed to give an answer on such a big subject in just 140 letters. An interesting fact is that normalization and Index are not really closely related. The right question should have been – what is the effect of normalization on performance?
In any case, let us go over my twit answer.
“Normalization increases the count of the table along with clustered index which makes every single entity with its independent index improving perf.”
This whole scenario is very intriguing. I will try to give a comprehensive explanation. Prior to normalization, usually, data is in a large table. As it gets normalized, all the redundant information is removed and data is categorized and placed in their respective tables. It is a common practice to have PK in tables that lead to additional clustered indexes on the database. This means the normalized database has more clustered indexes. As there is no redundancy, each table contains a unique entry for data. Because of this, all the clustered indexes have fewer rows, which effectively increase the speed of database operations. Besides, as there is no redundancy, the normalized database requires less storage and maintenance. Now when the query is intricate and involves numerous tables, lots of joins are introduced. These join effectively diminish performance. However, this loss of performance is less compared to the gain which was a result of normalization.
In case you have skipped the above paragraph and straightaway reading this sentence, I urge you to go back and pay attention to the previous paragraph. This paragraph provides a good explanation to help you understand the logical backstage theory of normalization and performance relations.
“@pinaldave Normalization is a data modularization concept. Indexes are data storage and retrieval concept. ” .
Let me know what you think about this subject. If possible, participate here by giving your explanation within 140 characters. Let us see what you all can come up with. Follow me on Twitter.
Reference : Pinal Dave (https://blog.sqlauthority.com)
