Views in SQL Server 2000

Wondered what would happen if we altered one of the underlying tables so a column that was referenced in the view no more existed? If we do not specify SCHEMABINDING, we could delete a column, and our view would fail when we later used it.

USE Northwind
GO
SELECT * INTO Employees2 FROM Employees
GO
CREATE VIEW vwEmployeeAge ([Name], Age)
AS
        SELECT TitleOfCourtesy + ' ' + FirstName + ' ' + ' ' + LastName, DATEDIFF(yy, BirthDate, GETDATE())
        FROM Employees2
GO
ALTER TABLE Employees2 DROP COLUMN FirstName
SELECT * FROM vwEmployeeAge

Output:
Server: Msg 207, Level 16, State 3, Procedure vwEmployeeAge, Line 3
Invalid column name 'FirstName'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'vwEmployeeAge' because of binding errors.

I think I have to explain some of the code first (note that we did not specify SCHEMABINDING in this example). On line three, we create a new table called Employees2, and inserts all data into it (so we do not have to modify the original table in the Northwind database). Then we create the view with the name vwEmployeeAge. We also specify the name of the columns, which we have never done before. This is valid, and in fact, we must do it here, since otherwise the columns would have no name. We could however used AS to specify the name in the SELECT query, but to be honest, I like this other way better.

Anyway, after we have created the view, we drop the column FirstName from Employees2, and then we try to use the view (I know, I haven’t showed you how to use a view yet, be patient, it will be in the next section). As you can see, it doesn’t work very well to use this view.

So, to protect ourselves from this in the future, we can use SCHEMABINDING. First of all, drop the table Employees2, and re-create it.

USE Northwind
GO
DROP TABLE Employees2
GO
SELECT * INTO Employees2 FROM Employees
GO

USE Northwind
GO
CREATE VIEW vwEmployeeAgeSchemabinding ([Name], Age)
WITH SCHEMABINDING
AS
        SELECT TitleOfCourtesy + ' ' + FirstName + ' ' + ' ' + LastName, DATEDIFF(yy, BirthDate, GETDATE())
        FROM Employees2
GO

Output:
Server: Msg 4512, Level 16, State 3, Procedure vwEmployeeAgeSchemabinding, Line 4
Cannot schema bind view 'vwEmployeeAgeSchemabinding' because name 'Employees2' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Ooops, didn’t work did it? When using SCHEMABINDING, the select statement must include the two-part names (owner.object) of tables, views or user-defined functions referenced. So, let’s try this instead:

USE Northwind
GO
CREATE VIEW vwEmployeeAgeSchemabinding ([Name], Age)
WITH SCHEMABINDING
AS
        SELECT TitleOfCourtesy + ' ' + FirstName + ' ' + ' ' + LastName, DATEDIFF(yy, BirthDate, GETDATE())
        FROM dbo.Employees2
GO

ALTER TABLE Employees2 DROP COLUMN FirstName

Output:
Server: Msg 5074, Level 16, State 3, Line 1
The object 'vwEmployeeAgeSchemabinding' is dependent on column 'FirstName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN FirstName failed because one or more objects access this column.

With View_Metadata

If WITH VIEW_METADATA is specified, SQL Server will return the metadata information about the view, instead of the underlying tables to the DBLIB, ODBC and OLE DB APIs, when metadata is being requested. Metadata is information about the view’s properties, for example column names or type. If WITH VIEW_METADATA is used, the client application is able to create an updateable client side cursor, based on the view.

With Check Option

This is probably the most useful of all options. It guarantees that data modification through the view complies with the WHERE condition. This means that if you insert or update data, it is not possible that it “disappears” when we query the view afterwards. I think this is explained best with an example.

USE Northwind
GO
CREATE VIEW vwCustomersInSweden
AS
        SELECT *
        FROM Customers 
        WHERE Country = 'Sweden'
GO
SELECT * FROM vwCustomersInSweden

Now, let’s insert data using this view, and then try to find what we just inserted.

INSERT INTO vwCustomersInSweden 
VALUES 
(
        'SHOPS', 
        'Shop Shop Shop', 
        'John Doe',
        'Owner', 
        '134 Polk St. Suite 5', 
        'San Francisco', 
        'CA', 
        '94117', 
        'USA', 
        '(415) 555-1234', 
        NULL
)
SELECT * FROM vwCustomersInSweden

Yes, it’s true, it’s not there. When we created this view, we probably not only wanted to limit the user to only see the Swedish customers. We probably also wanted to deny the user to insert data for other countries but Sweden. To accomplish this, we have to use WITH CHECK OPTION.

USE Northwind
GO
CREATE VIEW vwCustomersInSwedenCheck
AS
        SELECT *
        FROM Customers 
        WHERE Country = 'Sweden'
WITH CHECK OPTION
GO

If we try to insert the same data again (we first delete it), we will get an error.

DELETE FROM Customers WHERE CustomerID = 'SHOPS'
INSERT INTO vwCustomersInSwedenCheck
VALUES 
(
        'SHOPS', 
        'Shop Shop Shop', 
        'John Doe',
        'Owner', 
        '134 Polk St. Suite 5', 
        'San Francisco', 
        'CA', 
        '94117', 
        'USA', 
        '(415) 555-1234', 
        NULL
)

Output: Server: Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Views in SQL Server 2000, Part 3

With Schemabinding

With View_Metadata

With Check Option