#T-SQL Tuesday #35 - Soylent Green

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!

About these ads