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

I have to take issue with your first item right off the bat. While I know a lot of people like to use prefixes for stored procedures, tables, and the like, I've never had much use for that convention. When you start to get a lot of stored procedures that all start with "usp_", and you click to the expand the "Programmability\Stored Procedures" folder in Management Studio, it can be rather unwieldly to navigate.

Instead, require a prefix to match the logical feature set/functional group. What those prefixes are will vary by application or database. Then if you want to distinguish a stored procedure from a table, add your "_usp" requirement as a suffix.

For tables: you want something in your naming convention to distinguish between Application data (lookup tables) and User data.

Answer 2

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

1. avoid use of xp_cmdshell
2. avoid dynamic sql unless strictly necessary (such as for dynamic pivoting)
3. avoid cursors (if not on temp tables)

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

Answer 3

I found the following quite useful:

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

Answer 4

Also consider using multiple schemas. Use AppName.TableName instead of AppName_TableName, where AppName is a schema. The AdventureWorks sample does this, for instance.

Answer 5

Aren't roles and groups the same thing in SQL Server?

A few others...

1. Avoid using UDFs in WHERE clauses

2. Disallow direct SQL in applications (always use SPs)

3. Use comment blocks in front of views/procs/functions including a revision history and/or revision date
4. Use ANSI join syntax
5. Limit use of triggers, especially for replicated tables