I came up with the idea for creating the list for SQL Server 2005: Best Practices for Database Design, Implementation, and Maintenance while I was studying for 70-431 - MCTS: SQL Server 2005. All of the best practices that you'll read in this list are actually from the book published by the Solid Quality Learning. Over time, I contribute more best practices based on my experience and collaborations with other database professionals. I work with databases everyday and this subject is very near and dear to my heart. I'm constantly tuning queries and profiling databases in need of optimatization on a daily basis. If you're reading this list, I hope that you find this list helpful and please feel free to add to it by responding via comments. I will consolidate all valid contributions to the best practices list. Please provide links to sites with best practices. I will add all valuable links on this subject from other sites.

The format of this list will evolve over time.

• Filegroup Design: Create at least one user-defined filegroup to hold secondary data files and database objects. Configure this filegroup as the default filegroup so that SQL Server will store all objects you create in this filegroup.
• To avoid disk contention, do not put data files on the same drive that contains operating system files.
• Put transaction log files on a separate drive from data files. This splits give the best performance by reducing disk contention between data and transaction log files.
• Put the tempdb database on a separate drive if possible, preferably on a RAID 10 or RAID 5 system. In environments in which there is intensive use of tempdb databases, you can get better performance by putting tempdb on a separate drive, which lets SQL Server perform tempdb operations in parallell with database operations.
• Password Policies: To get a secure SQL Server environment, you should use the options to check the Windows expiration policy for SQL Server logins and apply the local Windows password policy on them.
• Security Assignments: Security best practices dictate that you never grant permission directly to a user. Therefore, you should add a Microsoft Windows login to a Windows group and the Windows group as a login to SQL Server. You then add this group as a user in a database. Next, create roles in a database corresponding to various job functions, assign database users to the appropriate role. Finally, assign security permissions on objects in the database to the database role. It is assumed that for all examples regarding security, you are implementing security best practices.
• Try to steer clear of cursors: Avoid cursors whenever possible. Ideally, cursors should be used only for administrative purposes when a set-based solution is impossible to implement.
• Schema binding trick: An old trick that many DBAs use in a production environment is to create a view for each table that selects all columns in the table and specifies the SCHEMABINDING option. These views are never used with any application or by any user. The only purpose of the views is to prevent a DBA from accidentally dropping a table or a column within a table. This trick does not prevent a DBA from purposefully dropping a table because the DBA can also drop the view and then drop the table. But dropping an object on purpose that should not be dropped is a security issue.
• Using views to modify data: Although you can use views to insert, update, and delete data, views are almost never used for that purpose. Stored procedures are always never used for the purpose. Stored procedures are always a better option because you can more easily validate changes via stored procedures. Stored procedures are also more flexible.
• Recompilation: Stored procedures are compiled into the query cache when executed. Compilation creates a query plan as well as an execution plan. SQL Server can reuse the query plan for subsequent executions, which conserves resources. But the RECOMPILE option forces SQL Server to discard the query plan each time the procedure is executed and create a new query plan. There are only a few extremely rare cases when recompiling at each execution is beneficial, such as if you add a new index from which the stored procedure might benefit. Thus, you typically should not add the RECOMPILE option to a procedure when you create it.
• Code efficiency: Databases are built and optimized for set-oriented processes instead of row-at-a-time processes. When constructing stored procedures, you always want to suse the minimum amount of code that also minimize the amount of work performed.