I am still reasonably surprised at how few developers seem to grasp fully the difficulties of ensuring the consistency and transactional integrity of the data stored in SQL Server, when that data is subject to concurrent queries and modifications, and the implications this has on the level of required database testing.

Most developers will be able to tell you about the ACID properties of transactions, but have a touching faith that SQL Server will just automatically take care of their enforcement. In most cases, it will, but in his excellent book, Defensive Database Programming, Alex Kuznetsov proves that it's far from guaranteed. When running under the READ COMMITTED transaction isolation level, the default for normal SQL Server operation, not only is it possible for a reporting query to return inconsistent results, if another transaction is modifying the same data, but that the situation is quite easy to reproduce. Likewise he demonstrates the 'lost updates' and constraint violations that can and do occur when data is subject to concurrent modifications, and how certain T-SQL patterns, such as IF EXISTS(…) THEN are unreliable under such conditions.

Any DBA who has been bitten by these subtle data integrity issues, or reports that erratically produce the wrong results, will know how much careful testing is involved in mitigating them. The database needs to be carefully, painstakingly designed, with appropriate levels of normalization. It needs to be thoroughly stress tested under concurrent access, with queries representative of a typical workload, against volumes and distributions of data that closely match reality. Query performance, and the stability of execution plans, needs to be closely monitored under various load and conditions. Extra measures to preserve data integrity such as use of higher isolations levels, additional constraints or hints, must be applied as necessary. Any subsequent change to the database, however seemingly-subtle, necessitates careful regression testing.

This is the reason why databases are so resistant to many of the Agile development practices that allow and encourage the changing of code freely and often.

Cheers,

Tony.