Database Development - General

1.     For Microsoft development I also prefer a unique record constraint built on a natural key and a surrogate key as the primary key. 

A.     This way we can always identify a record with one single unique value which simplifies development, SQL statements and audit log strategies since schema and SQL statements will be composed of single joins between tables.

B.     We have defined what is in our table with the unique record constraint built on a natural key. Without this aggregate figures are meaningless or at minimum undefined.

C.    For performance and index maintenance reasons using a surrogate key as the “CLUSTERED” primary key is usually provides the best generic performance for user interfaces.

D.    Microsoft’s SharePoint Access Services requires a single column primary key and referential integrity is enforced through the use of data macros so our design strategy is somewhat compatible with SharePoint Access Databases.

E.     I would stress that whether it is the primary key or just a unique record constraint the single most important aspect of table design is a unique record constraint built on a natural key.

2. Review existing stored procedures and replace cursor based operations with set based operations
3. Make sure there are no semi-random updates or repetitive updates. One way to ensure there are no semi-random updates is to run the population procedure to populate two copies of the same table using the same source data.  If there are any data discrepancies between the data in the resulting tables then it is likely that there are semi-random updates in the population code.
4. Make sure stored procedures are designed with flexible input parameters to eliminate the need for multiple procedures that perform the same basic operations.  This minimizes maintenance and debugging time.
5. With reporting stored procedures I like to standardize the input parameters and include a dynamic date range id.
6. Set up fully automated server side tracing and execution of the index wizard to analyze and automatically apply or just email suggested index additions or deletions. 
7. Ensure there are statistics on all tables.  Under certain circumstances the SQL Server 2000 sp_createstats function will die without an error message and not create statistics on all tables.  These missing statistics need to be identified and created to ensure efficient SQL Server execution plans.
8. Setup audit logging on all tables.  On SQL Server version prior to 2008 I do this with a stored procedure that dynamically creates and applies audit log triggers on all tables and uses 2 stages to populate the final audit log table so the performance hit of full audit logging is minimized. SQL Server 2008 greatly simplifies this with a new feature called Change Data Capture which is much more easily implemented.
9. To encapsulate business logic and reduce maintenance I like to create user defined functions and/or tables to perform repeatable operations.  This usually involves some tradeoff between performance and maintenance so it must be balanced according to the needs of the company and the amount of data being processed.