2

If I have a table MyTable like this:

Value1 decimal

Then I have a view MyView:

Select SUM(Value1) as SumValue1 from MyTable

When I look at the view's column data type in SSMS under the view's columns section it is a decimal.

Now if I modify the table to: 

Value1 real

If I refresh the view's column section in SSMS the data type is still a decimal. Now if I open the view and resave it, the datatype becomes a float.

Is this how it is supposed to be? It seems that the view doesn't change until I resave it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dtc
  • 10,136
  • 16
  • 78
  • 104
  • 3
    If you make changes to underlying tables, you need to refresh your view(s): `exec sp_refreshview 'MyView'` to bring their definition up to date again. That is indeed expected behavior. See [MSDN docs on sp_refreshview](http://msdn.microsoft.com/en-us/library/ms187821.aspx). – marc_s Nov 03 '12 at 07:26

1 Answers1

8

Yes, this is expected. Tables can be changed in ways that make the views completely invalid. If you want to prevent this from happening, you can use the WITH SCHEMABINDING option when creating the view. From the linked article:

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

Community
  • 1
  • 1
explunit
  • 18,967
  • 6
  • 69
  • 94