• deepak.a (10/15/2010)


    Hi Hugo which is the best pratice for Computed columns having PERSISTED or without having PERSISTED option or having an index for the computed columns

    It depends.

    For non-indexed computed columns, if the data modifies often and is not queried often, you should not persist it.

    If the data seldom modifies and the persisted column is often queried, you should persist it.

    If the data seldom modifies, the persisted column is sometimes queried and the other columns are often modified, it's an edge case. You save on evaluatig the expression when reading the computed column, but the extra bytes in each row slow down all accesses to the table.

    EDIT: Removed a line where I erroneously claimed that persisting is required to index a computeed column. See the post by Henrik that follows this one.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/