CODEWITHSUNDEEP
sql-serverstringalgorithmcomparisonsql-like
kemiller2002
kemiller2002

Reputation: 115420

Sql Server String Comparision

Is there any information as to how SQL Server compares strings and handles searching in them (like statments)? I am trying to find out if there is a way to determine how efficient it is to store information as a large string and use sql server to do a bunch of comparisons on rows to determine which match. I know this is potentially going to be slow (the each string of information would be 2400 characters long), but I need something doucmenting how the string is compared, so I can show the efficency (or inefficency) of it.

Upvotes: 0

Views: 791

Answers (5)

Dan Snell
Dan Snell

Reputation: 2245

In the MSDN Article about Full-Text searches the following is called out regarding how the LIKE predicate uses character patterns.

Comparing LIKE to Full-Text Search

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

Just additional information to the already mentioned. If you need to filter the large string with like, indices are also not used (except the wildcard % is only at the end of the search string). So it's best to avoid like and make the part you need to filter for available in an own field.

Upvotes: 0

Jason Coyne
Jason Coyne

Reputation: 6636

There are full text search indexes that you can apply to sql server, which are often used for things like search engines. The full text indexes typically allow for boolean logic operators for the search.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134941

searching in them will be slow because you won't be able to create an index since an index can't be over 900 bytes long/wide

I would do what Joel Coehoorn suggests and split it up into columns

you also might want to split it up in more tables because you can only store 3 rows pr page with 2400 chars per row

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

each string of information would be 2400 characters long

Exactly 2400? So you've got fixed-width fields in there? Save your time and just split it into separate columns. You'll thank yourself later.

If you must have data, set up a test db and try it both ways. Then at least you'll have data that's specific to your system.

Upvotes: 2

Related Questions