What is the best way to verify the existence of a record in the database via application?

Question

Today I use a method that sends a COUNT with the record id as a parameter for the database. There is a better, perhaps more performative way to do this?

Answer 1

Hello, It would be good to put the code used in the application.

With access to database I use as follows.

Select 1 from tabela where coluna=valor

Simple and Functional.

Additional Data:

• Using in Real Data

• Table with 1,541,770 records.

• SGBD: Postgresql

Being searched through a field that makes up the primary key

SELECT count(*) FROM parcela_prev WHERE  cor_cod = 'x' 

Total query Runtime: 916 ms.
1 Row retrieved.

SELECT 1 FROM parcela_prev WHERE  cor_cod = 'x' 

Total query Runtime: 949 ms.
26299 Rows retrieved.

In the real environment gave little difference, following by logic in both cases when returns 1 or more records the first option became more performatic.

Answer 2

As far as I know this form is good and there is nothing that brings significant improvement. Just this:

SELECT COUNT(id) FROM tabela WHERE coluna = valor

I put in the Github for future reference.

But this only works if you just want to verify the existence itself. If you will then give a INSERT, UPDATE or do something else that depends on whether or not the record exists, then you are doing something wrong because you may incur running condition.

Answer 3

As @Andrew mentioned, I prefer to use it too:

select 1 from tabela where coluna=valor

Out of curiosity, in SQL Server you may need something like this if you need to create an SQL script that needs to make a decision upon the existence of a record:

if exists (select 1 from tabela where coluna=valor)
begin
    //SQL
end