Wonder what will happen when a view is created on a table but later on the table column list gets altered using the Alter Table command. Will the view automatically get updated with the new/deleted columns in the table?
Well the answer to this is "OfCourse No". Why? The answer to this is much simple than what might appear. We all know that a view is a simple database object which has its definition stored in the syscomments system table and does not store any of the information anywhere in the database. This is opposite to what a table does. Now when a table gets altered and a column which was earlier present in the table has been dropped then on envoking the view the Sybase ASE server will try to check the view definition which will still be holding the column which had been deleted from the table and hence will give us an error that the required column does not exists.
Lets demonstrate the above concept with a simple example -
First we will create a simple table Employee as defined -
create table Employee
(
EmpId int,
EmpName varchar(100),
Age int
)
go
Now we will create a simple view V on this table as follows -
create view V as select * from Employee
go
After creating this view we will try querying the syscomments system table to find out views definition as stored by Sybase ASE server and the query is -
select * from syscomments where id = (select id from sysobjects where name = 'V')
go
On executing this query we find that the Sybase ASE server has expanded the '*' used in the view to the following -
create view V as select Employee.EmpId, Employee.EmpName, Employee.Age from Employee
go
As we can see the Sybase ASE server has replaced '*' and expanded it with column names. Now when we fire an Alter Table command to add or remove a column like -
alter table Employee
drop Age
go
OR
alter table employee
add Salary float NULL
go
then the alter table command does not rechecks that a view is already created on the table Employee and therefore the view is not corrected with the new or deleted column and hence when we try to execute the command -
select * from V
go
after dropping the Age column from the table Employee then as the Sybase ASE server tries to locate the Age column in the table Employee but is not able to find the same hence the Sybase ASE server tries to throw the error -
Invalid column name 'Age'.
which confirms that the view does not gets updated with the Alter Table command.
Well the answer to this is "OfCourse No". Why? The answer to this is much simple than what might appear. We all know that a view is a simple database object which has its definition stored in the syscomments system table and does not store any of the information anywhere in the database. This is opposite to what a table does. Now when a table gets altered and a column which was earlier present in the table has been dropped then on envoking the view the Sybase ASE server will try to check the view definition which will still be holding the column which had been deleted from the table and hence will give us an error that the required column does not exists.
Lets demonstrate the above concept with a simple example -
First we will create a simple table Employee as defined -
create table Employee
(
EmpId int,
EmpName varchar(100),
Age int
)
go
Now we will create a simple view V on this table as follows -
create view V as select * from Employee
go
After creating this view we will try querying the syscomments system table to find out views definition as stored by Sybase ASE server and the query is -
select * from syscomments where id = (select id from sysobjects where name = 'V')
go
On executing this query we find that the Sybase ASE server has expanded the '*' used in the view to the following -
create view V as select Employee.EmpId, Employee.EmpName, Employee.Age from Employee
go
As we can see the Sybase ASE server has replaced '*' and expanded it with column names. Now when we fire an Alter Table command to add or remove a column like -
alter table Employee
drop Age
go
OR
alter table employee
add Salary float NULL
go
then the alter table command does not rechecks that a view is already created on the table Employee and therefore the view is not corrected with the new or deleted column and hence when we try to execute the command -
select * from V
go
after dropping the Age column from the table Employee then as the Sybase ASE server tries to locate the Age column in the table Employee but is not able to find the same hence the Sybase ASE server tries to throw the error -
Invalid column name 'Age'.
which confirms that the view does not gets updated with the Alter Table command.
No comments:
Post a Comment