THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

How to make text searching go faster

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, March 8, 2016 2:39 PM by Rob Farley



Uri Dimant said:

Hi Rob

It is great post, but question is not answered :-))) Waiting for your investigation.

March 7, 2016 11:56 PM

Rob Farley said:

You mean the question about why a Hash is 10% (ish) faster in a binary collation?

It's because with a non-binary collation, it needs to make a call to GetSortKey(), so that it can hash a value which is equal for all the things that sort to the same point in that collation. Like how in a CI collation, H and h need to end up in the same hash bucket. In a binary collation, it doesn't have to call GetSortKey(), because things are only equal when they have exactly the same binary value.

I worked this out by asking someone who had access to a debugger which they regularly hook SQL Server into. He's a good friend, and happens to live only a few time zones away from me. (Thanks Paul!)

March 8, 2016 12:19 AM

Uri Dimant said:

I am aware of this Rob.

Using binary collation as you pointed out may return incorrect data (not all data found)

March 9, 2016 1:56 AM

Rob Farley said:

Yes. If you need to find "Uri" and "URI" and "uri", then you can't use a binary collation. But if it's always stored as "URI", then you can.

March 9, 2016 2:06 AM

Saeid Hasani said:

There are many situation that we can use this method such as codes, barcodes, national security codes etc.

March 25, 2016 7:54 PM

Rob Farley said:

Yes, Saeid. Lots of situations. Nice article - I wasn't aware of it. I presented this as one of my T-SQL Tips at TechEd AU back in 2008, but it wasn't new then either. Interesting that you refer to Brent's article on Sargability - he refers to one of mine in his, and was in the audience for my SQLBits session on it in 2010, which you can see at

March 25, 2016 8:16 PM

Saeid Hasani said:

Thanks Rob for your fast response. By referring to that article , I wanted to confirm your solution. I'm not against Uri's idea. He is a bright person in SQL world. But, this solution has its opponents (like few comments on my article). So, I was become happy when I read your great post. I didn't consider Brent's article references. I downloaded your SQLBits session and I saw it . It is an awesome session. Wish I saw it few years ago! :-) Thank you so much!

March 25, 2016 9:03 PM

Rob Farley said:

No worries, and thanks for supporting it.

March 25, 2016 9:12 PM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG


Privacy Statement