enrique_aeo
asked on
Computed Columns
hi experts, can yo explain me with transact sql code this
"You may be asked if non-persisted computed columns can improve performance. Unfortunately the answer to this covers material that is advanced for this point in the course but included here for trainer reference. Computed columns can help with performance, even when they are not persisted and not referenced directly. If you have a predicate like: unitprice * quantity > 2500 Sql Server cannot make any prediction on rows returned but if you create a computed column on unitprice * quantity, even if it is not persisted, SQL Server can and will create a statistic on it, which can lead to a much better plan. And this works, even when you not directly reference the computed column, as the query optimizer recognizes that it is the same expression. "
"You may be asked if non-persisted computed columns can improve performance. Unfortunately the answer to this covers material that is advanced for this point in the course but included here for trainer reference. Computed columns can help with performance, even when they are not persisted and not referenced directly. If you have a predicate like: unitprice * quantity > 2500 Sql Server cannot make any prediction on rows returned but if you create a computed column on unitprice * quantity, even if it is not persisted, SQL Server can and will create a statistic on it, which can lead to a much better plan. And this works, even when you not directly reference the computed column, as the query optimizer recognizes that it is the same expression. "
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As always, it depends on the specific queries.
Under the right conditions, this will have a significant beneficial impact on the SQL plans generated.
In other cases, it will have virtually no impact.
Under the right conditions, this will have a significant beneficial impact on the SQL plans generated.
In other cases, it will have virtually no impact.
As always, it depends on the specific queries
Absolutely. And the data population. But, in this case we are talking about non-persisted columns and to me that implies non-indexed, as well.
Non-persisted doesn't "imply" non-indexed, it guarantees it. You can't possibly index a non-persisted column.
Not being indexed doesn't prevent statistics on it from affecting the final query plan, though.
Not being indexed doesn't prevent statistics on it from affecting the final query plan, though.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then SQL will persist it for you. You cannot index a non-persisted column.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER