Blast from the past: Top 10 tips for database developers

New year means a clear out! Stumbled across this written between 2007 and 2009. I sound really lecturing, pompous and stuffy! I think this is the point I started doing more development than database administration. At the time it helped, not all of the tips would my top 10 today. The great thing about finding it? Food for thought on rewriting.

If you could go back and give your younger self 10 tips, what would be yours? Do you disagree with any of the advice? Would love your thoughts.

1. Invest in good database design.

A poor database design will impact query performance and data quality to a larger extent than purchasing additional hardware. Once a well designed, fully normalised database has been achieved, ensure that modifications to the design are not carried out in an organic fashion but are subjected to the same normalisation checks of the initial design.

2.Use the right data types.

As well as designing the database correctly, data types should be the smallest required. This should take into account future growth as well. For example, do not store numbers or dates as strings, nor booleans as integers.

3. Images and files do not belong in the database.

Instead, store the path to the object in the database and store the object in the file system.

4. Put business rules in the DBMS

Use the appropriate objects to enforce business rules at the database level. This can improve performance, for example, trusted check constraints will affect query optimisation. More importantly, they ensure data integrity. At a column level, ensure NULLs are only allowed where required. At a table level, ensure check constraints and foreign key relationships are specified. Use triggers prudently, both at a table and database level.

5. Use surrogate keys.

A surrogate key is a unique identifier that has no business related value. An sequential surrogate key that never requires modification as the primary key and clustered index, will ensure that other indexes created on the table are smaller in size and more efficient.

6. Be prudent with indexes.

The need to create too many indexes may indicate poor database design. Use the “Included Columns” feature to extend the usefulness of indexes for heavily used queries.

7. Make Where clauses SARGable.

A SARGable where clause takes advantage of indexes on a table to speed up completion of the query. The following search arguments will usually prevent query optimisation: “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, “LIKE ‘%xxx’”.

Additionally, using a function within a Where clause like “WHERE DATEADD(yy, 21, ReportDate) > 21” etc will usually prevent query optimisation. Use a variable instead of a function or rewrite the Where clause to not include the column name in these cases.

8. Use stored procedures.

Provided the SQL statements within the stored procedures are SARGable, a stored procedure will be compiled and improve execution performance. It will also ensure that business rules can be encapsulated and reused.

9. Avoid using cursors.

There is rarely a requirement to use a cursor or While statement. If a row-by-row operation is necessary, it could indicate poor database design and can be very expensive.

10. Test and measure.

The only way to be sure that a change will benefit is to test and measure the impact. Where possible, a replica of the live environment is recommended to ensure performance testing can also be carried out.