3

I always ask myself, why if a have two Tables on SQL Server and i create a view like this:

CREATE VIEW vw_MyView 
AS
SELECT T1.*, T2.ClientName FROM Table1 T1 inner join Table2 T2 on T1.ID_Client=T2.ID_Client

When i need to add a field to table 1, the view show Values missplaced? Values from column one are show on colunm two and so on!

This happends on every version of SQL Server i tested. Can anyone tell me why this is happening and how to fix it?

This behavior gives big problem, specially with derivated views. Thanks

ericpap
  • 2,917
  • 5
  • 33
  • 52
  • possible duplicate of [Why is using '\*' to build a view bad?](http://stackoverflow.com/questions/262450/why-is-using-to-build-a-view-bad) – AHiggins May 06 '15 at 15:52
  • Thanks, but that didn't answer my question. Why SQL Scramble my view data? – ericpap May 06 '15 at 15:54
  • 1
    Alternatively, you are seeing the same issue as [this guy](http://stackoverflow.com/questions/1013711/database-view-does-not-reflect-the-data-in-the-underying-table): the column position apparently matters when a view is created, and the columns may retain the original order even if the data does not. Does the suggested `sp_refreshview` help you get the views working? – AHiggins May 06 '15 at 16:02
  • 1
    A view does not dynamically go and look at the columns to return. When you create a view with select * it actually converts that to the current columns in the view definition. You shouldn't be using select * (other than for exists) anyway, especially in a view. If you type out the columns in your view definition this is a non-issue. – Sean Lange May 06 '15 at 16:12
  • @SeanLange Thanks, but again that is not my question. If the view is attached to the original table schema, why then if I add a field the data is missplaced? shouldn't stay with the original schema and not even considered the field i add (on the new schema)? – ericpap May 06 '15 at 18:28
  • 1
    That is what happens when you use * in a view. It is well documented. When you change the order the columns your view will get all screwy because the view definition was not built with column names. – Sean Lange May 06 '15 at 18:34

2 Answers2

9

After updating schema, you should refresh the view metadata for any and all views dependent on the schema:

EXEC sp_refreshview @viewName

Where the @viewName variable holds the name of the view. You can use this stored procedure in a script that can grab the views dependent on the table(s) in order to refresh them all dynamically, or you can just spin through all views (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS) and call the stored procedure on every view.

This has the added benefit of finding any views that are rendered invalid by schema changes and flagging them, as they'll error out when the SP is called.

pmbAustin
  • 3,890
  • 1
  • 22
  • 33
  • Thanks. But if i define the view with the * to get alll fields and run sp_refreshview, will the the field be present on the view? How could i know wich views are linked to a specific table, directly or indirectly? – ericpap May 06 '15 at 19:53
  • Yes, the refresh view will fix things up. That's why it exists. (another scenario is if you changed the length of a varchar or nvarchar field, renamed a column, etc). That's why this stored procedure exists. Of course, you could also just drop and re-create the view but that's kind of using a big hammer. – pmbAustin May 07 '15 at 17:19
  • As for finding the dependencies, just go to SQL Server Management Studio, expand the "views" node under your database, right-click your view's name, and select "View Dependencies". Then click the "Objects on which [view] depends" radio button. It'll list the tables and views right there. Any change to those will warrant an sp_refreshview call. – pmbAustin May 07 '15 at 17:21
1

The accepted answer is OK (I voted up too) for fixing that issue after changing the tables. For my environments I prefer to use WITH SCHEMABINDING (see https://msdn.microsoft.com/en-us/library/ms187956.aspx) for creation of the views. That leads to not being able to use the * in the select of the view as has been discussed here: Why is using '*' to build a view bad? and of course to not being able to change the columns of the table without changing (in most cases: dropping, recreating) the view

That is of course more cumbersome when changing tables, but that way you dont get runtime errors when forgetting to call EXEC sp_refreshview after a table change (for example column drop) because you are forced by the database to adapt the view as well.

Community
  • 1
  • 1
rominator007
  • 1,553
  • 1
  • 11
  • 22