From: Desert Code Camp 2007
Speaker: Eric Kassan (from World Doc)
Systems will scale better if less code is in the DB Server because almost always 1 Database Server but can have many application or web servers.
Specify dbo. in front of table name to improve performance.
Use a temp variable instead of a temp table if it's a relatively small data set.
Using Stored Procs for database access and never accessing tables gives you:
- Enhanced security. Only need to grant access to user account to stored procedures and not to the tables.
- Abstraction. You can change the database structure but the application talks to the the stored procs so will not be affected by the table structure changes.
Keep it simple - if there are a lot of users then don't use triggers, cursors and extended stored procs.