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.