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

Question

Hi all,

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

PHP code queries the table with LOWER(@name) = LOWER(name)

Thanks in a world of pain. LOWER(name) triggers a table scan. This is not making it faster, it is - brutally - a beginner mistake that kills performance. You should never compare against a calculation of the table fields, unless you ahve the result in a precaculated index (as you can do in SQL Server 2008, for example).

What about

WHERE name LIKE @name

which should ignore case... plus an Index on the name field.

Answer 2

You have various options:

• declare the column Name case-insensitive using the COLLATE clause

• compare Name and @Name using the COLLATE clause

• create a computed column LowerName AS LOWER(Name) PERSISTED and index this computed column

Answer 3

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