5

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 ;) )

I have tried with this table also, and the query execution plan still show a "Compute Scalar" calls. WHY?

create TABLE [dbo].[test_TB](
    [ID] [int] NOT NULL,
    [CustomProps] [xml] NULL,
    [TestPersisted]  AS (5*ID) PERSISTED,
 CONSTRAINT [PK_test_TB] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Tirma
  • 664
  • 5
  • 16
  • 2
    [This answer](http://stackoverflow.com/a/5999986/1048425) should help. – GarethD Jul 06 '15 at 09:13
  • I`ve seen this one. Thanks. but the solution is: there is not solution and not reason... :( – Tirma Jul 06 '15 at 10:18
  • @Tirma the link in the answer to MS support forum suggests that in some instances it is possible to rewrite the query so that the value is not recomputed. Maybe try that? – TT. Jul 06 '15 at 10:33
  • 1
    Have a look in the defined values property of the computer scaler. It may not be recalculating the column, it could just be aliasing the column or something else required by the engine and wouldn't have an effect on your queries performance. – Russell Hart Jul 06 '15 at 10:39

1 Answers1

0

GarethD is correct. Persisted columns in SQL are frustrating. You add them thinking they'll improve performance. If you want to persistently cache a value such as ID * 5, or any other calculation for that matter, just add another column and compute the value once and store the result.

user3112728
  • 395
  • 1
  • 12