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, by default, do not exist physically like tables; views are also called virtual tables

1 Creating a View



it encrypts the SELECT statement


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.1 ex

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


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%'; 


rows are actually inserted into the underlying base table


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


 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

 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


 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 )

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