Thursday 3 January 2013

SQL Server PERSISTED Columns

SQL Server PERSISTED Columns (SQL 2005+)

Normal calculated column values are calculated each time a query references them.

Implementing a calculated column as PERSISTED physically stores results in the table.

PERSISTED Calculated Columns
+ Give Better Performance
+ You can create indexes on them
- Take up storage space

Calculated Column
ALTER TABLE invoice ADD invoicetotal AS itemtotal + itemtax



PERSISTED Calculated Column
ALTER TABLE invoice ADD invoicetotal AS itemtotal + itemtax PERSISTED



Links :

Using Computed Columns in SQL Server with Persisted Values
Top 10 Hidden Gems in SQL Server 2005
Persisted Columns Problem : Persisting a time difference

No comments: