Tag Archive | View

Design views

May include but is not limited to: common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING

A view is a piece of metadata (a simple SELECT query) stored by the database server. It can be used like a table, thus you can query it, update the underlying tables, delete from them through the view, etc. What is the point of a view (you really shouldn’t ask this question preparing to be MCTIP)? We all have common select queries we run every day with joins, functions, etc. involved, and it takes a lot of time to enter the exact same query every time. Create a view for it, and you can even control the access for it with great granularity.

With a view, you can restrict access to even specified columns of a table, or just given rows (with a WHERE clause). Now with the advent of Common Table Expressions (CTEs) – views got a strong opponent. But you can even use a CTE in a view, making the best of both worlds (the CTE can recursively call itself). There’s a catch, however – views can’t get parameters.

Read More…

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.