Quite frankly let me share with you that i learned T-SQL programming from one of the worst database design which was designed by me. I never realized my database design is going to work for me or not/...what i did was started with development and created bunch of stored procedures, functions, views.... Every sproc having huge data processing logic, table joins, function calls, so i almost used all the syntax of T-SQL programming (including Transaction handling, temp Tables, Cursors, derived table, sub queries.....etc. ).
I never forget one of my sproc in which i used cursor inside cursor and two temp table for each cursors, and processing huge records in the cursor now you can imagine performance of my sproc. Anyhow first phase of my application was developed and deployed successfully.
Now Time to start with next phase....in the beginning i realized that the current database design is not going to support implementation of a small functionality, so what should i do..create new tables...maintain same data in multiple table..oh no..how bad design!!!!!!
Thank God it was not a customer project it was internal application for training purpose.
Then i started looking at my existing database design and started tweaking..... it took me around two weeks to stabilize my existing database ..changing the sprocs..code and making sure that existing functionality does not breaks.
At the end DB was stabilized...
Now let me make sure you do not make such mistakes....so here are some tips which you can take a look before you finalize your database design.
So guys SQL Server code review is very important...review you database design carefully..below are the steps which will help to ensure a good database design...