About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Wednesday, October 29, 2014

Example: Views can contain old column names

-- Demonstration that a view retains the old column names
-- after renaming a column, or adding a column
CREATE TABLE  Test1 (
      ID int,
      Name varchar(20)
)

INSERT INTO Test1 values (1, 'Hello')
INSERT INTO Test1 values (2, 'Good Bye')

SELECT *
FROM Test1

go
CREATE VIEW vTest1
AS
      SELECT *
      FROM Test1

go
SELECT *
FROM vTest1

EXEC sp_rename 'Test1.Name', 'NNName', 'COLUMN'

SELECT *
FROM Test1

SELECT *
FROM vTest1

alter table Test1 add MyNewCol varchar(20)

SELECT *
FROM Test1

SELECT *
FROM vTest1

DROP VIEW vTest1
DROP TABLE Test1



No comments:

Post a Comment