Not Exists

Question

Not Exists

ramana3327

SSCoach

Points: 19722
More actions
May 31, 2014 at 3:44 pm

#396843

Hi,
I am working with a query. I am looking for an alternative to not exists. Is the exception works instead of not exists
set @studentID = (select stdentID from student where classID = @classID and registeredNumber = @RegisteredNumber);
DELETE TableB WHERE studentID = @studentID
IF NOT EXISTS (SELECT 1 FROM @TableB)
BEGIN
--Moving data from staging tables to main tables when @TableB data is not passed.
INSERT INTO TableB (...)
select (...) from Staging
where studentid= @studentid
else
begin
insert into tableB (..)
select (..)
from TableC

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why are you looking for an alternative to NOT EXISTS?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

I second Gail's question. Why are you looking to replace WHERE NOT EXISTS? Unless there's a performance or resource usage problem with it (which might just need a bit of tweaking), I'd leave it alone.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 3

I heard that for large table we can't use exist or not-exit will be problem.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ramana3327 (6/1/2014)
I heard that for large table we can't use exist or not-exit will be problem.

And you tested that statement and confirmed it to be true?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19722 More actions · Answer 5

Actually I am getting some syntax error while executing that, so in the mean while I posted here to find any alternative.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

First test and see if there's a gram of truth in that statement and whether you even need to be looking for an alternative.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

ramana3327 (6/1/2014)
Actually I am getting some syntax error while executing that, so in the mean while I posted here to find any alternative.

ramana3327 (5/31/2014)
DELETE TableB WHERE studentID = @studentID
IF NOT EXISTS (SELECT 1 FROM @TableB)

Does @TableB exist or should you be referencing TableB? What's the error message?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden