Menu
I have a query in SQL Server like this
Alter table inventory
Add totalitems as iteminstore + iteminwarehouse PERSISTED`
What is the advantage of writing the persisted?
266
PERSISTED. Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.
A persistent staging table records the full history of change of a source table or query. The source could a source table, a source query, or another staging, view or materialized view in a Dimodelo Data Warehouse Studio (DA) project. In a persistent table, there are multiple versions of each row in the source.
In Jupyter Lab, the %sql magic command persist creates a new table in the database to which we are connected. The table name will be the name as the name of the Python variable.
Persisted computed columns It means that SQL Server physically stores the data of the computed columns on disk. When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically.
The computed value is persisted into the table, as if it were a normal column value.
If you don't have PERSISTED, then the value is computed every time the column is accessed.
Nicely and more extensively documented in the official MSDN documentation for computed columns:
PERSISTED
Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
