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.