Sql-server – ny need to persist a (precise) computed search column that I’ve indexed

computed-columnindexsql server

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?

Best Answer

Is there any need to persist these columns?

Generally, no, though there are a number of potential issues that will require validation with your particular workload and client applications.

The major requirements (including SET option requirements for all clients) are listed in the documentation at Indexes on Computed Columns. You may also, for example, be required to add the PERSISTED attribute for a number of reasons e.g. to add a CHECK or NOT NULL constraint.

The reason I have emphasised the need to test above is that SQL Server will sometimes choose not to use an index on a computed column (even if persisted!) in a range of scenarios that are tricky to enumerate in advance - and where using the index is "obviously" the best strategy. For some examples and background, please see my article Properly Persisted Computed Columns.

The upshot is that each query needs to be tested to ensure that SQL Server reliably chooses the index where that strategy is important for performance.

You can encourage selection of the index access method by making it covering for the target queries (so the index contains all columns needed by the query), but even this does not come with a guarantee. Using an index hint does come with a guarantee, but has risks including throwing an error if the index is unavailable.

There is also a limit to which a b-tree index can assist for leading-wildcard searches as given in the question:

WHERE foo.search_license_plate LIKE '%bar%';

At best, this will result in a full scan of the index on the computed column. You may also be interested in Trigram Wildcard String Search in SQL Server as an alternative.

Related Question