Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not Exists Expand / Collapse
Author
Message
Posted Saturday, May 31, 2014 3:44 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 550, Visits: 665
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
Post #1576389
Posted Sunday, June 1, 2014 10:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 39,968, Visits: 36,327
Why are you looking for an alternative to NOT EXISTS?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1576425
Posted Sunday, June 1, 2014 10:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1576428
Posted Sunday, June 1, 2014 2:11 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 550, Visits: 665
I heard that for large table we can't use exist or not-exit will be problem.
Post #1576437
Posted Sunday, June 1, 2014 2:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 39,968, Visits: 36,327
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 2008, MVP
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

Post #1576438
Posted Sunday, June 1, 2014 2:54 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 550, Visits: 665
Actually I am getting some syntax error while executing that, so in the mean while I posted here to find any alternative.
Post #1576439
Posted Monday, June 2, 2014 1:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 39,968, Visits: 36,327
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 2008, MVP
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

Post #1576473
Posted Monday, June 2, 2014 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 6,748, Visits: 13,890
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1576556
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse