By: Aaron Bertrand | Last Updated: 2016-11-17 | Comments (6) | T-SQL
Many people have come across situations where their queries are returning incorrect results - sometimes this is due to a bug in SQL Server, but it is far more common that it is less sinister than that.
Having spent quite a bit of time supporting a wide variety of users with an assortment of "wrong results" problems, I've found that there are actually only a few common scenarios that lead to incorrect results - and usually it is a logic or assumption problem.
The five things we will cover are:
- Date Literals
- BETWEEN / EOMONTH()
- Binary, case-sensitive, or wrong collation search
- Mixing up join / filter criteria
- Using READ UNCOMMITTED / NOLOCK
Let's get started.
1 - Date Literals
SQL queries are often constructed within application code or dynamic SQL,
and date strings typed by the user are copied and pasted, as is, into the query
text. The problem is that the user does not always speak the same language or
regional settings as the server. So when a Canadian types
into the textbox, meaning August 9th, the query ends up like this:
... WHERE EventDate = '9/8/2016';
Since the server is set up with US English, SQL Server has to interpret that as September 8th. The user might not even notice that the events returned are from the wrong date, since often internationalization is missed at the presentation layer too - so it will display 9/8/2016, just like the user typed.
A slight variation on this theme is when
9/8/2016 is passed
directly into the query, without proper delimiters, so it ends up looking like
... WHERE EventDate = 9/8/2016;
This won't possibly return the right data, even if they were expecting September 8th. The problem is that SQL Server allows for all kinds of sloppy implicit conversions; in this case, it thinks you are intentionally writing an expression that divides three integers. Nine divided by eight divided by 2016 is zero, therefore SQL Server implicitly converts your expression to the base date (1900-01-01) plus zero days. Evidence:
SELECT a = 9/8/2016, b = CONVERT(datetime, 9/8/2016); /* a b ---- ----------------------- 0 1900-01-01 00:00:00.000 */
Moral: Always use proper date/time parameters, and whenever using
a string representation at any layer, use a culture invariant format like
yyyymmdd. This may mean forcing your users to select from a calendar
control or set of drop-downs, because if you're letting them type, you can never
be absolutely certain whether they meant August 9th or September 8th.
2 - BETWEEN / EOMONTH()
One query strategy that I have always found problematic is the use of
BETWEEN for date range queries. Microsoft didn't do many people
any favors adding the
EOMONTH() function in SQL Server 2012.
BETWEEN is problematic in part because it is ambiguous - what
exactly does "between" mean? If I say "between 4 PM and 8 PM,"
it may seem clear, but should something that happens at exactly 8 PM be included?
What about at 8:01 PM? And 8:00:00.0000001? Even in plain English, it's not
always clear whether the start and end of the range should be included.
BETWEEN forces query writers to determine the end
of a period. If you are writing a report that covers the month of February,
how exactly do you determine the end of February? Leap year might seem to be
the biggest complication, but it's actually harder to determine the last instant
on the last day of the month. With
datetime, that's usually calculated
by subtracting 3 milliseconds from March 1st. But if the underlying data type
changes, or any parameter or variable used along the way, this value can round
up or down unexpectedly.
DECLARE @start datetime = '20160201', @end datetime = DATEADD(MILLISECOND, -3, '20160301'); DECLARE @d date = @end, @eom datetime = EOMONTH(@end), @sdt smalldatetime = @end, @dt2 datetime2(7) = @end; SELECT @dt, -- 2016-02-29 23:59:59.997 @d, -- 2016-02-29 @eom, -- 2016-02-29 00:00:00.000 @sdt, -- 2016-03-01 00:00:00 @dt2; -- 2016-02-29 23:59:59.9966667
If the column is a datetime column, but the parameters or variables are manipulated in this way, then you will have incorrect results:
- If the parameter is date, or is implicitly converted to a day boundary
BETWEEN @start AND @dwill miss anything from February 29th with a time other than midnight.
- If the parameter is
BETWEEN @start AND @sdtwill include any data from March 1st with a time of midnight.
- If the parameter is
datetime2, the end range is rounded down, so in theory, you could miss data from 23:59:59.997 (or .9999999 if the underlying data type is
EOMONTH(), in particular, seems to trick a lot of people into
thinking it returns the end of the month, when in fact - as demonstrated above
- it returns midnight on the last day of the month. Which of course is not the
same thing. Now,
BETWEEN would work here if the underlying data
date, but I strongly recommend against using that as an
exception to the rule, and using other methods elsewhere.
Moral: Always use an open-ended range for date range queries, even
when using the
date data type.
It is always easier to find the beginning of the next day, week, month, what have you, than it is to find the end of the current period. And, more importantly, an open-ended range is not vulnerable to rounding and conversion issues. The following can never return the wrong results (e.g. will always return all of the data for February, and only the data for February), regardless of what you do to the data types of the parameters or underlying columns:
DECLARE @start datetime = '20160201', @end datetime = DATEADD(MONTH, 1, '20160201'); SELECT ... WHERE datetime_column >= @start AND datetime_column < @end;
3 - Binary, case-sensitive, or wrong collation search
I often recommend people develop on a case-sensitive or binary collation. Like learning to drive using a stick shift in the winter, this can prevent surprises and make sure your code can be deployed anywhere and continue to function.
Let me illustrate with a simple example. Let's create two databases, one with case sensitive collation, and the other case insensitive:
CREATE DATABASE CaseInsensitive COLLATE Latin1_General_CI_AS; GO CREATE DATABASE CaseSensitive COLLATE Latin1_General_CS_AS; GO
Now, let's create a basic table, something you might do without having any idea about the collation of the database (or the server-level collation of the instance).
USE CaseInsensitive; GO CREATE TABLE dbo.x(LastName nvarchar(32) PRIMARY KEY); INSERT dbo.x(LastName) VALUES(N'Bertrand'),(N'ߐ'); DECLARE @ln_lower nvarchar(32) = N'bertrand', -- wrong case @ln_varchar varchar(32) = 'ߐ'; -- not Unicode SELECT LastName FROM dbo.x WHERE LastName = @ln_lower; -- 1 row SELECT LastName FROM dbo.x WHERE LastName = @ln_varchar; -- 0 rows
Because the column uses a case insensitive collation,
Bertrand still match. However, even in a case sensitive collation,
not all Unicode strings will match an "equivalent"
string - the strings simply won't be the same, as a conversion to
can lose information.
If we repeat this exact same code sequence on the case sensitive database,
bertrand no longer matches
Bertrand, and the Unicode
problem still exists.
Let's say we have a simple table with a Unicode column, and we want to find all the names where the first letter is in lower case. We'd expect we could do this with a case sensitive collation, but collations are funny:
DECLARE @x TABLE(LastName nvarchar(32)); INSERT @x(LastName) VALUES(N'berthelette'),(N'Bertrand'); -- now, use COLLATE to get all lower case letters: SELECT LastName FROM @x WHERE LastName COLLATE Latin1_General_CS_AS LIKE N'[a-z]%'; SELECT LastName FROM @x WHERE LastName LIKE N'[a-z]%' COLLATE Latin1_General_CS_AS; -- wait a minute, why is Bertrand returned? -- This collation uses dictionary sorting, which means aAbBcC... -- A workaround is to use a BIN/BIN2 collation, which use code-point. -- Meaning abc...ABC... -- Now only berthelette is returned: SELECT LastName FROM @x WHERE LastName COLLATE Latin1_General_BIN LIKE N'[a-z]%'; SELECT LastName FROM @x WHERE LastName COLLATE Latin1_General_BIN2 LIKE N'[a-z]%'; SELECT LastName FROM @x WHERE LastName LIKE N'[a-z]%' COLLATE Latin1_General_BIN; SELECT LastName FROM @x WHERE LastName LIKE N'[a-z]%' COLLATE Latin1_General_BIN2;
Moral: For search columns where you want to support case-insensitive
searches, define them using an explicit, case-insensitive collation. Also, always
use the N prefix when dealing with Unicode strings, not just to ensure correct
results, but also to avoid costly implicit conversions. Other workarounds include
UPPER() functions to both sides
WHERE clause, but these approaches can kill sargability
(as can the
COLLATE clause shown above).
4 - Mixing up join / filter criteria
A situation I see a lot out on forums is when a user is performing an outer
join but the query behaves like an inner join. This is often because
some of the criteria meant for the outer table is expressed in the
clause instead of the
JOIN clause. It will be easier to explain
with an example; let's say we have a table representing students, and another
table representing how many pets a student has:
CREATE TABLE dbo.Students ( StudentID int PRIMARY KEY, FirstName nvarchar(32) ); CREATE TABLE dbo.PetCount ( StudentID int PRIMARY KEY FOREIGN KEY REFERENCES dbo.Students(StudentID), PetCount tinyint ); INSERT dbo.Students(StudentID, FirstName) VALUES(1, N'Aaron'),(2, N'Madeline'),(3, N'Nora'); INSERT dbo.PetCount(StudentID, PetCount) VALUES(1, 1), (2, 5);
Here we see that Aaron has one pet, Madeline has five pets, and Nora has
no pets (no row in the
Now, let's say you need a query that would return all students with fewer than three pets. You would start with a left join:
SELECT s.FirstName, pc.PetCount FROM dbo.Students AS s LEFT OUTER JOIN dbo.PetCount AS pc ON s.StudentID = pc.StudentID; /* which returns: FirstName PetCount --------- -------- Aaron 1 Madeline 5 Nora NULL */
This looks good so far, but we still need to add a filter to remove Madeline
from the result set. What I see most people try at first is they add a
SELECT s.FirstName, pc.PetCount FROM dbo.Students AS s LEFT OUTER JOIN dbo.PetCount AS pc ON s.StudentID = pc.StudentID WHERE pc.PetCount < 3; /* which returns: FirstName PetCount --------- -------- Aaron 1 */
Well, that didn't work so well. Sure, we got rid of Madeline, who has 5 pets,
but we also got rid of Nora, who has 0 pets. Many people struggle to understand
this, but the easiest way to explain it is if you add a
to the outer table, you change the outer join to an inner join. From
a logical perspective, what's happening is that the filter can only return true
for values of
pc.PetCount that are known to be less than
PetCount for Nora is NULL, not 0, it is unknown
if that is less than 3, so the row is rejected.
Next, they try moving the filter to the join clause:
SELECT s.FirstName, pc.PetCount FROM dbo.Students AS s LEFT OUTER JOIN dbo.PetCount AS pc ON s.StudentID = pc.StudentID AND pc.PetCount < 3; /* which returns: FirstName PetCount --------- -------- Aaron 1 Madeline NULL Nora NULL */
This might be even more confusing - what happened to Madeline's pets? Changing
AND means that, essentially, the filter
only applies to the data that comes from the outer table. All rows are returned
Students but data from
PetCount is only included
when it matches the filter.
The way you have to solve this particular query is to use a
clause that whether
PetCount - or 0, when
doesn't exist - is less than 3:
SELECT s.FirstName, PetCount = COALESCE(pc.PetCount, 0) FROM dbo.Students AS s LEFT OUTER JOIN dbo.PetCount AS pc ON s.StudentID = pc.StudentID WHERE COALESCE(pc.PetCount, 0) < 3; /* which finally returns what we expect: FirstName PetCount --------- -------- Aaron 1 Nora 0 */
Typically, the problems I see with outer joins is that a filter is mistakenly
placed in the
WHERE clause. In this case, that's exactly where
Moral: Strive to understand the different logical join mechanisms, and make sure to test against all edge cases before being satisfied that your query logic is sound.
5 - Using READ UNCOMMITTED / NOLOCK
Last but not least,
NOLOCK is a really popular table hint, but
most people think it's just a "go faster" button and don't realize
(or ignore) that it actually still incurs locks, so its name is not entirely
accurate. And as I explained in
Bad habits : Putting NOLOCK everywhere, there can be corruption issues and
exceptions, but incorrect results can happen too. These can manifest in four
different ways, all due to the way SQL Server can scan the data:
- Dirty read: You read an uncommitted row that later gets rolled back.
- Skipped row: You miss a row entirely, because it moves from ahead of your current scan position to behind it.
- Re-read row: You read a row twice, because it moves from behind your current scan position to ahead of it.
- Half-updated row: You read a value from a column that is literally in the middle of an update.
The latter is the most interesting case to me, because it is not obvious
(and I didn't realize it until I saw it
demonstrated by Paul White). The other three scenarios are often known trade-offs
- sure, for the benefits, it's worth it to them to occasionally see a row that
never existed, or miss a row, or read the same row twice. But seeing a single
value that has been partially updated borders on corruption to me.
Let's illustrate with an example using an
In one query window, create the following table, and start this infinite loop:
CREATE TABLE dbo.PixieDust ( ID int PRIMARY KEY, LOB nvarchar(max) NOT NULL, ); -- insert a value that will span at least two pages: INSERT dbo.PixieDust(ID, LOB) VALUES(1, REPLICATE(CONVERT(nvarchar(max), N'A'), 4097)); SET NOCOUNT ON; DECLARE @AllAs nvarchar(max) = REPLICATE(CONVERT(nvarchar(max), N'A'), 4097), @AllZs nvarchar(max) = REPLICATE(CONVERT(nvarchar(max), N'Z'), 4097); WHILE 1 = 1 BEGIN UPDATE dbo.PixieDust SET LOB = @AllAs WHERE ID = 1; UPDATE dbo.PixieDust SET LOB = @AllZs WHERE ID = 1; END;
Now, in a second query window, run this loop, which will halt as soon as
NOLOCK query reads a value that has been half updated. It shouldn't
SET NOCOUNT ON; DECLARE @ValueRead nvarchar(max) = N'', @AllAs nvarchar(max) = REPLICATE(CONVERT(nvarchar(max), N'A'), 4097), @AllZs nvarchar(max) = REPLICATE(CONVERT(nvarchar(max), N'Z'), 4097); WHILE 1 = 1 BEGIN SELECT @ValueRead = LOB FROM dbo.PixieDust WITH (NOLOCK) WHERE ID = 1; IF @ValueRead NOT IN (@AllAs, @AllZs) BEGIN PRINT LEFT (@ValueRead, 32); PRINT RIGHT(@ValueRead, 32); BREAK; END END;
Before too long, you should see this output, meaning that the value read
NOLOCK query consisted of some As and some Zs:
Just one simple example where
NOLOCK can make a real mess of
your data. (Don't forget to go back to the first query window, stop that infinite
loop, and drop the table!)
Moral: Only use
NOLOCK where the risk of the above side
effects is acceptable (and where this method actually improves performance or
concurrency in a meaningful way). I would strongly consider
SNAPSHOT as an alternative.
Now, I am not saying
NOLOCK doesn't have its place - it is certainly
fine for ballpark-style queries, ad hoc queries against large tables, non-production
systems, and other scenarios. But I have witnessed many shops where it is dictated
by policy that all queries must use
NOLOCK. I would be very careful
about this in production systems where accuracy is important; in my experience,
accuracy is usually pretty important.
These are five of the most common reasons I see for "wrong results" issues in SQL Server. Do you see a common scenario that I haven't mentioned? Let me know in the comments below!
- See these related tips and other resources:
- For date/time-related queries:
- For collation- and case-sensitive queries:
- For joins:
- For NOLOCK:
Last Updated: 2016-11-17
About the author
View all my tips