You may be using SQL Server views to enforce column-level security against base tables, or to reduce programming complexity. What you may have seen is that if you make changes to the base table(s), this can adversely affect the usability of the view. Let's look at a couple of examples:
Dropping a column Obviously, dropping a column on which the view depends can cause problems... whether or not the view mentions the column explicitly. If you run the following code:
CREATE TABLE dbo.splunge
(
splungeID INT,
blat VARCHAR(32)
)
GO
CREATE VIEW dbo.vSplunge
AS
SELECT splungeID, blat
FROM dbo.splunge
GO
EXEC sp_rename 'splunge.blat', 'foo', 'COLUMN'
GO
SELECT * FROM dbo.vSplunge
GO
DROP VIEW dbo.vSplunge
DROP TABLE dbo.splunge
GO |
Results:
Server: Msg 207, Level 16, State 3, Procedure vSplunge, Line 4
Invalid column name 'blat'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'dbo.vSplunge' because of binding errors. |
If you use SELECT * in your view, you will see a slightly different error:
CREATE TABLE dbo.splunge
(
splungeID INT,
blat VARCHAR(32)
)
GO
CREATE VIEW dbo.vSplunge
AS
SELECT *
FROM dbo.splunge
GO
ALTER TABLE dbo.splunge DROP COLUMN blat
GO
SELECT * FROM dbo.vSplunge
GO
DROP VIEW dbo.vSplunge
DROP TABLE dbo.splunge
GO |
Results:
Server: Msg 4502, Level 16, State 1, Procedure vSplunge, Line 4
View or function 'dbo.vSplunge' has more column names specified than columns defined.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'dbo.vSplunge' because of binding errors. |
This is because the view relies partly on the definition of the table as it existed at the time the view was created.
Renaming a column CREATE TABLE dbo.splunge
(
SplungeID INT,
blat VARCHAR(32)
)
GO
CREATE VIEW dbo.vSplunge
AS
SELECT SplungeID, blat
FROM dbo.splunge
GO
EXEC sp_rename 'splunge.blat', 'foo', 'COLUMN'
GO
SELECT * FROM dbo.vSplunge
GO
DROP VIEW dbo.vSplunge
DROP TABLE dbo.splunge
GO |
Results:
Server: Msg 207, Level 16, State 3, Procedure vSplunge, Line 5
Invalid column name 'blat'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'dbo.vSplunge' because of binding errors. |
Re-creating the table with columns in a different order This is only a problem if you use SELECT * in your views (which you shouldn't do), but here is a repro:
CREATE TABLE dbo.splunge
(
SplungeID INT,
blat VARCHAR(32)
)
GO
CREATE VIEW dbo.vSplunge
AS
SELECT *
FROM dbo.splunge
GO
DROP TABLE dbo.splunge
GO
CREATE TABLE dbo.splunge
(
blat VARCHAR(32),
SplungeID INT
)
GO
INSERT splunge SELECT 'foo', 1
GO
SELECT * FROM dbo.vSplunge
GO
DROP VIEW dbo.vSplunge
DROP TABLE dbo.splunge
GO |
Results:
SplungeID blat
--------- ----
foo1 |
How is this possible? SplungeID is an INT, and blat is a VARCHAR, yet it seems that the view has swapped their values. This is because the view relies on the ordinal position of the columns in the table's current form, but still lists the column names in the order of the original creation. You can avoid this problem by never using SELECT * in your views; in the meantime, you can make the symptom go away by running the following command:
EXEC sp_refreshview 'dbo.vSplunge'
GO |
This will tell the server about the new table definition and straighten out the ordinal position and data for each column. You could accomplish the same thing by altering the view, or dropping and re-creating.
So, how do you avoid these problems? Your first instinct might be to run 3rd party software to monitor all DDL events on the server, and then updating views manually to reflect the changes to the base tables. But there are better ways than merely being reactionary.
- First, never use SELECT * in a view definition. ALWAYS name your columns explicitly. This will take care of both confusion and potential data consistency issues.
- More importantly, you can use the WITH SCHEMABINDING attribute for views. See how the above mistakes are avoided:
CREATE TABLE dbo.splunge
(
splungeID INT,
blat VARCHAR(32)
)
GO
CREATE VIEW dbo.vSplunge
WITH SCHEMABINDING
AS
SELECT splungeID, blat
FROM dbo.splunge
GO |
Notice that the only change is the WITH SCHEMABINDING option. Now, when I try to drop a table:
DROP TABLE dbo.splunge
GO |
Results:
Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'dbo.splunge' because it is being referenced by object 'vSplunge'. |
When I try to drop a column:
ALTER TABLE dbo.splunge DROP COLUMN blat
GO |
Results:
Server: Msg 5074, Level 16, State 3, Line 1
The object 'vSplunge' is dependent on column 'blat'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN blat failed because one or more objects access this column. |
And when I try to rename the column:
EXEC sp_rename 'splunge.blat', 'foo', 'COLUMN'
GO |
Results:
Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 368
Object 'splunge.blat' cannot be renamed because the object participates in enforced dependencies. |
So obviously, the WITH SCHEMABINDING option helps keep your views immune from problematic base table changes.
[ Comment, Edit or Article Submission ]
