SQL Server Naming & Formatting Conventions

When starting a new database, I prefer the following set of naming convention standards:

Avoid using Hungarian notation (or any other prefix / suffix abbreviated notation)

Hungarian notation was a bad practice Microsoft started in the days of limited memory. Microsoft now vehemently dissuades the practice. It is visually displeasing to look at, and can be confusing. Do not use any abbreviated prefix / suffix notation.

For instance, do not prefix stored procedure names with "sp" (for select), "isp" (for inserting) etc. Likewise, do not prefix table names with "tbl", or views with "vw". Any redundant prefixes / suffixes like these are absolutely unnecessary since developers use strongly-typed objects or have access to metadata. The developer should know if they are accessing a stored procedure, table, or any other database object. They do not need a prefixed name to tell them that.

In the same vein, I would not use "in" and "out" prefixes to denote stored procedure parameter direction. Such as: @in_activeflag, @out_weekendreadydate etc. Again, the developer should already know the direction of the parameters.

Also do not use "PkId" for a primary key column name. A primary key name (or any database object name for that matter) should be something meaningful like "CustomerId".

Only abbreviate words which have a 3 or more letter acronym equivalents

For instance, "EmployeeSSN" is okay, however "EmployeeID" is not. "ID" is not a two-letter acronym for identification; use "EmployeeId" instead.

On a related note, "Weekend" is one word and should not be represented as "WeekEnd" (unless it means the end of a week range and not the weekend).

Do not use SQL reserved keywords for column names

This one goes without saying. If you have to enclose your column name in brackets like "[Date]" then it needs to be renamed. Something like "CreatedDate" or "DateCreated" instead.

Use proper-case for database objects such table names, stored procedure names, function names etc. Do not use underscores to separate words.

For example, use "Customers" instead of "customers" or "custs" for a table name. And use "SelectAllCustomers" or "GetAllCustomers" instead of "sp_customers", "sp_custs", or "select_custs" for a stored procedure name.

Use proper-case for parameter names

For example, use @WeekendReadyDate instead of @out_weekendreadydate. Of course, this applies to your T-SQL function or stored procedure parameters. In your actual code (C# etc.) all of your method parameters would be camel-case.

Formatting Conventions

Here are some code formatting standards I prefer:

Always fully qualify objects (table names, functions, other stored procedure names etc.)

Do this so the SQL compiler doesn't have to incur the lookup call upon initial compilation.

from
    dbo.customers

Left-align keywords and place their right-handed code on indented separate lines

For example:

select
    EmployeeId,
    EmployeeSSN,
    FirstName,
    LastName

from
    dbo.Employees

order by
    LastName;

Use lowercase for SQL keywords

Microsoft recommends using uppercase for SQL reserved words, but I disagree. The practice was started way back before they had color-coded keywords. Nowadays we use a nice IDE which has color syntax highlighting for keywords and we don't have to use a monochromatic text editor where uppercase keywords would stand out. I find that all caps keywords to be visually distracting. It's more easy to look at lowercase keywords especially if they're already colored.

Terminate SQL statements with a semicolon

This makes it easier on the SQL complier to identify the begin and end of separate statements. It also makes it easier to identify a problem area when debugging compiler errors.

Use:

set nocount on;
declare @WeekendReadyDate datetime;

Instead of:

SET NOCOUNT ON;
DECLARE @WeekendReadyDate DATETIME;

Avoid using unnecessary horizontal white-spaces (i.e. do not right justify or tab-align multiple lines)

Again, keywords are color-coded. There's no reason to make use of superfluous white-spaces.

Don't do this:

DECLARE @WeekEndReadyDate   DATETIME;
DECLARE @HolidayInfo        CHAR(325);
DECLARE @WeekEndReadyFlag   SMALLINT;

Instead do this:

declare @WeekEndReadyDate datetime;
declare @HolidayInfo char(325);
declare @WeekEndReadyFlag smallint;

Microsoft is working on SQL Server Best Practices Analyzer (version 2.0 for SQL Server 2005) which is currently in private beta. The product will mainly provide setup / configuration / security best practice recommendations; however it will also eventually support T-SQL recommendation in the future. I would strongly suggest that you make use of this product when it is eventually released. Here is the link to the product team's blog: SQL Server Relational Engine Manageability Team Blog.

In addition, here are some external links which are useful: