From: Moderated discussion of advanced .NET topics Sent: Tuesday, February 17, 2004 12:29 PM > > 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.
Sure, I agree 100%, but that is not the issue here. The issue is initially using SELECT * versus SELECT [commalist] in views and/or sprocs, and the risks that are involved with either choice in the long-term. Most people bang hard on using the latter option, IMO without satisfactory reasons, stating that it will be "more professional" and "save you work". Your own example illustrates no work is saved. Client apps are saved solely by an attentive DBA/developer who's making the changes, irrespective of the querying method used. The example with physically splitting the Name column implies that one or more old apps depend on the older Name column semantics, and one or more new apps need to have access to the new "split names" columns semantics. The initial use of SELECT * versus SELECT [commalist] is irrelevant; existing code will need to change or new code will be created somewhere to account for the different semantics in different apps. As a manager or team lead, I don't care if it costs time to my DBA or my developer on the project, it's still time and resources used up. A SELECT * in a view prevents column changes/splits/renames from breaking views (and any sprocs going to tables and not views), but there is still some work either in the app layer or db layer to present the new semantics where required, ex: SELECT *, [new calculated column that holds combined values] FROM table. SELECT [commalist] will make work also, in that the new split columns must be manually added and the old Name column be turned into a calculated column. Six of one-half, half a dozen of the other. (Personally, I don't like the idea of a trigger used to split names upon entry. Sometimes, the handling/parsing values isn't clear-cut. Where do you split the Name value "Anna Marie Smith Jr." into FirstName and LastName?) > Again, there's no need to break callers. If by callers, you mean the application(s) in the application layer, then yes, there is no need to break them. But, there is work involved somewhere. The idea presented here seems to be that SELECT [commalisted] will prevent such problems. It won't. It simply replaces some problems with others. (Isn't that always the way? :) ) > I don't think I agree. A view that joins these tables will, of necessity, > qualify the names. I think I misspoke on this tangential subject. I didn't mean to say that the join in the view or sproc will have name clashes, the DB is usually smarter than that, but that the result set (by that, I mean the "column names") might not be as well-qualified in the end-result when handed over to the application layer (say, an Excel export) depending on the system. > Why should code that's referring to just the Perk table be more > verbose than "select ID, Name, Desc from Perk" ?? In that case, it doesn't need to be. But in a joined query of three tables, each with a "Name" column, going into an app layer where the DB's column's name qualification is lost, then yes, there is a need for some "verbosity". There's no harm in it and some (minor) benefit. > 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. Again, I fail to see how SELECT * usage result in distinctly more "breaks" than SELECT [commalist] short of the typical examples like Marc Brooks provided. These examples could easily be prevented by another way of naming columns. Before you say that's just a "workaround", the "considerate DBA" example you posted above as a means to circumvent the problem I illustrate is the same. :) > 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. Apps will still "break" if the semantics of the changes are not translated well in the lower layers. "SELECT PersonID, Name FROM Person" will break if I split columns and no one is there to account for it in the lower layers. There are a variety of ways to handle the change in semantics in the different scenarios, but the point is that neither SELECT * nor SELECT [commalist] will save you in all cases of change. _________________________________ 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
