SQL Server 2005 Do's and Don'ts for Developers

From:  Desert Code Camp 2007
Speaker: Eric Kassan (from World Doc)

Notes:

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.