T-SQL Tuesday is a monthly SQL Server blogging event. One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic. This month’s event is hosted by Nick Haslam (Twitter|Blog), and he’s asking us to talk about Soylent Green.
Thankfully, as this isn’t a film I know, he’s put in an alternative, specifically asking us for “your most horrifying discovery from your work with SQL Server”.
On with a story…
It was a Wildcard and stormy night
No, it’s no good. I can’t write that sort of post.
Wildcards – not always what you think
Once upon a time, a long time ago, I was wondering why a particular search against our case document database was taking so long. After all, the client code generated a search string of the form “xxxxxxxx_%” (where xxxxxxxx is a zero-padded eight digit case reference ID, and everything beyond the underscore is a document ID, reference, date etc). Aaaanyway – there was this long-running query, so I took a look.
It seems that the client code, under very particular circumstances (which I forget) had failed to put a Case Reference ID at the front of the search string, and so was looking for all documents whose names began with “_”. Not a problem, I would have thought in my more naive days. Boy, was I wrong.
SELECT ... WHERE Document_Name LIKE '_%'
is, in effect, the same as
SELECT ... WHERE Document_Name LIKE '%'
And this meant that the poor client app was trying to build a document list containing everything in our database – at that point, a mere 12 million records. No wonder the client app was hanging, the PC was out of memory, and the server was having a fit.
SQL Server wildcards – not just %
Here’s where we can do some learning.
Checking the documentation for the
LIKE operator gives the details. Summarised:
- “%” matches any string or zero or more characters, and “_” matches any single character string,
- Not-quite-regular expressions can be built using , – and ^
- If you actually want to match one of these “reserved” characters, you can “escape” it with s
- You can declare your own Escape character
Powerful. But not as powerful as real regular expression handling using the CLR…
…and I can’t remember if we ever managed to find that bug!