You are a database developer on an instance of SQL Server 2008. Your database contains the Employee table defined using the following Transact-SQL statement:
CREATE TABLE dbo.Employee(
EmpID int PRIMARY KEY,
LastName varchar(35) NOT NULL,
FirstName varchar(35) NOT NULL,
HireDate datetime DEFAULT (GETDATE()),
Status varchar(8) CHECK (Status IN (‘Active’, ‘Inactive’)),
SSN char(9) NOT NULL,
Salary money NOT NULL CHECK (Salary > 0),
Commission money DEFAULT 0);
You want to create a view that meets the following requirements:
• Users must not be able to query data for inactive employees.
• Users must not be able to query the SSN, Salary, or Commission column for any employees.
In addition, developers must not be able to modify the structure of the Employee table such that the view becomes unusable.
Which statement should you use to create the view?
A. CREATE VIEW EmpView
WITH SCHEMABINDING AS
SELECT *
FROM dbo.Employee WHERE Status <> ‘Inactive’;
B. CREATE VIEW EmpView
AS
SELECT EmpID, LastName, FirstName, HireDate, Status
FROM dbo.Employee WHERE Status <> ‘Inactive’;
C. CREATE VIEW EmpView
WITH SCHEMABINDING AS
SELECT EmpID, LastName, FirstName, HireDate, Status
FROM dbo.Employee;
D. CREATE VIEW EmpView
WITH SCHEMABINDING AS
SELECT EmpID, LastName, FirstName, HireDate, Status
FROM dbo.Employee WHERE Status <> ‘Inactive’;
Correct Answer: D
Explanation/Reference:
A view is a logical table based on a SELECT statement that references one or more underlying tables or views. When you create a view, the SELECT statement defining the view is stored in the database, rather than the actual data. When a user references the view in a query, the values are retrieved from the underlying tables or views using the view’s SELECT statement. This ensures that the data that is visible to users through the view is always the most recent data.
Views are often used to restrict access to specific columns and rows of data in a table. When defining a view, you can prevent users from accessing a column by omitting the column from the SELECT list. You can prevent users from accessing specific rows by including a WHERE clause that filters the rows that are visible through the view. For example, in this scenario, users cannot query the SSN, Salary, or Commission columns because these columns are not included in the SELECT list. In addition, users cannot query inactive employees because the WHERE clause filters out employees that are inactive.
In this scenario, you also wanted to prevent developers from making changes to the table that might make the view unusable. You should include the WITH SCHEMABINDING clause to accomplish this. 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 such a way that the view becomes 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.
To use schema binding for views, you must include a SELECT column list and all objects must be referenced using their two-part name, which includes the schema name and object name.
You should not use the statement that includes the WITH SCHEMABINDING clause but uses an asterisk (*) in the SELECT list because when schema binding a view, the SELECT list must contain a list of columns. This statement will generate the following error:
Msg 1054, Level 15, State 6, Procedure EmpView, Line 5Syntax ‘*’ is not allowed in schema-bound objects.
You should not use the statement that omits the WITH SCHEMABINDING clause because this will not meet the requirement of preventing developers from making database changes that might affect the view’s definition.
You should not use the statement that omits the WHERE clause because this will not meet the requirement of only allowing users to query active employees.