0

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 adding 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!

earthling
  • 5,084
  • 9
  • 46
  • 90

4 Answers4

2

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

Benjamin Anderson
  • 1,376
  • 10
  • 15
  • 1
    If you use `LIKE Name%` then yes - that might use an index, if present. If you use `LIKE %Name%` then pretty certainly no, since that expression will not be able to use any indices if present.... – marc_s Dec 16 '10 at 06:23
2

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

Community
  • 1
  • 1
Joe
  • 41,484
  • 20
  • 104
  • 125
  • cool, so regardless, I can always provide an alternate collation. – earthling Dec 16 '10 at 00:23
  • Yes, but generally speaking most SQL Server instances are case insensitive, so you generally won't have to worry about it. – Joe Dec 16 '10 at 01:11
  • 1
    @Joe, until you have to. For instance, postgres is case sensitive always. MySQL is case sensitive on Linux and case insensitive on windows. – treaz Apr 23 '14 at 08:19
2

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

Ronnis
  • 12,593
  • 2
  • 32
  • 52
1

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.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • 3
    This is often true but not always the case, you have to be using a collation that specifies case insensitivity for it to work. You can find out your collation by running `SELECT DATABASEPROPERTYEX('dbName', 'Collation')` which will output results something like `SQL_Latin1_General_CP1_CI_AS` the second to last part of that string tells us, CI = case insensitive and CS = case sensitive – Robb Dec 16 '10 at 00:07
  • Many sql server instances I've worked in have been set to case sensitive. To be safe, I'd like to know a best practice if there is one. Looks like I can set an alternate collation on the query regardless of the current setting. – earthling Dec 16 '10 at 00:24