I have a table which has lot of inserts, is it a good database design to create indexes on that table?

“B-Tree” stands for balanced tree. In order that “B-tree” fundamental work properly both of the sides should be balanced. All indexing fundamentals in SQL Server use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance. In order that we can understand the fundamental properly let’s try to refer the figure down.
If you see the first level index there is “2” and “8”, now let say we want to insert “6”. In order to balance the “B-TREE” structure rows it will try to split in two pages, as shown.
Even though the second page split has some empty area it will go ahead because the primary thing for him is balancing the “B-TREE” for fast retrieval. Now if you see during the split it is doing some heavy duty here:-
√ Creates a new page to balance the tree.
√ Shuffle and move the data to pages.
So if your table is having heavy inserts that means it’s transactional, then you can visualize the amount of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen.
So when you forecast that a table has lot of inserts it’s not a good idea to create indexes.