Persisted Computed column re-computing on select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Persisted Computed column re-computing on select

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2011-01-19 : 09:53:10

What the heck is going on here?

We have a persisted computed column Serviceid_RAW.
varchar(50)
nullable

The forumula is:
(isnull(CONVERT([varchar](50),[dbo].[strippunctuation]([serviceid]),(0)),''))

[ServiceID] is a non-nullable varchar(50) column
Strippunctuation is a function that simply removes non character/numeric values.

Works fine EXCEPT....
If we query the table, concatenating the computed column value with another value, we can see every row is RE-computing! Highly inefficient and confusing.

Example: ([comments is a varchar(1000) column)
This does NOT re-compute

SELECT top 100
S.serviceid_raw
, [comments]
FROM services S

If I run this one, it re-runs the compute logic for EVERY row (as a stored proc):
SELECT top 100
S.serviceid_raw + [comments]
FROM services S

Can someone enlighten us?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-19 : 10:02:22

What happens if you
select serviceid_raw + [comments]
from
(
SELECT top 100
S.serviceid_raw, [comments]
FROM services
) a

with cte as
(
SELECT top 100
S.serviceid_raw, [comments]
FROM services
)
select serviceid_raw + [comments] from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pug2694328
Posting Yak Master

166 Posts

Posted - 2011-01-19 : 10:30:33

Thanks for the thoughts Nigel!

Both of those re-run the compute on every row so no love.
Does someone know the SQL Server underlying logic that's driving the re-compute?

Aside...
You'll need to add an 's' alias to the services table get those to run:


select	a.serviceid_raw 
	+	a.[comments]
from	(
		SELECT top 100
			S.serviceid_raw
		,	[comments]
		FROM services	s
		) a

with cte as
	(
	SELECT top 100
	S.serviceid_raw, [comments]
	FROM services	S
	)
select serviceid_raw + [comments] from cte

pug2694328
Posting Yak Master

166 Posts

Posted - 2011-02-01 : 10:35:00

We worked around it by creating a trigger to keep the column up to date, but still would like to understand the underlying issue we were hitting...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources