Are there any big differences between the two search techniques? It seems like they are both very similar.
SELECT * FROM TABLE1 WHERE TEXTFIELD1 LIKE '%DATABASES%'
SELECT * FROM TABLE1 WHERE CONTAINS (TEXTFIELD1 ,' "DATABASES" ')The like query will miss all indexes, but the fulltext query will use some sort of search-page-like index that the FullText engine has created.
Also, the FullText index has to be manually (or via a job) updated to collect all of the new additions. The like query will simply pick them up.|||I didn't try full text with mssql but yes... they are very diferent.
Like may seems to be fast some times, depending on the database, amount of records, etc, etc..
but compare full text index has the purpose to make very fast searchs. That's why the index it's so big.
Read about indexes of full text, and you will see that the size it's huge. Like has nothing to do in comparison to full text.
2012年3月27日星期二
2012年3月26日星期一
Full Text Search Query
Hi,
If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '), no
problem.
But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse" ')
returns an error, because the word 'the' is in the black list.
On my website, the users can check a checkbox if they want a search on all
the words they have entered.
Is there a way (something else having my own blacklist and removing by
myself the black words before sending the query to SQL Server...) to avoid
this error ?
If the only way is to use my own blacklist, is there a way to retreive the
SQL Server's one ?
Thanks.Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
> Hi,
> If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '),
> no
> problem.
> But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse"
> ') returns an error, because the word 'the' is in the black list.
> On my website, the users can check a checkbox if they want a search on all
> the words they have entered.
> Is there a way (something else having my own blacklist and removing by
> myself the black words before sending the query to SQL Server...) to avoid
> this error ?
> If the only way is to use my own blacklist, is there a way to retreive the
> SQL Server's one ?
You can remove the list of words from the SQL Server noise word file, or
remove the words from your query. I ended up clearing out my noise word
file. It depends on your installation path (and possibly SQL Server version)
as to where your noise word files are, mine are in
\MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
noise word file is noise.enu, but I also edited noise.eng. If you decide to
remove all the noise words from the config, do not empty the file
completely - leave a single line with a space on it. You'll have to
repopulate your catalogs if you change your noise word file so that the
words you have removed are added to the catalogs.
BTW: a more appropriate group for this is
microsoft.public.sqlserver.fulltext :)
Dan|||Steph,
In addition to what Daniel says below... Yes, the full path to the noise
word files (noise.enu) is SQL Server version specific, I usually shorten it
to the files under \FTDATA\SQLServer\Config. Note, that noise.enu = US
English, noise.eng = UK English and noise.dat = Neutral and are related to
the column-specific "Language for Word Breaker" in the FT Indexing wizard.
Also, run a Full Population after modifying the noise word files.
You may also want to review KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
For other SQL FTS resources see "SQL Server 2000 Full-Text Search Resources
and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:um7ny3iLFHA.656@.TK2MSFTNGP14.phx.gbl...
> Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
>
'),
all
avoid
the
> You can remove the list of words from the SQL Server noise word file, or
> remove the words from your query. I ended up clearing out my noise word
> file. It depends on your installation path (and possibly SQL Server
version)
> as to where your noise word files are, mine are in
> \MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
> noise word file is noise.enu, but I also edited noise.eng. If you decide
to
> remove all the noise words from the config, do not empty the file
> completely - leave a single line with a space on it. You'll have to
> repopulate your catalogs if you change your noise word file so that the
> words you have removed are added to the catalogs.
> BTW: a more appropriate group for this is
> microsoft.public.sqlserver.fulltext :)
> Dan
>
If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '), no
problem.
But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse" ')
returns an error, because the word 'the' is in the black list.
On my website, the users can check a checkbox if they want a search on all
the words they have entered.
Is there a way (something else having my own blacklist and removing by
myself the black words before sending the query to SQL Server...) to avoid
this error ?
If the only way is to use my own blacklist, is there a way to retreive the
SQL Server's one ?
Thanks.Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
> Hi,
> If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '),
> no
> problem.
> But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse"
> ') returns an error, because the word 'the' is in the black list.
> On my website, the users can check a checkbox if they want a search on all
> the words they have entered.
> Is there a way (something else having my own blacklist and removing by
> myself the black words before sending the query to SQL Server...) to avoid
> this error ?
> If the only way is to use my own blacklist, is there a way to retreive the
> SQL Server's one ?
You can remove the list of words from the SQL Server noise word file, or
remove the words from your query. I ended up clearing out my noise word
file. It depends on your installation path (and possibly SQL Server version)
as to where your noise word files are, mine are in
\MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
noise word file is noise.enu, but I also edited noise.eng. If you decide to
remove all the noise words from the config, do not empty the file
completely - leave a single line with a space on it. You'll have to
repopulate your catalogs if you change your noise word file so that the
words you have removed are added to the catalogs.
BTW: a more appropriate group for this is
microsoft.public.sqlserver.fulltext :)
Dan|||Steph,
In addition to what Daniel says below... Yes, the full path to the noise
word files (noise.enu) is SQL Server version specific, I usually shorten it
to the files under \FTDATA\SQLServer\Config. Note, that noise.enu = US
English, noise.eng = UK English and noise.dat = Neutral and are related to
the column-specific "Language for Word Breaker" in the FT Indexing wizard.
Also, run a Full Population after modifying the noise word files.
You may also want to review KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
For other SQL FTS resources see "SQL Server 2000 Full-Text Search Resources
and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:um7ny3iLFHA.656@.TK2MSFTNGP14.phx.gbl...
> Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
>
'),
all
avoid
the
> You can remove the list of words from the SQL Server noise word file, or
> remove the words from your query. I ended up clearing out my noise word
> file. It depends on your installation path (and possibly SQL Server
version)
> as to where your noise word files are, mine are in
> \MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
> noise word file is noise.enu, but I also edited noise.eng. If you decide
to
> remove all the noise words from the config, do not empty the file
> completely - leave a single line with a space on it. You'll have to
> repopulate your catalogs if you change your noise word file so that the
> words you have removed are added to the catalogs.
> BTW: a more appropriate group for this is
> microsoft.public.sqlserver.fulltext :)
> Dan
>
2012年2月26日星期日
full outer join
hello
i know that this post is not related to asp.net forum but if anyone can help me.
i have made three sql tables called table1,table2 and table3.
each one contains primary field called employeeid
table1 contains in addition to the primary a field callled field1.
table2 contains in addition to the primary a field callled field2.
table3 contains in addition to the primary a field callled field3.
the first time table1 contains one record
employeeid field1
---- --
1353 abc
the second table contains no data
the third table contains also one record
employeeid field3
---- --
1353 def
i have made a query :
select field1,field2,field3 from table1
full outer join table2 on table1.employeeid=table2.employeeid
full outer join table3 on table2.employeeid=table3.employeeid
the result is :
field1 field2 field3
abc null null
null null def
when i delete the record from the first table and put it in the second empty table:
the result :
field1 field2 field3
null abc def
i need to understand the results ?
i know that the outer join will get the rows from the both tables,but the results how can i get i don't understand
thank you for the help
The results you are getting is because OUTER JOIN defaults mathematically to a NULL so you need to use the ISNULL function with your replacement value. And I cannot remember now but OUTER JOIN have fixed table placement in the query to get expected results. BTW strange things happening with OUTER JOIN is normal. Hope this helps.
i know that this post is not related to asp.net forum but if anyone can help me.
i have made three sql tables called table1,table2 and table3.
each one contains primary field called employeeid
table1 contains in addition to the primary a field callled field1.
table2 contains in addition to the primary a field callled field2.
table3 contains in addition to the primary a field callled field3.
the first time table1 contains one record
employeeid field1
---- --
1353 abc
the second table contains no data
the third table contains also one record
employeeid field3
---- --
1353 def
i have made a query :
select field1,field2,field3 from table1
full outer join table2 on table1.employeeid=table2.employeeid
full outer join table3 on table2.employeeid=table3.employeeid
the result is :
field1 field2 field3
abc null null
null null def
when i delete the record from the first table and put it in the second empty table:
the result :
field1 field2 field3
null abc def
i need to understand the results ?
i know that the outer join will get the rows from the both tables,but the results how can i get i don't understand
thank you for the help
The results you are getting is because OUTER JOIN defaults mathematically to a NULL so you need to use the ISNULL function with your replacement value. And I cannot remember now but OUTER JOIN have fixed table placement in the query to get expected results. BTW strange things happening with OUTER JOIN is normal. Hope this helps.
订阅：
博文 (Atom)