What is the point of the Lowered* columns in asp.net membership tables?

Question

What is the architectural reason for the column names prefixed with "Lowered" in the SQL schema for ASP.Net membership and friends? Some examples of the columns in question are below:

• aspnet_Applications.LoweredApplicationName
• aspnet_users.LoweredUserName
• aspnet_membership.LowerEmail

I see that the lowered columns are indexed, but it seems to me that you could just index the associated non-lowered column and leave out the apparent duplication.

I'm sure there is a good reason for them to exist, but I can't figure it out.

Answer 1

There is no purpose for this in a non case sensitive database like SQL Server. This is a reusable database regardless of the type of database in which you are using. E.g. Informix is case-sensitive for all string data which is stored. Using this database on an Informix server would be a good reason to have/use this column instead of lower()'ing the column yourself. I am not saying that you can't do case sensitive searches in SQL Server by any means (varbinary, BINARY_CHECKSUM, runtime/declarative COLLATE, etc.). This would change the functionality of the out of the box database.

The idea of any calculated column is to save cycles on doing those calculations during querying. Most especially during large queries. The other thought is one which you had in indexing those columns. Again, this is done to save cycles.