In other words, if we add a month, then subtract a month, we might not get back to the date we started from. For example:  SELECT  DATEADD(MONTH, 1, DATEADD(MONTH, -1, '20100330')) ,        DATEADD(MONTH, -1, DATEADD(MONTH, 1, '20100330'))2010-03-28 00:00:00.0002010-03-30 00:00:00.000  ...

The biggest advantage of unit testing is the ability to make changes quickly, and with confidence that we have not broken anything with our change. Whether we need to speed up a query real quick, or to fix a bug, automated testing saves us a lot of time, especially if test failure is exposed in user friendly way. This post continues the series on ...

This came up on sqlservercentral.com, where I read the following claim (in a discussion thread, not in an article): ''COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT''. I have a repro script which shows how COUNT(*) running under REPEATABLE READ returns wrong results with high ...

I was reading Eric Lippert's blog post, and came across the following quote: ''uncertainty erodes confidence in our users that we have a quality product that was designed with deep thought, implemented with care, and behaves predictably and sensibly''. How very true. By default, the code we are developing should run in exactly the same way ...

My book is finished. A couple years ago I googled up ''Defensive Database Programming'', and came up with nothing. Naturally, I started filling up the void. Although the book is complete, I am planning to continue my research; I would appreciate any other defensive scenarios to discuss and describe. Many thanks to my longtime friend Hugo ...

It is well known that SELECT * is not acceptable in production code, with the exception of this pattern: IF EXISTS(SELECT *    We all know that whenever we see code code like this: Listing 1. ''Bad'' SQL ...

Even if two processes compete on only one resource, they still can embrace in a deadlock. The following scripts reproduce such a scenario. In one tab, run ...

I use schemas to simplify granting permissions. For tables and views, I have three schemas: Data, the actual data my customers need. Can only be modified via sprocs. Staging, only visible to data loaders and devs. Full privileges on INSERT?UPDATE/DELETE for those who see it. Config, the configuration data used in loads, only visible to data ...

My second take at the same problem I blogged about yesterday: To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we are telling to the optimizer may be no longer relevant. In the previous post, Optimizing a ...

To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we are telling to the optimizer may be no longer relevant. This is when unit test shine - they allow us to document the assumptions which we are telling to the ...