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.
+2
A:
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.
Joel Coehoorn
2009-04-21 19:03:47
Due to the nature of the data, I can't do that. I thought about that approach, but it would force me to do a bunch of concatenations to determine the overall value of the data. The 2400 is essentially a large set of flags which we would need to search to find a series of set flags in a row. Really what I am looking for is info stating this would be really inefficient to do.
Kevin
2009-04-21 19:10:25
I think (KNOW!) you'll find that splitting up the string on insert will be MUCH faster than splitting it out on search or retrieval. Cardinal rule of database design: 1 column, 1 value.
Joel Coehoorn
2009-04-21 19:16:27
Here's the deal. The dba wants each char to represent 1 min of time. I have to search to determine how long am available time span is. Splitting it up means that I'll have to do an large amount of work determining which values line up where to figure out how long the time span is. I'm not saying you're wrong, but what I need is enough info to show why this is bad idea. I have other data structures in mind to approach the problem, but I need to refute his idea before I can present my own.
Kevin
2009-04-21 19:33:22
Then get your dba to write the search code for you. DBA's generally get what they want in the end, so good or bad it might be best to prepare for this now. Just either tell him you're not smart enough and you need his expert help, or that you don't think he can do it -- depending on his personality and the relationship ;)
Joel Coehoorn
2009-04-21 19:37:27
A:
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
SQLMenace
2009-04-21 19:11:27
A:
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.
Jason Coyne
2009-04-21 19:55:15
I am not personally aware of anyone using the SQL Server full text index as a search engine back end. Do you know of any examples? Most implementations that I am aware of use Lucene or custom code for text search implementation.
Joshua Drake
2009-04-21 20:08:42
A:
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.
Stefan Steinegger
2009-04-21 21:37:00