Full Text Search with Empty Search String

Hi,

I have implemented full text search in one of my applications and i am using FREETEXTTABLE to perform the search. Actually i wanna to show all rows from the table whenever search string is empty,

Although i am currently using Dynamic sql to do this task, but i just wanna to know best practices to handle the situation ? Is there any way that i could use WildCard to return all rows?

Please suggest me a better way of doing it.

Thanks

/* You will need to adapt these examples */

/*This is the general pattern for returning the unfiltered record set

when the parameter is NULL. */

WHERE MyColumn = ISNULL(@.myVariable, MyColumn)

/*The following code snippets are taken from a production sproc that performs a complex search that includes a FULL-TEXT search. The input parameter is @.Keywords, it's DEFAULT = '' and the WHERE clause works if @.Keywords is NULL or not.

*/

--First

DECLARE @.KeywordNull1int

DECLARE @.KeywordNull2int

--second

IF @.Keywords = ''

BEGIN

SET @.KeywordNull1 = 1

SET @.KeywordNull2 = 1

END

ELSE IF @.Keywords <> ''

BEGIN

SET @.KeywordNull1 = 1

SET @.KeywordNull2 = 2

END

--third

--this is part of a complex WHERE clause

AND((CONTAINS(f.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)) OR (CONTAINS(fl.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)) OR (CONTAINS(FFT.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)))

ORDER BY fl.FolderPath,[FileName],FileExtension

|||

Hi,

Thanks very much for your reply it will be very helpful for me in future projects, but i am using FREETEXTTABLE for search purposes not CONTAINS,

i am even not sure which one is better, Do you have any idea which one actually is ?

|||Since I have no idea what your SELECT statement that contains FREETEXTTABLE looks like or what you are trying to do I have no way to answer your question.|||

RE general question of comparison of CONTAINS/CONTAINSTABLE to FREETEXT/FREETEXTTABLE - none is better or worse, they are intended for different scenarios.

1) CONTAINS and CONTAINSTABLE have a simpler ranking function (called TF/IDF in Information Retrieval) and support a mini-query language that allows precise specification of the user's intent - is supports operators like AND, OR, NEAR, FORMSOF(INFLECTIONAL,...)/FORMSOF(THESAURUS,...) etc.

2) FREETEXT and FREETEXTTABLE are have more complex ranking (OKAPI BM 25 in IR terms) and are intended to more closely resemble Web search experience over full-text-indexed data in your database.

You may want to start with http://msdn2.microsoft.com/en-us/library/ms142494.aspx or more generally, at http://msdn2.microsoft.com/en-us/library/ms142547.aspx to get more details.

Best regards,

|||Thanks Denistch , it really helped me!!!|||

Hi Denistch,

Problem is still the same, let say if i use query select tblID from CONTAINSTABLE(Table1,*,'') as keytable where ....

so whatever the query is, if search string is empty, how can i use wildcard to return all the rows from Table1. e.g if i use something like CONTAINSTABLE(Table1,*,"a*") it will return all the matching words starting with 'a', but i dont want just 'a', i want all rows!!!

Any help will be highly appreciated .

I know i can use CONTAINS or LIKE, but i have got very long dynamic query, if change to CONTAINS it does'nt give me rank, so i would be changing the whole query just for one scenario....which i dont wanna do!

Thanx!

sql

Full Text Search Problem

I wanna search for cars thats are Ferrari,_NAME and _TYPE fields are full text indexed

SELECT

COUNT(*)FROMCONTAINSTABLE(ADDS,*,'Ferrari AND Cars') >>> return only 8 results

but in facts there are 600 results.Doesn't "*" search in all coloumns?

********************************

Do I have to make a query like this...

SELECT

Count(T1.[KEY])FROMCONTAINSTABLE(ADDS,_NAME,'Ferrari')AS T1>>>>>return 600 results.

INNER

JOINCONTAINSTABLE(ADDS,_TYPE,'Cars')AS T2

ON

T1.[KEY]=T2.[KEY]

I don't want this type of query as I don't know how many parameters I will have and I am sick of the joins which slow down the server.

Your first query should work. Can you post a sample row which you think should satisfy the CONTAINSTABLE filter but is?not?count?|||

FIRST QUERY RESULTS

ID----NAME ------TYPE

2 Ferrari and Cars null

SECOND QUERY RESULTS

ID----NAME ------TYPE

24 Ferrari Car........................missing rows

24 Ferrari is a car null

24 null ferrari Cars are faster

|||Then I guess you need to use OR operator, not AND. As Ferrari is?in?a?different?field?than?where Car resides for "missing row".

SELECT
COUNT(*) FROM CONTAINSTABLE(ADDS,*,'"Ferrari" OR "Car"')|||

than I have to put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.

but in that case I wil retrieve all the unneccessary data from the database.

what about building a table like _ID,VALUE....and in the value field I put the _NAME and _TYPE of the car and full text index that table.

Does this perform better than using "OR"?

|||That's a good idea. If so, are you sure you need Full-Text search? Why not just use LIKE operator on some columns to filter rows??For example:

SELECT
COUNT(*) FROM NAME Like'Ferrari%' AND?TYPE?LIKE '%Car%'

Pay more attention to table schema/relation design will improve your database performance.|||

are you sure you need Full-Text search?

----

As far as ? know >>>>>>>>LIKE '%Car%' can not use indexes in sql 2005 so it much slower than searching tokens in a document using full text.

Do u know any way to see how the sql server uses the execution plan?How can I see which process uses the most RAM?

|||Yes you're right, LIKE 'Car%' can use Indexes, while LIKE '%Car%' can't. However personally I often use LIKE operator instead of Full-Text search if possible.?

There are several ways to check execution plan in SQL2005:

1.?SET?SHOWPLAN?ALL?ON,?which?will?causes Microsoft? SQL Server? not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

2. CaptureSQL Profiler trace with proper Performance events( such as Show Plan All)

3.?Use?new?powerfulExecution Related Dynamic Management Views and Functions

Here is a great article for overall Performance T-Shooting in SQL2005:
Troubleshooting Performance Problems in SQL Server 2005

Enjoy SQL2005!|||

thanks for all the links but esppecially the 3 rd one but I am unable to use

sys.dm_exec_background_job_queue

sys.dm_exec_background_job_queue_stats

sys.dm_exec_cached_plans

sys.dm_exec_connections

sys.dm_exec_cursors

sys.dm_exec_plan_attributes

sys.dm_exec_query_memory_grants

sys.dm_exec_query_optimizer_info

sys.dm_exec_query_plan

sys.dm_exec_query_resource_semaphores

sys.dm_exec_query_stats

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_sql_text

these methods I have used it like

SELECT * FROM sys.dm_exec_connections....but the resultset has no rows...

Do u have an idea why that happens?

|||That means currently no user connection has been established to the SQL instance. Where did you execute the query?|||hmmmm ok. some of them work so I must be missing something thanks anyway.

Full Text Indexing

Hello,
I wanna learn Full Text Indexing feature in MS SQL, Please let me know where could i have a good resource about it. I tried google but i didnt find what i need.

regardsStart with SQL Server's Books Online. Then move on to the resultsthat you get from this Google search: "Sql Server" "Full text indexing"
|||Try the link below for a two part article on Full Text indexing in SQL Server. Full Text is an add on to SQL Server dependent on Microsoft search service and the Catalog must be populated to get expected results for search with Microsoft Proprietry implementation of ANSI SQL CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3454281|||

I have the same problem did u find some good resources?

I am looking foward to find away to scale(ditrubute), the full text engine in my machines.

Please let me know what u have got.

|||

erdsah88:

I have the same problem did u find some good resources?

I am looking foward to find away to scale(ditrubute), the full text engine in my machines.

Please let me know what u have got.

The first thing you have to do is run searches that will not return a whole table because SELECT * returns a whole table so sometimes your predicates are ignored. What I mean is you are telling SQL Server I want the whole table and you then add a predicate which SQL Server consider a trick it gets your query very slow. So spend time with Management Studio and the Profiler to reduce the cost of your query, make sure you run all queries through your application so you know how it affects your performance. Jeff Prosise published a check list for Asp.net application data was one of the most important, I have told you to get a data person you ignored it, you need someone who knows SET Algebra so your tables will be cleaned and all your SELECT * DML(data manipulation language) will be dropped. Microsoft built a new physical architecture for SQL Server 2005 see if you can use the new INDEX COLUMN include and pay attention to the IAM(index allocation mapping) pages. All of the above will take a long time but will clean most of your existing problems. One more thing test drive the new Database Developer VS use it to clean your DML. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/06/07/WebAppFollies/

http://blogs.msdn.com/sqlserverstorageengine/archive/tags/Index+Fragmentation+Series/default.aspx

http://msdn2.microsoft.com/en-us/library/ms190806.aspx