asked
Replace a field in a view using SELECT *
I am customizing a system that is driven on views. I need to create a view that replaces an existing field with a calculated amount but still use * so that the view always has all the fields the underlying table (or view) has. For example say the table has columns Aaa, Bbb, Ccc, Ddd and my view needs to replace Bbb. I need to do something like SELECT (1+1) AS Bbb, * FROM [MyTableOrView] but this will result in two Bbb columns. If I do SELECT Aaa, (1+1) AS Bbb, Ccc, Ddd From [MyTableOrView] this will fail if a new column is added to the underlying table/view and this view is not updated. Is there a way around this? Cheers, Jack
The only way that I know is to push the calculation down into the original view. This is probably something that you don't want to / can't do. Either do that or as you say, list each and every column from the view. Anyone else any ideas?
Firstly there is a very strong argument against using `SELECT *` in a view for many reasons such as query optimisation, application crashing, network overload etc etc. as discussed in the answers to this question [
http://ask.sqlservercentral.com/questions/18228/sql-server][1] - a view is essentially no different from any other query (unless its an indexed view). For your requirements; no, you wont be able to get around the fact that if you have a view where you name a column BBB and then continue using `SELECT *` that if the table gets a column added that is called BBB then your view will give erroneous/misleading results. I guess this is the best argument for not using `SELECT *` in your case. . . [1]:
http://ask.sqlservercentral.com/questions/18228/sql-server
