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.

When you create views, you can specify two options: CHECK OPTION and SCHEMABINDING. SCHEMABINDING prevents modifying underlying tables referenced in the view. CHECK OPTION makes sure that when you modify the view, you can only modify those values that are present in it. For example when a WHERE clause exist in your view and you try to modify data outside the scope of it, an exception will be raised.

The last topic here is partitioned views. Partitioning is the method of dividing the data in a large table into smaller tables. Then you can define your view by using the partitioned tables, and joining them with UNION ALLs. Now that’s another topic, but the partitioning is based on CHECK constraints in the partitioned table, but we’ll see that later.

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: