Computed column in view does not result in correct value

Last Post 21 Nov 2008 05:09 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

20 Nov 2008 10:58 AM

We have a view definition with a calculated column in SQL2005, as in:
create view v_test as
select *, patindex( '%abc%', col1 ) as MatchCandidate
from tableX

MatchCandidate should contain non-zero if string 'abc' is present anywhere in tableX.col1

This view is then used by a number of queries/SPs as in the following example:

select * from v_test
where MatchCandidate <> 0

All was working correctly until only a subset of rows were being retrieved by this query.
When debugging, the query above was rephrased as:

select MatchCandidate, patindex( '%abc%', col1 ) as MatchCandidate2
from v_test

and the two columns had different values! the view's MatchCandidate values were 0 whereas MatchCandidate2 values were non-zero.

So the view's computed column was not being computed dynamically.

Even though the underlying table's structure was not changed in any way, [sp_refresh v_test] was executed. After this both the computed MatchCandidate & MatchCandidate2 had the same resulting values.

Any ideas why this would happen and how to ensure this doesn't happen again?

New Member
New Member

21 Nov 2008 05:09 AM

Welcome to SQL Server Magazine Forums!

I was unable to reproduce it with AdventureWorks.

Are you up-to-date with service packs?

use AdventureWorks;
create view vPatIndexTest
as select ProductID, ProductName = Name, ListPrice, Color,
PATI = PATINDEX('%r%', Name)
from Production.Product

select *, PATI1 = PATINDEX('%r%', ProductName)
from vPatIndexTest
where PATI != PATINDEX('%r%', ProductName)

Kalman Toth, SQL Server 2008 Training
New Member
New Member

21 Nov 2008 07:38 AM
Thanks for your follow up.

My example is an oversimplified one...
The key here is that we can no longer reproduce the anomaly after using sp_refreshview. This is a cause for concern because we can't be certain if/when/why this issue arose in the first place.

Acceptable Use Policy