August 22, 2018 at 11:50 pm
Comments posted to this topic are about the item Indexing a Computed Column
August 22, 2018 at 11:51 pm
Nice, easy question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
August 23, 2018 at 12:54 am
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.
August 23, 2018 at 9:54 am
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.
August 24, 2018 at 11:00 am
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
August 28, 2018 at 3:21 am
nice question Steve,
Cheers
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply