You're right -- if you need your applications to fully grok the updated schema, then the applications will need to change. My point was that it's quite possible to make many kinds of significant schema changes that end up having no impact at all on applications, provided that the applications avoid constructs like SELECT * and INSERT without a column list.
It is uncommon for an application (using current MS technologies) to be able to handle changes to the columns returned by SELECT * without some kind of "hard error" that crashes the app, rendering it useless until modified and redeployed. If for no other reason than that you want to be able to roll out changes to your different apps on different schedules, and to make the database schema changes "when ready" to avoid delaying deployment and testing of a new app that requires the changes, I think SELECT * should be avoided. But there's no law that says we have to agree -- we don't even know each other! <g> At 11:24 AM 2/18/2004, Tiseo, Paul wrote >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] 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
