I have a number of columns that store data that may contain some extra characters for formatting/grouping. Think white space and dashes in data like phone numbers and license plate numbers.
When querying, we can't be sure if and where these dashes are present, so we need to strip them from the column. Doing so while querying isn't great for performance, so I want to create a computed column for the stripped column and put an index on that.
-- trimming the length down to 50 characters -- which is the length of the original field ALTER TABLE foo ADD search_license_plate AS (LEFT(REPLACE(license_plate, '-', ''), 50)); CREATE INDEX ix_foo_search_license_plate ON foo(search_license_plate);
These computed columns are always precise, so they don't need to be persisted because of a lack of precision.
The data will typically only be entered once and will rarely be altered. Never in high volumes, so I don't care much about any performance impact of persisting the column.
Also, the computed column will never be returned from a query; it will only be used in conditions on the query, typically
SELECT foo.id, foo.license_plate FROM foo WHERE foo.search_license_plate LIKE '%bar%';
... although I can't guarantee that there will be no
SELECT * done on these tables.
Is there any need to persist these columns?