Home > Articles > Data > SQL

  • Print
  • + Share This
From the author of

Changes to Underlying Objects

Be on the alert for issues arising from modifications of the tables or views on which views are constructed.

SELECT * View

One special problem is the view based on a SELECT * statement. A query of the view runs without generating error messages, but does not display columns added to underlying objects after the view was created. This is because the asterisk (meaning "show all columns") is expanded at view creation time, not at execution time. In the following example, the addon table starts out with two columns.

create table addon
(name char(5),
num  int)

insert into addon
values ('one' , 1)

After a view is created, someone adds a column to the table.

create view addonv
as
select *
from addon

alter table addon
add status char(1) null

A query of the table shows three columns.

select *
from addon

name  num         status 
----- ----------- ------ 
one   1           NULL
(1 row(s) affected)

A query of the view shows only two columns—the two that existed when the view was created.

select *
from addonv
name  num     
----- ----------- 
one   1
(1 row(s) affected)

Column Names

Another issue to look out for is changes in column names. Let's say you have a view based on two tables. It gets the publisher name from the publisher table and uses the titles table to calculate the number of books each publisher has.

create view pubvu
as
select pub_name as Publisher, count(title_id) as Titles
from publishers, titles
where publishers.pub_id = titles.pub_id
group by pub_name

select *
from pubvu

Publisher                                Titles   
---------------------------------------- ----------- 
Algodata Infosystems                     6
Binnet & Hardley                         6
New Age Books                            6
(3 row(s) affected)

Now consider what happens when you add a pub_name column to the titles table.

alter table titles
add pub_name varchar(4)
The command(s) completed successfully.

The change is without apparent effect on pubvu until you query the view, when you get an error message telling you the pub_name column in the view (which used to run fine!) is ambiguous.

select * 
from pubvu
Server: Msg 209, Level 16, State 1, Procedure pubvu, Line 3
Ambiguous column name 'pub_name'.

You can prevent this by giving the full name of all columns in queries.

drop view pubvu
The command(s) completed successfully.

create view pubvu
as
select publishers.pub_name as Publisher, count(titles.title_id) as Titles
from publishers, titles
where publishers.pub_id = titles.pub_id
group by publishers.pub_name
The command(s) completed successfully.

select *
from pubvu

Publisher                                Titles   
---------------------------------------- ----------- 
Algodata Infosystems                     6
Binnet & Hardley                         6
New Age Books                            6
(3 row(s) affected)
  • + Share This
  • 🔖 Save To Your Account