When it comes to non-schema-bound-views (NSB View) it is important to remember that there is no schema bound between objects used in the view and metadata for a view.
Let’s look at the following scenario:
-- Create Table_1 CREATE TABLE dbo.Table_1 (Col_A int NULL, Col_B numeric(18, 0) NULL, Col_C nvarchar(50) NULL) ON [PRIMARY] GO; -- -- Add data INSERT dbo.Table_1 (Col_A, Col_B, Col_C) VALUES (10, 10.5, 'ABC'); INSERT dbo.Table_1 (Col_A, Col_B, Col_C) VALUES (20, 0.5, 'DEFG'); -- -- Create View CREATE view [dbo].[vw_MyView] AS SELECT TBL.*, (TBL.Col_A * TBL.Col_B) AS ColMult FROM dbo.Table_1 TBL GO;
Attempt to use the view would give the following result:
Col_A, Col_B, Col_C, ColMult ================================= 10, 10.5, ABC, 105 20, 0.5, DEFG, 10
So far we are OK. But “fun” starts when a new column is added to the table
Case #1 – bad:
ALTER TABLE dbo.Table_1 ADD Col_D nvarchar(10) NOT NULL DEFAULT 'A'
Result of the view:
Col_A, Col_B, Col_C, ColMult ================================= 10, 10.5, ABC, A 20, 0.5, DEFG, A
Well… OK, it is not as bad as it seems since we would find the problem almost immediately in application since types of expected and retured data are incompatible. Bad situation, but manageable.
Case #2 – catastrophic:
ALTER TABLE dbo.Table_1 ADD Col_D int NOT NULL DEFAULT 0
Let’s look at the result now:
Col_A, Col_B, Col_C, ColMult ================================= 10, 10.5, ABC, 0 20, 0.5, DEFG, 0
ColMult now contains 0 (zero)!!! If we have significant logic which relays on that, then big troubles are coming our way.
Now this is a catastrophic situation. And not easily visible. And off course work just fine on Developer’s machine…
Testing… Testing… Testing…
What just happened here?!!!
This is when knowing non-schema-bound-views definition is important. And let’s remember – this is default state of any new view created.
First, what a difference between schema bound views and not bound.
In order to create schema bound view special view attribute need to be specified. Let’s adjust original view definition as following:
-- Alter View ALTER VIEW [dbo].[vw_MyView] WITH SCHEMABINDING AS SELECT TBL.*, (TBL.Col_A*TBL.Col_B) AS ColMult FROM Table_1 TBL
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. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
If we try executing script above to add the column error would occur: “Syntax ‘*’ is not allowed in schema-bound objects.”
As you can see from definition of the attribute, view need to be defined explicitly with all columns listed. So if we were to have columns specified explicitly, then no problem would occur in the first place and is considered a good practice.
In our scenario above this is not an intent. Our view is designed to adjust itself to a table structure changes.
Inconvenience… or advantage… I let you decide.
Finding a cure
Is there a way to fix the problem?
Dirty way of forcing the view metadata to be refreshed would be simply recreate the view using original script, but imagine you have 30+ of them – Not good, time consuming and error-prone.
There is a simple, universal and “human-error protected” way – after changes are applied to tables, simply refresh metadata of every view defined:
DECLARE @Table_Name varchar(120) DECLARE Refresh_Views CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = 'dbo' OPEN Refresh_Views FETCH NEXT FROM Refresh_Views INTO @Table_Name WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_refreshview @Table_Name print 'refreshed View for: ' + @Table_Name FETCH NEXT FROM Refresh_Views INTO @Table_Name END CLOSE Refresh_Views DEALLOCATE Refresh_Views
In the script sp_refreshview would tell SQL Server to update the metadata for the all non-schema-bound views in current database.
Note: Alternative: We can also use more generic command – sp_refreshsqlmodule – which “updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects.” This may be even better since it would allow to address any possible schema issues with any objects and not just views.