You are a database developer on an instance of SQL Server 2008. Your Prod database contains a Meeting table defined using the following statement:
CREATE TABLE Meeting (
ID int IDENTITY(1,1) PRIMARY KEY,
Description varchar(30) NOT NULL,
MaxAttendance smallint DEFAULT 0,
Type bit NULL,
Priority tinyint CHECK (Priority BETWEEN 0 and 10),
Cost money NULL);
Assuming appropriate permissions, which statement will successfully create a view?
A. CREATE VIEW RegularMeeting
WITH SCHEMABINDING AS SELECT * FROM Meeting WHERE Priority = 5 WITH CHECK OPTION;
B. CREATE VIEW SpecialMeeting
AS SELECT * FROM Meeting WHERE Priority = 1 WITH CHECK OPTION;
C. CREATE VIEW WeeklyMeeting
WITH SCHEMABINDING, ENCRYPTION AS SELECT ID, Description FROM Meeting WHERE Priority = 7 WITH CHECK OPTION;
D. CREATE VIEW StatusMeeting
WITH SCHEMABINDING AS SELECT m.* FROM dbo.Meeting m WHERE Priority = 4;
Correct Answer: B
Explanation/Reference:
Assuming appropriate permissions, the following statement will successfully create a view:
CREATE VIEW SpecialMeeting AS SELECT * FROM Meeting WHERE Priority = 1 WITH CHECK OPTION;
This statement will create a view named SpecialMeeting, through which all columns in the Meeting table for rows with a Priority value of 1 are visible. The WITH CHECK OPTION clause is used to ensure that no data modifications can be made through a view that would cause the underlying data to violate the view’s definition. Because you included the WITH CHECK OPTION clause, an UPDATE or INSERT statement that updates data using the view cannot modify the data in a way that would cause the data to violate the view’s definition. For example, if you attempted to update a row using the view that sets the Priority value to a value other than 1, an error message similar to the following would be displayed:
Msg 550, Level 16, State 1, Line 2The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans aview that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The CREATE VIEW statements for the RegularMeeting and StatusMeeting views include the WITH SCHEMABINDING clause and an asterisk (*) for the SELECT list. Each of these statements will generate an error similar to the following because you must explicitly list the columns in the SELECT list when using schema binding:
Msg 1054, Level 15, State 6, Procedure SpecialMeeting, Line 5Syntax ‘*’ is not allowed in schema-bound objects.
The WITH SCHEMABINDING clause ensures that base tables of a view cannot be dropped or modified in a way that affects the view’s definition.
This prevents users from dropping or modifying base tables in a way that makes the view unusable. To drop base tables or make such modifications, you would need to first drop the view, alter the view omitting SCHEMABINDING, or alter the view to remove any unwanted dependencies.
The CREATE VIEW statement for the WeeklyMeeting view will generate the following error:
Msg 4512, Level 16, State 3, Procedure WeeklyMeeting, Line 5 Cannot schema bind view ‘WeeklyMeeting’ because name
‘Meeting’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
The statement fails because only the object name was specified in the SELECT statement. When schema binding a view you must specify the two-part object name, including the schema name and object name. The WITH ENCRYPTION clause is used to encrypt the CREATE VIEW statement that is stored in the sys.syscomments table. This ensures that the statement is not stored in plain text that is readable by others.
You should also note that when creating a view, with or without schema binding, the underlyingCHECK constraints on base tables are ignored. For example, in this scenario, the following statement would successfully create a view with schema binding and encryption, but no rows would be visible through the view because of the table’s CHECK constraint:
CREATE VIEW ManagerMeeting WITH SCHEMABINDING, ENCRYPTIONAS SELECT ID, Description FROM dbo.Meeting WHERE Priority = 12
WITH CHECK OPTION;
