SQL Server Database Development Standards

Question

I have to develop database development standards for our organisation for SQL Server and any code that interfaces to it. The code used can be anything from .NET code to VBScript to SQL Server Jobs.

Does anyone have a good link for this kind of thing?

My quick list is follows:

1) Naming Conventions
-- Stored Procedures usp_AppName_SPName
-- Functions usf_AppName_SPName
-- Indexes IX_TableName_IndexName
-- Tables AppName_TableName
-- Views VW_Name

2) Allocation of permissions to roles, never directly to users or groups
3) Allocation of roles to groups, never directly to users
4) Use of minimal permissions
5) No inline sql in code, always use SP or Functions
6) Use of explicit transactions
7) Readonly transactions where applicable
8) Always use explain plans to ensure sql is performant.

What other things do we need to cover? I am sure that there are lots of things....

Answer 1

Since we are talking best-practices I'd throw in a few things to avoid:

9. avoid use of xp_cmdshell
10. avoid dynamic sql unless strictly necessary (such as for dynamic pivoting)
11. avoid cursors (if not on temp tables)

P.S. Btw - I am doing all of the above ;)

Answer 2

I found the following quite useful:

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx http://www.codeproject.com/KB/database/sqldodont.aspx