Pattern Matching

Kevin Feasel



Aaron Bertrand has an article on performance testing various pattern matching techniques:

I wanted to prove to myself that the above procedure was undeniably better overall for all possible searches than any of the queries that use ORconditionals, regardless of whether they use LIKE, LEFT/RIGHT, SUBSTRING, or CHARINDEX. I took David’s basic query structures and put them in stored procedures (with the caveat that I can’t really test “contains” without his input, and that I had to make his OR logic a little more flexible to get the same number of rows), along with a version of my logic. I also planned to test the procedures with and without an index I would create on the search column, and under both a warm and a cold cache.

The conclusion is ambiguous.  Read on for the full story.

Related Posts

Refreshing Views After DDL Changes

Eduardo Pivaral shows how you can refresh the metadata for a view in SQL Server after one of its underlying tables or functions changes: So we proceed to execute an alter view over the first view: ALTER VIEW dbo.[vi_invoices_received_by]ASSELECT ConfirmedReceivedBy as [Received by], COUNT(InvoiceID) as [# of Invoices], CustomerIDFROM Sales.InvoicesGROUP BY ConfirmedReceivedBy, CustomerID;GO So we […]

Read More

Finding Gaps in Dates

Jason Brimhall shows how you can find gaps in your data: This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set […]

Read More


September 2016
« Aug Oct »