November 22, 2005 at 12:23 pm
I found an issue when a view is created using "SELECT * FROM..." format and thought will share with all.
When a new column is added to a table using Enterprise Manager not at the end of the list but in the middle then the view, that uses "select * from.." format, keepts the name of the old column but displays new values.
Try this in Northwind or Pubs.
(1) create following table and insert a row
CREATE TABLE emp (empno INT, active BIT)
INSERT INTO emp VALUES (100,1)
(2) create following view using above table
CREATE VIEW emp_v AS SELECT * FROM emp
(3) Now do a select from both table and view and everthing is OK
SELECT * FROM emp
SELECT * FROM emp_v
(4) Now go to Enterprise Manager and select the table "emp" and go to "design table" and insert a new column "hight varchar(10)" BEFORE column "active"
(5) come back to query analyzer and update the value of this new column to something say "5 feet"
UPDATE emp SET hight = '5 feet'
(6) Now do a select from view it keeps the name of the second column as old column name (active) but displays the value of new column (hight i.e. '5 feet')
of course not too many people alter table using enterprise manager and moreover a new column should not be added in the middle and we should avoid using "select * from ..." in the views, also above problem can be fixed by refreshing the views (sp_refreshview) but still it's not good for sql server to display column name with values from some other column without giving any error
November 22, 2005 at 2:35 pm
Thanks for sharing. One more reason not to use select *
November 23, 2005 at 7:54 am
Your problem has nothing to do with Enterprise Manager. No matter how you add a column to a table, a previously created view will not handle the table as you are expecting.
Views are not text objects, like "SELECT * FROM MyTable," that are interpreted at run-time. They (views) are compiled objects based on the schema at the time they were compiled, and cannot know anything about new columns. If the table design is modified then the views must be dropped and re-created.
Explicitly including the columns you want to return instead of using "Select *" is a good standard, but following that standard does not eliminate any work. You still must DROP and re-CREATE, or, at least, ALTER your view after modifying the table.
November 23, 2005 at 8:43 am
Thanks R. Brush.
I understand and I agree with what you described. But what I didn't know until I encountered this problem was that the view keeps the column position/number and not the column name from the schema when it was created.
Because if you look at the above example the column "active" is still there (and it was there when it was created) and I was not expecting to see new column (hight) in the view but why should it show the column title as "active" and values from some other column (hight)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply