|
|
Posted by Erland Sommarskog on 04/17/06 11:19
Peter (someone@someplace.com) writes:
>> CREATE TABLE dbo.t1 (x INT NOT NULL PRIMARY KEY, z1 INT NULL);
>> GO
>> CREATE VIEW dbo.v1 AS
>> SELECT * FROM dbo.t1
>> GO
>> ALTER TABLE dbo.t1 DROP COLUMN z1 ;
>> ALTER TABLE dbo.t1 ADD z2 INT ;
>> GO
>> EXEC dbo.sp_recompile 'dbo.v1' ;
>> /* Notice that the second column still exists as Z1 in the view */
>> SELECT x,z1 FROM dbo.v1 ;
>>
>
> O that is bad!!! I gonne test that immidiatly tuesday. Is it different
> when naming the columns?
Yes, then you get an error when you try to access the view.
Then again, what David's example really shows is that you should use
sp_refreshview when you've changed the underlying table, not sp_recompile.
To wit, with sp_refreshview, v1 picks up the changed in columns.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|