I work for a very large enterprise and I am wanting to know what is the best way to design t-sql for the stored proces to be used from an application?
Option 1 - Be sure to optimize your Database design so your application will only require at most 6 joins and used stored procs for queries using proper indexs to gain the most performance
Option 2 - Create Views and use stored procs against the views
1. What is the perfromance hit in using views. My thought process is that views should only be used when you know the data you want to return "hence the name 'VIEW', should not be used as a query".
2. Are stored procs against the schema tables the best for performance.
3. What is the best way to tune and ensure your stored proc is optimal with the correct indexes, so you build a lighting fast query?
View Complete Post