This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
I need to design a database that will contains information about personal disease of users. What can be the approach in order to implement the columns of the DB's tables: encrypt the information, separate data within two differents DB, one for sensitive data and another for not sensitive data, or...
Alright, I feel like this is a terrible idea, but I need some help understanding why this is bad:
I'm still working on implementing a disaster recovery/business continuity solution for our datacenter. We're running MSSQL 2008 Enterprise and we plan on running a passive instance in the cloud. Cur...
The kind of crap that drives me crazy - guy accepts my answer even though he can't figure out how to use it, then un-accepts and accepts another simpler answer. Even though I wrote a complete procedure for him that would have worked if he knew how to copy and paste: stackoverflow.com/questions/10126212/…
I guess I'm mostly annoyed because I took time out of my vacation to give the guy a proper "you're doing it wrong" answer. I had to edit my answer three times because he edited the question three times. I wish we could apply warning flags to troublesome users.
40 minutes of my vacation I'll never get back - closing the lid, ciao folks
It's not that you're wrong, but there's a level of truth behind each of those rants, when used in the correct context. Both of these situations seem to be the correct context.
I believe that you are correct, @Phil
But here's my question. I downvoted that answer. -1 to rep (it's not that precious to me, but I don't have a whole lot of it to toss around like some of us [ahem @jcolebrand]).
@AaronBertrand Low rep, long question, poor grammar, "Why no worky?" question... all the red flags were there. Be pickier about what you answer, especially on a large site like SO where there is a lot of crap.
Is it possible to run a full text search on an FTS-indexed column for all values in another table?
Conceptually it'd look like
select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue)
I'm rather new to MSSQL FTS, though I know that the equivalent would work in postgres
select d....
I have a search key, I have a products title table. Im going to select from title table, like, SELECT * FROM titles WHERE title LIKE '%searchkey%'. Now, this is standard. Lets say my search key is of 5 words namely, A B C D E. Example, "nokia lumia 800 phone windows". Now, if I, SELECT * FROM titles WHERE title LIKE '%nokia lumia 800 phone windows%. I wont get any result because, title is "nokia lumia 800" only. What do I do now?
@beck03076 If you want to match any of the keywords, you need multiple LIKEs separated by ORs. If you want something more complex than that, you need to use something like Full-text search or an external search component like Lucene.
@NickChammas I understand your point. Im going to install sphinxsearch, anyway. But thing is, which is efficient?. Right now, Im doing a, LIKE '%A%B%C%D%E%' and if I dont get any results, I will query it again with LIKE '%A%B%C%D%', and so on upto LIKE "%A%". At one point I will get results. Do you think this is fine. To summarize, if the number of search keywords is n. Then I will query the table n number of times, that being the worst case scenario.
@beck03076 You can futz it like that to make it work, but the complexity of this solution, and the fact that it does not allow you to weigh your matches (e.g. matching on "lumia" is better than matching just on "nokia") should tell you that you need a full-text search solution that plain SQL cannot give you.
@NickChammas Yes, matching on lumia is way better than just nokia. So what do I do. Im going to install SphinxSearch. Does that bring full-text search into the picture?. Please bear with my partial knowledge?. If not, how do I bring it in?
@beck03076 Well, you can build something yourself, or incorporate full-text search. I don't have direct experience with FTS so I can't help you further. Do some research and come back if you have a specific question about how to use FTS in your situation.
@swasheck Thank you so much man. Thats exactly what I was looking for. 1. Is this available in mysql?...2. What about performance when compared with like '%nokia%', (I already hear, like degrades performance).{ I can also understand If extreme indexing is done on products table title column by some engine on steroids, the contains can be a player}
alright. Just tell me this. "select [column] from titles where CONTAINS(title, 'nokia lumia 800 phone windows') " will this query match , "nokia lumia 800"(without "phone windows")..?
what about "select [column] from titles where CONTAINS(title, 'nokia lumia 800 phone windows OR nokia lumia 800 phone OR nokia lumia 800 OR nokia lumia OR nokia')"
Now i want the system to stop proceeding when it finds. Because, when it finds nokia lumia 800, its pointless matching nokia lumia and nokia again and again, 3 words together spot a match which is more relevant
@beck03076 Here's my suggestion. You seem to have the makings of a question. Based on what you already know and what you've gained from @NickChammas, you might want to put this to a question.
The basic data structure for a text retrieval system is an Inverted Index. This is essentially a list of words found in the document collection with a list of the documents they occur in. It can also have metadata about the occurrence for each document, such as the number of times the word appe...
And a rather tedious looking journal article on phrase searching
@ConcernedOfTunbridgeWells Hey, thanks for stepping in. Can you explain to me on a HIGH LEVEL as to what is going on in cosine ranking approach?
Alright, thanks @swasheck and @ConcernedOfTunbridgeWells, it was very informative and im going to go through all of it that you guys provided. Break a leg.
@beck03076 Conceptually you make a pair of n-dimensional vectors where each dimension corresponds to a word or a phrase. If you're matching two documents then you count the occurence of each word or phrase on each document's vector. If you're doing keyword searches then the set of keywords becomes the second document. The vector of counts becomes a point in n-dimensional space (obviously n could be quite a large number of dimensions for a document-to-document match).
2
The dot product of these vectors is the cosine of the angle between the vectors in n-dimensional space.
You can do searches based on nearest-neighbour algorithms to cut down the search space from requiring you to do the comparison for each document. Then you stuff your search results into a priority queue based on the cosine of the angle between the vectors.
Two parallel vectors will have a cosine of 1 (cos (0) = 1) The angle will grow further apart as the documents get more dissimilar, with the cosine of the angle trending to 0. The highest cosine ranked document will pop out of the top of the priority queue.
Pagerank works by weighting the cosine values with secret formulas based on popularity, spamminess heuristics, heuristics that detect obvious gaming of search results and suchlike.
This probably doesn't help much apart from a brief primer on the theory behind text retrieval systems. Perhaps it might give you some insight into the subtleties of your problem, though.
@ConcernedOfTunbridgeWells : Thank you so much, it was amazing and the best part is I understood 80% of what you said. I will take this as a lead and do my research to understand what you said in totality. Yes, I would be more happy if you stay back, make my brain understand all of it. I love examples. If you can quote an example and explain this concept, I will rejoice and share it with many people at work as well.
@ConcernedOfTunbridgeWells : Great talking to you. Im signing off. Thanks. Thanks. bye
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
Is it possible to run a full text search on an FTS-indexed column for all values in another table?
Conceptually it'd look like
select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue)
I'm rather new to MSSQL FTS, though I know that the equivalent would work in postgres
select d....
The basic data structure for a text retrieval system is an Inverted Index. This is essentially a list of words found in the document collection with a list of the documents they occur in. It can also have metadata about the occurrence for each document, such as the number of times the word appe...
