Does a record exist

Fastest way to check sql stmnt & code

[Argblat]

Hi all,

I'm wondering what the fastest way is in a situation where you have a query a database strictly to find if a record exists or does not exist.

The most important aspect, to me, would be to optimize the code / reduce the number of lines.

If possible, my language of choice is c#

thank you!
-Mike

[txbakers]

make your query as follows:

select field from table where column = yourdata
then,
if EOF, it's not in there.

You can also look up the if NOT EXISTS syntax for some databases, but I think that might take a bit longer (nanoseconds...) to run.

[Argblat]

I guess I should have been more specific in that I'm must more interested in the coding aspect of checking.

I think that a good example is a login page. The user enters their username and password, and you have to go and check against the database...but all you care about is if it is there or not.

As far as coding goes, I finally figured out the right 'google words' and started seeing some interesting articles that talk about ExecuteScalar as the best option to recieve one piece of data from a sql query.

I also think (but have no verified) that adding "select TOP 1" (or a LIMIT in mysql) helps to speed up the process since it stops after it finds that one row you're asking for...but I haven't verified that for any database.

-Mike

[Easy_Coder]

EDIT: Oops... you didn't want rowcount. Ignore the below code.

select rows from sysindexes where id = OBJECT_ID('YOURTABLENAMEHERE') and indid < 2

[duckhunter]

The fastest way as far as SQL Server is concerned is to use the "IF EXISTS" function

IF EXISTS (SELECT FIELD FROM TABLE)
BEGIN
--Do Something if it exists
END