From: Moderated discussion of advanced .NET topics
Sent: Monday, February 16, 2004 8:17 PM
>
> -- and the same line from above now fails... simply because the view used
> a SELECT *
Well, one last word from me. Feel free to add, but I think my own viewpoint
doesn't need further explaining, so I'll refrain from lengthening the thread
should you reply to this.
Okay, so then let's do what you say then:
ALTER VIEW PeoplesPerk
AS
SELECT dbo.Candidate.ID, dbo.Candidate.Name, dbo.Candidate.DrivingPerk,
dbo.Perks.PerkID, dbo.Perks.Description
FROM Candidate
LEFT JOIN Perks ON Candidate.DrivingPerk = Perks.PerkId;
GO
This now works. (By that I mean the view, as defined by you, doesn't break)
We can do:
ALTER TABLE Perks
ADD (Name CHAR(40) NULL);
UPDATE TABLE Perks
SET Name = Description;
Two problems with the whole post, though:
A) The first is the restricted consideration of the impact of the use of
SELECT *, especially since you don't seem to contrast it with the risks of
SELECT [commalisted columns]. What if I now need to split the column
dbo.Candidate.Name into dbo.Candidate.LastName and dbo.Candidate.FirstName?
ALTER TABLE Candidate
ADD (FirstName CHAR(40) NULL)
ALTER TABLE Candidate
ADD (LastName CHAR(40) NOT NULL)
ALTER TABLE Candidate
DROP COLUMN Name
Now we break the exact same code again, but for a different reason. Back to
my original point, which you missed: That's why I don't have a problem with
SELECT *. It doesn't help to do it one way or another; there's no clear-cut
winner. You trade one set of potential risks with a different one. What is
more likely for whatever table you are about to create: a column split, a
table split, a column add/drop? I certainly don't know. People need to ask
themselves that before picking SELECT * vs. SELECT [commalisted columns].
B) In your particular example, it is a bad practice to have similar column
names across tables, especially any that have any chance of being joined.
Attributes with names like 'Name', 'Type', 'ID', 'Code', 'Desc', 'Comment',
etc. should include the table name in the attribute name. This would have
made your last SQL statements in your example not be problematic. This will
prevent name clashes at the level of joined views and sprocs. Also, it helps
with ad hoc querying such that the column names don't have to be renamed in
the result set:
CREATE TABLE Candidate
(
CandidateID INTEGER NOT NULL
, CandidateName CHAR(40) NOT NULL
, DrivingPerk INTEGER
);
CREATE TABLE Perk
(
PerkID INTEGER NOT NULL
, PerkName CHAR(40) NOT NULL
, PerkDesc CHAR(40) NOT NULL
);
I prefer SELECT *, because I think the risks in that scenario is low, and
made lower by proper application of other practices. But, I also don't care
enough to criticize others for using the other way! :)
Have a nice day.
_________________________________
Paul Tiseo, Systems Programmer
Research Computing Facility
Mayo Clinic Jacksonville, Griffin 371
[EMAIL PROTECTED]
===================================
This list is hosted by DevelopMentorŪ http://www.develop.com
Some .NET courses you may be interested in:
NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles
http://www.develop.com/courses/gaspdotnetls
View archives and manage your subscription(s) at http://discuss.develop.com
