Persisted computed columns ... A good idea? Hello!
Computed columns are supported by SQL Server since version 7 ... In contrast, SQL Server 2005 can now make these persistent columns.
Indeed, until then, the columns were calculated virtual columns, ie columns that were not written to disk. Being able to make a persisted computed column is the act of making this column physics, and therefore store data inherent in this column on the data disk.
SQL Server 2000 had already brought the opportunity to create indexes on computed columns, thus improving greatly the performance of some applications, particularly in the field of data consolidation. The paradigm that remains etched in the collective memory is the consolidation of revenue by month.
Indeed, consider the following table:
CREATE TABLE dbo.Vente
(
VenteID INT IDENTITY (1, 1) NOT NULL
CONSTRAINT PRIMARY KEY CLUSTERED PK_Vente ,
DateVente SMALLDATETIME NOT NULL,
MontantVente MONEY NOT NULL
) A motion to consolidate these results the month of January would be: SELECT
SUM (MontantVente) FROM dbo.Vente WHERE DATEPART (MONTH , DateVente) = 1
However, if the volume is large, this query can quickly become very expensive. If the solution to index the column DateVente does nothing in our case, it can be very useful to use a computed column:
ALTER TABLE dbo.Vente ADD AS MoisVente DATEPART ( MONTH , DateVente) This column using a deterministic, it is possible to add an index. To this end, a number of options must be correctly positioned:
SET ANSI_NULLS ON SET
ANSI_PADDING ON
SET ON ANSI_WARNINGS
SET ON ARITHABORT
CONCAT_NULL_YIELDS_NULL SET ON SET QUOTED_IDENTIFIER ON
SET OFF NUMERIC_ROUNDABORT Then it is possible to create the index:
CREATE INDEX NONCLUSTERED IX_MoisVente ON dbo.Vente ( MoisVente ) Before our optimized query: SELECT
SUM (MontantVente) FROM dbo.Vente WHERE MoisVente = 1 The result is pretty convincing, and can be extremely rewarding.
The question that arises is what may well provide the persistence of such a computed column. Indeed, the argument raised regularly indexing of the column seems to fall into the water ... As to the conditions of use indexes on computed columns, they prove that the identical value made persistent or not, with the exception of determining the function used ...
Why clutter data discs obviously unnecessary?
The first track would be the use of nondeterministic functions, or that it would be difficult to know if they are, especially as regards the CLR functions.
But we must also look to cases more intensive calculations: the persistence takes all its interest in requiring complex calculations significant CPU resources, especially when the volume of changes is low.
In these cases, the calculation on the fly of our column necessarily entails a slowdown of the system and justifies the persistence of this calculation.
Or, here are the applications of these persisted computed columns ...
