lundi 6 juillet 2015

Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?

I have one persisted column based on a XML field, and SQL Server always use "Compute Scalar" when i query this table. Why? is it not persisted?

I suspect I 'm not doing any condition that prevents the column to be truly persisted. This is my table (and the funtion you need to create the persisted column):

create FUNCTION [dbo].[FuncTestPersisted] (@xml XML)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN @xml.value('(/node/@value)[1]','int')
END


create TABLE test_TB(
    [ID] [int] NOT NULL,
    [CustomProps] [xml] NULL,   
    [TestPersisted]  AS ([dbo].[FuncTestPersisted]([CustomProps])) PERSISTED,
 CONSTRAINT [PK_test_TB] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I will really appreciate every suggestion for avoiding the "compute scalar" call. Please, without using triggers (one trigger for each computed colum is....... too much work ;) )

Aucun commentaire:

Enregistrer un commentaire