Some weeks ago, a developer told me he had found some strange behavior in SQL Server. Someone immediatelly yelled “bug”. Comments like this always grab my attention. More often than not, however, it’s not a bug. That also turned out to be the case here. But still, the behavior was not what I had expected, so I decided to write a blog post about it.
The scenario was this. The name of a customer had been entered twice: once with, and once without a space behind the name. Obviously, the front end application should have checked this, but alas. Now, we had two records for the same customer. When trying to correct the data in the database, the supposed bug appeared in a simple where clause. I can’t show the real data here, so I’ll use the AdventureWorks2012 sample database as an example. In the person table, there is one record for Mr. Syed Abbas:
You would expect not to find this record if you were looking for a person with last name ‘Abbas ‘. However, both queries return the same record, regardless of the number of spaces you put at the end of the string:
You can add a record for Mr. Abbas with several trailing spaces to verify that it works both ways. As it turns out, this is actually the intended behavior. According to this Knowledge Base article, the ANSI SQL-92 standard requires that when SQL compares to strings of different length, it has to pad the shortest string with spaces.
This proves that there is always something new to learn about SQL, even about something as basic as a where clause.