Using Computed Columns For Pattern Matching

Erik Darling shows how you can use a computed column to speed up %something searches:

We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching.

What’s back-searching? It’s a word I just made up.

The easiest example to think about and demo is Social Security Numbers.

One security requirement is often to give the last four.

Obviously running this for a search WHERE ssn LIKE '%0000' would perform badly over large data sets.

One of the trickiest parts of performance tuning is understanding that the way people want to see data is not necessarily the way you should store the data.

Related Posts

Views Don’t Improve Performance

Grant Fritchey lays down the law on views: One day, it’s going to happen. I’m going to hear some crazy theory about how SQL Server works and I’m going to literally explode. Instead of some long silly rant with oddball literary & pop culture references you’ll get a screed the size of Ulysses (and about […]

Read More

Scalar Function Blocking

Erik Darling notes that scalar functions can cause multi-table blocking: Someone had tried to be clever. Looking at the code running, if you’ve been practicing SQL Server for a while, usually means one thing. A Scalar Valued Function was running! In this case, here’s what it looked like: 1 2 3 4 5 6 7 […]

Read More

Categories

January 2018
MTWTFSS
« Dec Feb »
1234567
891011121314
15161718192021
22232425262728
293031