May 5, 2009 at 8:20 am
When should I use sp_refreshview?
Here are two scenarios:
1. In the following example, should I call sp_refreshview() on [View B]?
[View A]'s old definition:
SELECT A.a, A.b
FROM tbl1 AS A
[View B]'s definition:
SELECT A.a
FROM [View A] AS A
[View A]'s new definition:
SELECT A.a, A.c, A.d
FROM tbl1 AS A
2. In the following example, should I call sp_refreshview() on [View B]?
[View A]'s old definition:
SELECT A.a, B.b, A.c
FROM tbl1 AS A
JOIN [View B] AS B ON A.b = B.b
[View B]'s definition:
SELECT B.b
FROM tbl2 as B
[View A]'s new definition:
SELECT A.a, B.b, A.d, A.e
FROM tbl1 AS A
JOIN [View B] AS B ON A.b = B.b
May 5, 2009 at 9:39 am
If you are saving these as views then you should not have to refresh. If you are running these with create or alter statements from TSQL you should run refresh after you run view B so that the last statement works for view A.
May 5, 2009 at 10:12 am
JKSQL (5/5/2009)
If you are saving these as views then you should not have to refresh. If you are running these with create or alter statements from TSQL you should run refresh after you run view B so that the last statement works for view A.
Thanks for your reply. I plan to run these with create/alter statements from TSQL. Should I run the refresh for both examples?
you should run refresh after you run view B so that the last statement works for view A.
Which example are you referring to? Or are you referring to both examples?
May 5, 2009 at 10:50 am
Now that I am thinking about it when you run the alter/create have a "GO" statement afterwards you should be good. There should not be a need for a sp_refresh. If you think you need the refresh just run it when you commit the change on the the subquery. ie SELECT B.b
FROM tbl2 as B
SP_refresh should only be used when a field changes ie datatypes. In your example it is joining on the same field so there should not be an issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply