SQL 301 – Views

At this point I'm sure you know how to CREATE and ALTER a VIEW.  But today I'd like to go into a little more detail on views.  I'd like to cover updatable views,  and some additional options you can add to your CREATE or ALTER VIEW statements.

Updatable Views

You can write insert s, updates, and deletes against a view, as long as the following statements are true.

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.  If you try to update two different base tables in a single statement, you'll get an error: Msg 4405, Level 16, State 1, Line 2 View or function 'viewName' is not updatable because the modification affects multiple base tables. If you need to be able to update multiple base tables at once, You'll have to create an INSTEAD OF trigger to cover that UPDATE, INSERT, or DELETE statement.  At that point though, you're writing your own rules!
• The column(s) you're trying to update cannot be computed columns.  This means they cannot use AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP, UNION, UNION ALL, CROSS JOIN, EXCEPT, and INTERSECT will all be forbidden, if you want to do any updates without resorting to triggers.
• You aren't using GROUP BY, HAVING, or DISTINCT clauses.
• You aren't using TOP.

If any of the above aren't true, you'll have to use an INSTEAD OF trigger, or directly update the base tables.  Now that you understand updating the view, I'd like to introduce you to some options you can add to your view definition

WITH CHECK OPTION

CREATE VIEW viewName
AS
SELECT sampleCount FROM tableName WHERE sampleCount BETWEEN 1 and 100
WITH CHECK OPTION

This option forces the update (or insert) to check any criteria set through the SELECT statement and make sure the changes you make will still be visible through the VIEW after the change is made.  In the above example, if I ran an update against the view and tried to change the sampleCount to a value outside of 1 top 100, you'd get the following error: 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. This becomes useful when you want to limit users ability to make changes, but you can't completely lock them out of a table.

You could define a view that limits the user to a specific subset of a table.  If they try to change the data so it goes outside of that subset, the WITH CHECK OPTION could prevent that!

I would like to point out now, ny updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.  So, if you didn't lock the user out of the base table, they could go around your protection, and make the change anyway.

WITH ENCRYPTION

Honestly, I'm not sure why this really matters, but the body of your view is stored in sys.syscomments.  If you want to encrypt that, and prevent it from being published for replication... pass WITH ENCRYPTION on your create statement.  Check out the following code.

CREATE VIEW viewName
WITH ENCRYPTION
AS
SELECT columnName FROM tableName

WITH SCHEMABINDING

Finally, I'd like to cover SCHEMABINDING for your views.  This is useful for making changes to your tables impossible without first making changes to your view.  This is useful when you have several programmers working in one database.  I know I've run into problems with a view that was built on a table that got changed under me, and I didn't know about it until a user reported an error.

WITH SCHEMABINDING can prevent that.  If a user tries to make a change to a base table that would affect the VIEW, you first have to drop the VIEW, or alter the view so the change will no longer affect it.  Or you can alter the view to remove the SCHEMABINDING temporarily.

There is a catch...When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced.

That's all for today...you'll need some time to digest this.  If you have any questions, let me know.  I'm here to help!