Create and alter views

This objective may include but is not limited to: WITH ENCRYPTION, WITH SCHEMABINDING, WITH CHECK OPTION, manage permissions (GRANT, DENY, REVOKE)

A view is a select command stored in your database. Further simplifying, it’s a pointer to a select command. Nothing else but the command is stored, and executed when you query the view.

What is the point of it? You might want to show only a set of columns instead of all of them, or you have a complex select command on a few tables, and you would like to call it easily.

To create a view, just use the following syntax:

Select statement

To alter it, use the ALTER VIEW statement. There are a few options you can specify, preceded by a WITH clause: SCHEMABINDING, ENCRYPTION and CHECK OPTION.

  • WITH SCHEMABINDING: there are times when you want to change a column name in a table. This, however, would break your view, because the SELECT command is evaluated only in its creation time, when you create the view. To overcome this, create your views with the WITH SCHEMABINDING clause. This won’t let you change your column name, when a view references on it.
  • WITH ENCRYPTION: encryption simply obfuscates the SELECT statement. It’s not really an encryption method, and can be easily reverse-engineered, so it’s generally discouraged.
  • WITH CHECK OPTION: when you update data through the view, WITH CHECK OPTION validates the data regarding the columns in the SELECT statement. This makes sure that the modified data is visible in the view. Note that if you update data in the underlying table directly, CHECK OPTION will do you no good.

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: