SQL Views: A Mind Map

In this post I did a mind-map on view; A view can be thought of as either a virtual table or a stored query, check the first post here.

 

Views

views, by default, do not exist physically like tables; views are also called virtual tables

1 Creating a View

REATE VIEW view_name [(column_list)] [WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}] AS select_statement [WITH CHECK OPTION]

1.1 WITH ENCRYPTION

it encrypts the SELECT statement

1.2 SCHEMABINDING

it binds the view to the schema of the underlying table N> It you need to change the underlying table, you have to drop the view or change it so that it no longer has the SCHEMABINDING clause

1.3 ex

 USE sample; GO CREATE VIEW v_clerk AS SELECT emp_no, project_no, enter_date FROM works_on WHERE job = 'Clerk'; 
 -- explicit column names in the ire definition (because of aggregates) USE sample; GO CREATE VIEW v_count(project_no, count_project) AS SELECT project_no, COUNT(*) FROM works_on GROUP BY project_no; 

2 Advantages

To restrict the use of particular columns and/or rows of tables. To hide the details of complicated queries To restrict inserted and updated values to certain ranges

3 ALTER VIEW
3.1 ex

 USE sample; GO ALTER VIEW v_without_budget AS SELECT project_no, project_name FROM project WHERE project_no >= 'p3'; 

4 DROP VIEW

N> A view is not automatically dropped if the underlying table is removed.

4.1 ex

 USE sample; GO DROP VIEW v_count; 

5 catalog view
5.1 sys.objects

N> All rows of this view that have the value V for the type column are views.

5.2 sys.views
5.3 sp_helptext

to get query belonging to a particular view

6 DML Statements

Views are retrieved and modified with the same Transact-SQL statements

6.1 Retrieval

a view is used exactly like any base table

6.1.1 ex

 USE sample; GO CREATE VIEW v_d2 ... ; GO SELECT emp_lname FROM v_d2 WHERE emp_lname LIKE 'Mario%'; 

6.2 INSERT/UPDATE/DEL

rows are actually inserted into the underlying base table

6.2.1 WITH CHECK OPTION

it used ests every inserted row to ensure that the conditions in the WHERE clause are evaluated

ex

 USE sample; GO CREATE VIEW v_2006_check AS SELECT emp_no, project_no, enter_date FROM works_on WHERE enter_date BETWEEN '01.01.2006' AND '12.31.2006' WITH CHECK OPTION; GO INSERT INTO v_2006_check VALUES (22334, 'p2', '1.15.2007'); 

6.2.2 Restrictions

The insertion fails when: – The FROM clause in the view definition involves two or more tables and the column list includes columns from more than one table – A column of the view is derived from an aggregate function – The SELECT statement in the view contains the GROUP BY clause or the DISTINCT option – A column of the view is derived from a constant or an expression // mainly because it does not make sense to insert rows because he aggregate that does aggregation on rows…

6.2.3 ex

 USE sample; GO CREATE VIEW v_100000 AS SELECT project_no, budget FROM project WHERE budget > 100000 WITH CHECK OPTION; GO UPDATE v_100000 SET budget = 93000 WHERE project_no = 'p3'; 
 USE sample; GO CREATE VIEW v_budget (budget_reduction) AS SELECT budget*0.9 FROM project; GO DELETE FROM v_budget; 

7 Indexed Views

If a view is frequently used in queries, you can increase performance by creating a clustered index on the view N> Defining a clustered index means that the system materializes the dynamic data (of the view) into the leaf pages on an index

7.1 Creating

process: 1. Create the view using the CREATE VIEW statement with the WITH SCHEMABINDING 2. Create the corresponding clustered index N> after 2 you can create any number of nonclustered indices on the view N> You need ON for QUOTED_IDENTIFIER http://msdn.microsoft.com/en-us/library/ms174393.aspx ANSI_NULLS http://msdn.microsoft.com/en-us/library/ms188048.aspx CONCAT_NULL_YIELDS_NULL http://msdn.microsoft.com/en-us/library/ms176056.aspx ANSI_PADDING http://msdn.microsoft.com/en-us/library/ms187403.aspx ANSI_WARNINGS http://msdn.microsoft.com/en-us/library/ms190368.aspx and OFF for NUMERIC_ROUNDABORT http://msdn.microsoft.com/en-us/library/ms188791.aspx

7.1.1 ex

 -- 1 CREATE VIEW v_enter_month WITH SCHEMABINDING AS SELECT emp_no, DATEPART(MONTH, enter_date) AS enter_month FROM dbo.works_on; -- 2 CREATE UNIQUE CLUSTERED INDEX c_workson_deptno ON v_enter_month (enter_month, emp_no); 

7.1.2 IsIndexable
ex

 SELECT objectproperty(object_id('v_enter_month'), 'IsIndexable'); 

7.1.3 sessionproperty

use the sessionproperty property function to test whether one of the options of the SET statement is activated

ex

 SELECT sessionproperty ('QUOTED_IDENTIFIER'); 

7.2 sp_spaceused

it allows you to check whether the view is materialized

 

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s