Full Text Search "NOT Contains "

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Full Text Search "NOT Contains "

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-25 : 09:53:40

If Full Text Catalog is created
then Is it possible to use "CONTAINS" predicate
as "NOT CONTAINS"

We can do following using CONTAINS as below,

SELECT EmpID,taskname
FROM Employee
WHERE CONTAINS(taskname, 'billable expense AND billable expenses');

I want to know is it possible to do

SELECT EmpID,taskname
FROM Employee
WHERE NOT CONTAINS(taskname, 'billable expense AND billable expenses');

I havent tried either but want to know can we do Full text using
NOT CONTAINS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:10:26

there's no NOT CONTAINS

but you can achieve equivalent functionality as below i guess


SELECT EmpID,taskname
FROM Employee

EXCEPT

SELECT EmpID,taskname
FROM Employee
WHERE CONTAINS(taskname, 'billable expense AND billable expenses');

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-25 : 10:17:45

Thanks Visakh,

But I was trying to use the CONTAINS in single query
for performance tuning issue i am facing
( even u looked in)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177039

Now in order to reduce Index scan Cost % . i was truying to use CONTAINS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:25:56

ok...then isnt it enough to populate a temporary table with contains result, index it and then use it along with master table in NOT EXISTS condition to get excluded result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-25 : 10:47:03

But my query is

UPDATE
@temp_Project
SET
EACRevenue = pbrs.EACRevenue
FROM
(select p.ProjectCode,
p.RevisionNum,
sum(p.EACRevenue) AS EACRevenue
FROM (Select a.ProjectCode,
a.RevisionNum,
ISNULL(a.NegotiatedChargeAmt,0) AS EACRevenue
from ProjectRuleBillingSchedule a, TaskRule tr, Task t
where a.ProjectCode = tr.ProjectCode
AND a.RevisionNum = tr.RevisionNum
AND a.TaskUID = tr.TaskUID
AND a.ProjectCode = t.ProjectCode
AND a.RevisionNum = t.RevisionNum
AND a.TaskUID = t.TaskUID
AND t.TaskName not like 'Billable Expense%'
AND tr.BillTypeCode = 'F')p
GROUP BY p.ProjectCode, p.RevisionNum) pbrs,
@temp_Project TP
WHERE TP.ProjectID = pbrs.ProjectCode
AND TP.RevisionNum = pbrs.RevisionNum

and u are suggesting
1) to put the taskname from Task table into some temp table first
and
2) then Join it in above query.

If thats case then do u think it reduces the performance or we are allowing Engine to do more work ?

But u suggested intersting point.But m not sure does it help in performance tuning?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 11:33:57

what all indexes does task table have curently?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-25 : 13:50:18

Task table has
NonClustered index on
ProjectCode ,RevisionNum ,TaskUID

and i created separate one more Non Clustered index on column=taskname as it has condition in where clause (AND t.TaskName not like 'Billable Expense%')

Now how can be index scan converted into seek ( as cost of index scan is more 48% which i think is bcoz of like operation).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources