SQL Server - Schema/Code Analysis Rules - What would your rules include?

We're using Visual Studio Database Edition (DBPro) to manage our schema. This is a great tool that, among the many things it can do, can analyse our schema and T-SQL code based on rules (much like what FxCop does with C# code), and flag certain things as warnings and errors.

Some example rules might be that every table must have a primary key, no underscore's in column nam开发者_开发问答es, every stored procedure must have comments etc.

The number of rules built into DBPro is fairly small, and a bit odd. Fortunately DBPro has an API that allows the developer to create their own. I'm curious as to the types of rules you and your DB team would create (both schema rules and T-SQL rules). Looking at some of your rules might help us decide what we should consider.

Thanks - Randy

Some of mine. Not all could be tested programmatically:

• No hungarian-style prefixes (like "tbl" for table, "vw" for view)
• If there is any chance this would ever be ported to Oracle, no identifiers longer than 30 characters.
• All table and column names expressed in lower-case letters only
• Underscores between words in column and table names--we differ on this one obviously
• Table names are singular ("customer" not "customers")
• Words that make up table, column, and view names are not abbreviated, concatenated, or acronym-based unless necessary.
• Indexes will be prefixed with “IX_”.
• Primary Keys are prefixed with “PK_”.
• Foreign Keys are prefixed with “FK_”.
• Unique Constraints are prefixed with “UC_”.

I suspect most of my list would be hard to put in a rules engine, but here goes:

If possible I'd have it report any tables that are defined as wider than the bytes that can be stored in a record (excluding varchar(max) and text type fields) and/or a datapage.

I want all related PK and FK columns to have the same name if at all possible. The only time it isn't possible is when you need to have two FKs in the same table relating to one PK and even then, I would name it the name of the PK and a prefix or suffix describing the difference. For instance if I had a PersonID PK and a table needed to have both the sales rep id and the customer id, they would be CustomerPersonID, and RepPersonID.

I would check to make sure all FKs have an index.

I would want to know about all fields that are required but have no default value. Depending on what it is, you may not want to define a default, But I would want to be able to easily see which ones don't to hopefully find the ones that should have a default.

I would want all triggers checked to see that they are set-based and not designed to run for one row at time.

No table without a defined Unique index or PK. No table where the PK is more than one field. No table where the PK is not an int.

No object names that use reserved words for the database I'm using.

No fields with the word Date as part of the name that are not defined as date or datetime.

No table without an associated audit table.

No field called SSN, SocialSecurityNumber, etc. that is not encrypted. Same for any field named CreditCardNumber.

No user defined datatypes (In SQL Server at least, these are far more trouble than they are worth.)

No views that call other views. Experience has shown me these are often a performance disaster waiting to happen. Especially if they layer more than one layer deep.

If using replication, no table without a GUID field.

All tables should have a DateInserted field and InsertedBy field (even with auditing, it is often easier to research data problems if this info is easily available.)

Consistent use of the same case in naming. It doesn't matter which as long as all use the same one.

No tables with a field called ID. Hate these with a passion. They are so useless. ID fields should be named tablenameID if a PK and with the PK name if an FK.

No spaces or special characters in object names. In other words if you need special handling for the database to recognize it in the correct context in query, don't use it.

If it is going to analyze code as well, I'd want to see any code that uses a cursor or a correlated subquery. Why create performance problems from the start?

I would want to see if a proc uses dynamic SQl and if so if it has an input bit variable called Debug (and code to only print the dynamic SQl statment and not execute it, if the Debug variable is set to 1).

I'd want to be able to check that if there is more than one statement causing action in the database (insert/update/delete) that there is also an explicit transaction in the proc and error trapping to roll the whole thing back if any part of it fails.

I'm sure I could think of more.