Condition NOT EXIST in Sp - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Condition NOT EXIST in Sp

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-11 : 01:24:46

Iam using query as specified below :-

INSERT INTO tbl_emp_info
( emp_id,
task_id,
person_code,
followup_dt,
actual_close_dt,
)

SELECT
employee_id,
task_id,
code,
followup_date,
close_date,
FROM
tbl_emp_tracking_info
WHERE
employee_id=@emp_id AND
processed_flag='N' AND
NOT EXISTS( SELECT * FROM tbl_emp_info WHERE employee_id=@emp_id and task_id=tbl_emp_tracking_info.task_id)

Now the basic need is to insert the information from tbl_emp_tracking_info to tbl_emp_info for which they don't have a emp_id and task_id.

If they have it then I need to write a update stored procedure.
I know the above one works but :
Instead of the above exist what if this one is in place

NOT EXISTS( SELECT emp_id FROM tbl_emp_info WHERE employee_id=@emp_id and task_id=tbl_emp_tracking_info.task_id)
OR
NOT EXISTS( SELECT 1 FROM tbl_emp_info WHERE employee_id=@emp_id and task_id=tbl_emp_tracking_info.task_id)
OR
NOT EXISTS( SELECT emp_id,task_id FROM tbl_emp_info WHERE employee_id=@emp_id and task_id=tbl_emp_tracking_info.task_id)

Which one gives better performance

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-11 : 01:53:49

not exists (select * from...where...)

to verify, check the execution plan

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-11 : 01:58:39

I think all will have same performance. Set the execution plan and see

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources