Microsoft® SQL Server® 2012 Bible

287

Chapter 11: Projecting Data Through Views

11

Creating a view with schema binding locks the underlying tables to the view and prevents

changes, as demonstrated in the following code sample:

Use AdventureWorks2012

go

CREATE TABLE dbo.Test (

[Name] NVARCHAR(50)

);

go

CREATE VIEW dbo.vTest

WITH SCHEMABINDING

AS

SELECT [Name] FROM dbo.Test;

go

use AdventureWorks2012

go

ALTER TABLE Test

ALTER COLUMN [Name] NVARCHAR(100);

Result:

Msg 5074, Level 16, State 1, Line 1

The object 'vTest' is dependent on column 'Name'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN Name failed because one

or more objects access this column.

Some restrictions apply to the creation of schema-bound views. The SELECT statement must

include the schema name for any referenced objects, and SELECT * (all columns) is not per-

mitted. (But that last requirement shouldn’t bother anyone who follows best practices.)

Within Management Studio’s View Designer, the WITH SCHEMA BINDING option can be

enabled within the View Properties page.

When the schema underlying a view that is not schema bound does change, it likely breaks

the view. If this happens, to repair the view, either re-create it or run the sp_

refreshview system stored procedure.

Encrypting the View’s SELECT Statement

The WITH ENCRYPTION option is another security feature. When views or stored proce-

dures are created, the text can be retrieved through the sys.sql_modules and sys

.syscomments system views. The code is therefore available for viewing. The view may

contain a WHERE condition that should be kept confi dential, or there may be some other

reason for encrypting the code. The WITH ENCRYPTION option encrypts the code in the

system tables, hides the code from sys.sql_modules and sys.syscomments, and pre-

vents anyone from viewing the original code.

c11.indd 287c11.indd 287 7/30/2012 4:41:12 PM7/30/2012 4:41:12 PM

http://www.it-ebooks.info