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
SCHEMABINDING
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.
