Which is faster to find repetitions?

I have a table with 1 column and I want to check repetition of a value between 10,000 available rows.

I think I have two choices:

Make a query using SELECT statement, like this :

Var = Query('SELECT * FROM Table WHERE
Field1="VALUE"');

if (Var <> null)
  MessageBox("This value exists in the table");

Se开发者_如何学Got my column as Primary Key and use INSERT statement, like this:

try {
    Var = Query('INSERT INTO Table(Field1) VALUES("VALUE")');
}
catch {
    MessageBox("This value exists in the table");
}

Which is faster?

There's no general answer here, it depends upon how your schema is set up. In most (perhaps all?) relational databases, making a field a Primary Key will automatically create an index on that field. And doing the uniqueness check against an index is pretty much as fast as you can get in this case.

But, you can index your field without declaring it your table's Primary Key. And if you do that the SELECT command will be just as fast as the INSERT plus catch method. More broadly, you can only have one Primary Key per table, so making the field the Primary Key is not a very robust solution. It will break as soon as you have multiple fields that you want to enforce uniqueness on (unless you make a compound primary key across both fields...but I digress, and that doesn't enforce per-column uniqueness anyways).

So I would recommend creating an index on your field/column, and then using the the SELECT method to see if the value already exists. Alternately, you can index the field and stipulate this it should be unique, without making it your Primary Key, and use the INSERT plus catch approach.

I'd recommend using select count:

Var = Query('SELECT count(*) FROM Table WHERE Field1="VALUE"');

if (Var > 0) MessageBox("This value exists in the table");

The second approach is not so nice, IMO, and it's probably going to be much slower. And BTW, it should read exists instead of exist :-)

If you want to insert a value, if it doesn't exist, then something like the following would be most appropriate (although different SQL dialects may apply):

INSERT INTO Table(Column)
SELECT 'New Value' WHERE NOT EXISTS (SELECT * FROM Table where Column = 'New Value')

And then checking whether 0 or 1 rows were affected.

Note where I'm saying most appropriate, I'm not making a performance evaluation. I'm talking about the code that most clearly expresses the intent. Usually, this will be good enough. Only rarely should you move away from the code that most clearly expresses your intent, for something less clear that performs 0.5% better...

You should also beware your first format (SELECT * FROM TABLE...), as a general style. If you were querying a large, wide table, performing such a select may cause a lot of I/O to be performed by the database, to retrieve all column values, just for your code to then ignore all of those values. SELECT *... within an EXISTS clause, on the other hand, is specially dealt with by most database engines, and will not retrieve actual row/column data.