Inline At 10:13 AM 2/17/2004, Tiseo, Paul wrote (in part) >[snip] >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
A considerate DBA would not do that. She will either make Name a computed column, or rename the underlying table and make a view Candidate that has a Name column -- even if it also has FirstName and LastName. (Callers can choose which to use.) There's no justification for breaking client code unnecessarily, just because you've made a design decision to change your schema. (This is an example of how views can be extraordinarily useful.) In SQL Server 2000, you could even use an "instead of" trigger to allow callers to write to the Name column of the view -- but that would not be necessary if the Candidate table (view) is edited by code that accesses an up-to-date application-level schema definition. >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]. Again, there's no need to break callers. Column splits can be handled as described above; table splits can be handled the same way. Column add is not an issue unless SELECT * was used. If you really feel the need to drop a column (to save the storage?) you can use a view that returns NULL for that column (or, if it's a character column, something like "*** FIX THIS PROGRAM ***"). Breaking callers is almost never necessary. Sometimes you _want_ to break callers, so you can locate client code that needs to be changed to do whatever new thing you think it is important for all clients to do. Wanting callers to break is somewhat unusual (but easy to accomplish, even if the caller didn't use SELECT *). >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 don't think I agree. A view that joins these tables will, of necessity, qualify the names. (DBAs who write views that don't table-qualify all column names are either not thinking about how to avoid future work, are not using a tool that assists them with this menial task, or both. Similarly, developers who write join-SELECTs without table-qualifying column names should learn to do better.) Why should code that's referring to just the Perk table be more verbose than "select ID, Name, Desc from Perk" ?? >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] I fear that the next previous paragraph sounds like "criticiz[ing] others for using the other way" -- but I strongly feel that professionals do not (and should not) criticize people -- it's fine to criticize ideas, but not to criticize the people who have those ideas. (People who flame rather often seem not to have learned that.) It depends on your goals. If the goal is to make people happy with how quickly you can fix the problem when an app fails, or to get more billable hours in a consulting environment, SELECT * can be a useful technique -- fixing the resulting problems is quite easy, even when you've been paged when on vacation and have to do the fix over a slow modem line. But if your goal is to write apps that don't break, or be a DBA supporting people whose apps aren't supposed to break just because you changed things, SELECT * is a technique that's easily and rather painlessly avoided. J. Merrill / Analytical Software Corp =================================== 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
