SQL LOWER (or UPPER) vs Like

I have a table that isn't going to get very big and contains the name of a client account and a user's alias. I'm importing this information from production in order to run a few reports.

To help out with management of the table contents, I'm ad开发者_JS百科ding a simple AddUser stored procedure where first I want to check if the client account/alias combo already exists in the table.

Since I want this check to be case insensitive, I considered using LOWER or like when comparing the values.

Probably in this scenario, I wouldn't see a big difference either way with regard to performance, but it made me wonder if there is a best practice when doing this sort of thing.

many thanks!

LIKE is going to be much faster than the conversion using LOWER, especially the longer the name.

Is the database instance set to a case-sensitive collation for strings? If not, none of that is necessary as all comparisons will be case insensitive.

See here: SQL server ignore case in a where expression

lower(name) = 'Somename' will not use an index, whereas name like 'some%' might.

If you're using SQL Server, you can use a regular compare (=). Unless you change the default setting, SQL Server string comparisons are case insensitive.