SQL Server Performance Forum – Threads Archive

Checking Existance of a record

Can anyone tell me if this is the best way (performance-wise) to check for the existence of a record in a table given certain criteria: SELECT TOP 1 ‘True’ FROM mytable WHERE myCriteriaExpression Example: SELECT TOP 1 ‘True’ FROM dbo.[Orders] WHERE customer_id=’123′

The best option is IF EXISTS: IF EXISTS (SELECT T.CustomerID FROM db :confused:

rders T WHERE T.CustomerID = @CriteriaString)
BEGIN
……………
END The EXISTS keyword is like the TOP 1 clause: the query stops after one match is found. This way it doesn’t really matter if your criteria make up a unique key or not. I would guess your plan was to use something like this: SELECT TOP 1 ‘True’ FROM mytable WHERE myCriteriaExpression
IF @@ROWCOUNT > 0
BEGIN
…………….
END … but I think the IF EXISTS syntax makes the flow of the procedure somewhat easier to follow.

IT doesnt matter what you have in your SELECT list. SQL Server will exit out of the condition as soon as it finds the first row that matches your condition in your "myexpression". So you can put TOP 1, <column list> or 1. Its all the same. <pre id="code"> IF EXISTS ( SELECT * FROM yourTable WHERE <Condition&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> BEGIN …… END </pre id="code"> *********************** Dinakar Nethi SQL Server MVP *********************** http://weblogs.sqlteam.com/dinakar/</a>

It usually doesn’t hurt to name a specific column. I have seen problems in triggers with text columns, where the * would fail. Better safe than sorry.

Thanks for the replies, everyone. What I am actually looking for is a stand-alone SP. In other words, I don’t need to check for the existence of a record as part of a larger SP. THis is why I’m not sure EXISTS applies.

You can embed these statements on to a Stored procedure and run that based on your criteria, your questions sounds like a classroom exercise. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>