Storing a pre-processed varchar column in the database along with the original one

Question

I have a big table with names and surnames. People search this database via a Web interface. PHP code queries the table with LOWER(@name) = LOWER(name). In order to make the search faster, I want to make a derived column in the table named lower_name and always LOWER() the names before storing. But when it comes to send the results to web interface, I want to show the original ones. However, I don't want to change the table structure. Is there some kind of Index that automatically does the trick or an option to create an "invisible column" in SQL Server or something like that?

Answer 1

You can create a presisted computed column with an index on it:

ALTER TABLE YourTable ADD lower_name AS LOWER(name) PERSISTED 
go
CREATE NONCLUSTERED INDEX IX_YourTable_lower_name 
ON YourTable (lower_name)
go

You do not INSERT or UPDATE this column, the DB will do it for you and always keep it in sync with your "name" column.

If you don't want to use a computed column you could create a view, that has the LOWER(name) AS lower_name column in it and put an index on that column:

http://msdn.microsoft.com/en-us/library/cc917715.aspx