Rules to Better SQL Databases - Developers

• Do you understand relational database design?
• Do you normalize your database?
• Do you visualize your database with an ERD?
• Data - Do you not allow Nulls in text fields?
• Data - Do you not allow NULLs in number fields if it has the same meaning as zero?
• Data - Do you avoid spaces and empty lines at the start of character columns?
• Data - Do you use Identities in SQL Server?
• Data - Do you avoid deleting records by flagging them as IsDeleted (aka Soft Delete)?
• Data - Dates - Do you make sure you have valid date data in your database?
• Data - Dates - Do you know DateTime fields must be converted to universal time?
• Data - Do you use temporal tables to audit data changes?
• Data - Do you avoid invalid characters in object identifiers?
• Do you have a general Contact Detail table?
• Data - Do you use a URL instead of an image in your database?
• Schema - Do you only use Unicode datatypes (nchar, nvarchar and ntext) in special circumstances?
• Schema - Do you always use Varchar?
• Schema - Do you have standard tables and columns?
• Schema - Do you use Bit/Numeric data type correctly?
• Schema - Do you use Natural or Surrogate primary keys?
• Schema - Do you create primary key on your tables?
• Schema - Do you create clustered index on your tables?
• Schema - Do you avoid using indexes on RowGuid column?
• Schema - Do you have a rowversion column?
• Schema - Do you use FillFactor of 90% for indexes and constraints?
• Schema - Do you always have version tracking tables?
• Schema - Do you use computed columns rather than denormalized fields?
• Schema - Do you use triggers for denormalized fields?
• Do you validate each "Denormalized Field"?
• Schema - Do you avoid using user-schema separation?
• Schema - Do you create a consistent primary key column on your tables?
• Schema - Do you use separate lookup tables rather than one large lookup table for your lookup data?
• Schema - Do you know how to provide best database schema document?
• Schema - Do you add zs prefix to system tables?
• Views - Do you avoid having views as redundant objects?
• General - Do you know every object name should be owned by dbo?
• Stored Procedures - Do you keep your Stored Procedures simple?
• Stored Procedures - Do you return a value indicating the status?
• Stored Procedures - Do you standardize the return values of stored procedures for success and failures?
• Stored Procedures - Do you use OUTPUT parameters if you need to return the value of variables?
• Stored Procedures - Do you check the global variable @@ERROR after executing a data manipulation statement?
• Stored Procedures - Do you use SCOPE_IDENTITY() to get the most recent row identity?
• Stored Procedures - Do you SET NOCOUNT ON for production and NOCOUNT OFF off for development/debugging purposes?
• Stored Procedures - Do you avoid starting user stored procedures with system prefix "sp_" or "dt_"?
• Stored Procedures - Do you avoid using SELECT * when inserting data?
• Stored Procedures - Do you use transactions for complicated stored procedures?
• Stored Procedures - Do you use error handling in your Stored Procedures?
• Stored Procedures - Do you know SQL stored procedure names should be prefixed with the owner?
• Relationships - Do you turn on referential integrity in relationships?
• Relationships - Do you use Update Cascade when creating a relationship?
• Relationships - Do you avoid using Cascade Delete?
• Relationships - Do you set Not For Replication when creating a relationship?
• Relationships - Do you have FOREIGN KEY constraints on columns ending with ID?
• General - Do you know object name should not be a reserved word?
• General - Do you know object name should not contain spaces?
• General - Do you know to not use "sp_rename" to rename objects?
• General - Do you know object name should follow your company naming conventions?
• Do you use a SQL Server object naming standard?
• General - Do you use a SQL Server Stored Procedure Naming Standard?
• General - Do you use a SQL Server Indexes Naming Standard?
• Do you use a SQL Server Relationship Naming Standard?
• Do you know the naming convention for use on database server test and production?
• Middle Tier - Do you implement business logic in middle tier?
• Do you parameterize all input to your database?
• Views - Do you use SQL Views?
• Data – Do you avoid empty lines at the start of character columns?
• Schema - Do you use less than 24 characters for table names?
• Middle Tier - Do you submit all dates to SQL Server in ISO format?
• SQL Server - Do you know the different ways to query data?