Suggested persisted computed usage is dangerous

[npbenjohnson]

Persisting a column in sql server does not mean that it will never be recomputed, which in some situations can result in extremely expensive queries if you add a hashid column.

Easy reproducible example:
For TestTable: Id: int, Value:string, HashId: (string hashid of Id), create an index on Value, and then query:
select Value, HashId from TestTable order by Value
This will recompute every hash id in the table because the query optimizer believes it is faster to recompute the hashid than read it from the clustered index. For tables with 100,000+ records this can add 10-20 seconds to a basic select query.

Workarounds:

1. Include the hashid column as an included property in every index that may be used in a query which also requires the hashid. This appears to work, but increases the size of indexes.
2. Disable recomputing columns with a traceflag. This is not a clean workaround, the related traceflag is only intended for testing features when upgrading sql server, and the flag that fixes this issue also disables computed column substitution.

Detailed description of what happens: https://stackoverflow.com/questions/5998217/why-does-the-execution-plan-include-a-user-defined-function-call-for-a-computed

[waynebloss]

Thanks. I've quoted this issue in the readme now.

[waynebloss]

Apologies to anyone who ran into a production issue due to this! Hopefully we can now avoid that for anyone else.