Indexing a Computed Column

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    Comments posted to this topic are about the item Indexing a Computed Column

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71257

    Nice, easy question, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Thomas Franz

    Hall of Fame

    Points: 3527

    You should / could have mentioned that an index persists the column (but only in the index itself) too.

    Persisting on the table level safes you a little bit CPU at the costs of disk / memory space when quering the computed column by using the Clustered Index / Key Lookup . When you have a simple calculation (as adding or multiplying two small numbers) and you are not CPU bound, persisting does not always make sense. When you need the computed column only, because a query returns you always the top 50 rows depending on the sum / product, it would be enough to create the computed column non persisted and add an index (who will save the persisted results)

    On the other hand, if you use a computed column to save the size or HASH_BYTES() of a VARBINARY(max) column, you should always persists it, since the SQL server otherwise would need to read the whole BLOB columns from disk.

    God is real, unless declared integer.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714668

    It's not really relevant here. If you don't think an index persists the value, that's something you need to learn about indexing.

    I try to get the questions to be narrow and stick to the scope. In this case, it's good to know that you don't need to persist the column in the table, as many people think you do.

  • Chris Harshman

    SSC-Forever

    Points: 41661

    I think what he is saying is that you do not need to make the computed column persisted in the table, you can index a non-persisted computed column and it is stored that way

  • Shayn Thomas

    SSCertifiable

    Points: 5377

    nice question Steve,

    Cheers

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply