November 28, 2014 at 7:16 am
Hi,
I have a view like:
select * from column1, column2, column3
view name - teste1
I have another view named test2 with the folowing code:
select * from teste1
I have added a new column to teste1, so, now I have the folowing view:
select * from column1, column2, column3, column4
When I make a select to teste2 I still get only the three columns.
Only when I made:
alter view teste2
select * from teste1
It showed the four columns
Is this normal?
November 28, 2014 at 7:21 am
Yes, normal and documented. You need to run sp_refreshview on both. Or stop using SELECT *
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2014 at 8:23 am
As Gail said, normal behaviour.
Even worse is if you drop a column being used in a view or modify it's data type!
This is one of the main benefits of using something like RedGate toolbelt for migrating code though the dev/test/prod evironments. It automatically picks up the dependency of the view and adds the refresh to the migration script AFTER the underlying table has been updated. Can't tell you how many times this has saved me 😀
November 28, 2014 at 8:37 am
Hi Gail,
What do you mean by stop using select?
What other possibility to I have available to select from a pre existing view?
tks
November 28, 2014 at 8:41 am
Gail said don't use SELECT *
the * tells the parser to get all columns, but to do this it has to go to the table schema and work out what the columns are which requires a round trip and has an overhead.
In a production solution, you should specify the columns that you want returned, this way your views are less llikely to break when you change the table structures.
This is especially important if you are using .NET or some other interface that expects a certain column to be in position X in the results set. If the columns move as a result of you changing tables then you could get all sorts of wierd behaviour in your application.
November 30, 2014 at 7:26 am
river1 (11/28/2014)
What do you mean by stop using select?
I didn't say stop using select. I said stop using SELECT *. Explicitly define what columns you need and you won't have problems of views changing (or not changing) unexpectedly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2014 at 2:24 pm
GilaMonster (11/30/2014)
river1 (11/28/2014)
What do you mean by stop using select?I didn't say stop using select. I said stop using SELECT *. Explicitly define what columns you need and you won't have problems of views changing (or not changing) unexpectedly.
Then you have the problem of identifying which view(s) need to include all columns from a table when its columns get modified. That is, there really can be a trade-off here: it's not necessarily quite as simple as a blanket rule of "never use *" implies.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 1, 2014 at 3:01 pm
ScottPletcher (12/1/2014)
GilaMonster (11/30/2014)
river1 (11/28/2014)
What do you mean by stop using select?I didn't say stop using select. I said stop using SELECT *. Explicitly define what columns you need and you won't have problems of views changing (or not changing) unexpectedly.
Then you have the problem of identifying which view(s) need to include all columns from a table when its columns get modified. That is, there really can be a trade-off here: it's not necessarily quite as simple as a blanket rule of "never use *" implies.
Any necessary view changes should be included in the original scoping and design of the change and therefore be included as part of the roll out so it should be much easier to manage than "hey let's hope nothing breaks when we add/change a column because we've been using SELECT * everywhere".
December 1, 2014 at 3:22 pm
ZZartin (12/1/2014)
ScottPletcher (12/1/2014)
GilaMonster (11/30/2014)
river1 (11/28/2014)
What do you mean by stop using select?I didn't say stop using select. I said stop using SELECT *. Explicitly define what columns you need and you won't have problems of views changing (or not changing) unexpectedly.
Then you have the problem of identifying which view(s) need to include all columns from a table when its columns get modified. That is, there really can be a trade-off here: it's not necessarily quite as simple as a blanket rule of "never use *" implies.
Any necessary view changes should be included in the original scoping and design of the change and therefore be included as part of the roll out so it should be much easier to manage than "hey let's hope nothing breaks when we add/change a column because we've been using SELECT * everywhere".
Yes, we all know the general rules and how things "should (optimally) work". But, in a practical sense, how then do you know specifically which view(s) need columns added when the underlying table changes? That is, specifically which views must contain all data columns for the app using them to work correctly (such as perhaps utility apps).
I'm certainly not recommending use of *, but * has a special meaning that a list of columns doesn't. Thus, unless you have the infrastructure in place to accurate identity the views that need "all" columns, and are able to distinguish those specific views even after you're removed the non-best-practice * from the views, you might be better off leaving in "*" and just using sp_refreshview on every such view.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 1, 2014 at 6:33 pm
You could schema bind a view to a table , so that the columns the view depends on are not altered until the binding is removed. This would have no impact if a column is added to the base table , but it will keep a error from happening when removing a column the view needs. Not sure if this works with a select * however. I dont know of a way to easily identify which views have bindings.
----------------------------------------------------
December 1, 2014 at 7:56 pm
My recommendation is to use SQL Server Data Tool. Its compile feature will detect any problems caused by changing one db object.
December 2, 2014 at 8:26 am
GilaMonster (11/28/2014)
Or stop using SELECT *
+ 1000
Don Simpson
December 16, 2014 at 4:05 pm
MMartin1 (12/1/2014)
You could schema bind a view to a table , so that the columns the view depends on are not altered until the binding is removed. This would have no impact if a column is added to the base table , but it will keep a error from happening when removing a column the view needs. Not sure if this works with a select * however. I dont know of a way to easily identify which views have bindings.
Just to add a note to my previous post here .. there is a way to find schema binded views ...
SELECT [name] from sys.views
WhereOBJECTPROPERTY([object_id],'IsSchemaBound') = 1
Straight from Books Online 😉
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply