Is PATINDEX faster than LIKE?

re: Is PATINDEX faster than LIKE?

Mark Jackson — Wed, 24 Oct 2007 13:24:16 GMT

The important bit you missed is the phrase ' but for which indexes cannot be used'. Your test has a clustered index on the searched field and that will be used every time. If you run the same test with a clustered primary key index (on an id field) and your search field set up as a text datatype there is a hugh difference. I have had up to 50% speed gains on a 1.3 million record table of life customer data using patindex, although I would rather shoot myself in the head then do either of these as using a fulltext index on the same data returns the same results in less than a second, compared to 20+ seconds with patindex, or 35+ seconds with like.

re: Is PATINDEX faster than LIKE?

Anon Anon — Tue, 14 Oct 2008 18:03:03 GMT

The main reason anyone will swirl away from using SQL FT to using a LIKE or a PATINDEX is when they have to do a *term .

SQL FT supports only term* and not *term or *term* .

For example: If I were to search for 123*, I will get 1234, 123456 etc. I can also get 00123 if I were to create a column with the reverse text & create a FT index on it.

But, How can i find the term with has "0012345" ? Looks like this is not supported with 2008 either ! huh ! After all these years Microsoft does not seem to figure out this trivialities!

-AA

re: Is PATINDEX faster than LIKE?

Alex Cons — Wed, 09 Mar 2011 23:13:21 GMT

Why can't they just create a "Reverse" Index? the same way they create an index and check the letters left to right just create another right to left and boom!, you have *123*.

They must have it for a couple of languages like Arabic or Japanese I would guess.

re: Is PATINDEX faster than LIKE?

Ankit — Fri, 07 Oct 2011 10:47:00 GMT

Would some one please answer the poor guys question ?? I wanna know !!

re: Is PATINDEX faster than LIKE?

Adam Machanic — Fri, 07 Oct 2011 14:06:48 GMT

About the reverse index? Well they "could" create it (they being the SQL Server team). There are lots of things they could do, but at the end of the day they need to decide what to put in the product based on available time, resources, and the realities of the fact that they're trying to sell software. Would a reverse index improve sales? Probably not much. Therefore, it's not going to be prioritized, no matter how useful it would be.

But you can create it yourself. Add a computed column using the expression REVERSE(yourTextCol) and index it. And there you have it, a reverse index. Is it the prettiest thing in the world to have floating around in your database? Nope. But it gets the job done.