Creating Views and Virtual Tables in SQL Server 2012

 

 

A view in SQL Server 2012 is a way to dynamically viewing data my query. You can look at a virtual table, where it pulls data from various tables and display the data in one view. Very similar concept as libraries in Windows 7 and dynamic distribution list Exchange. SQL Server 2012 does this by getting data from column and rows across multiple tables and placing them in a single view. Note that it also allows tables across different databases. SQL views can be used in RBAC, role based access control model, where you only want to grant a set of tables for a group of end users.

There are various types of views within SQL Server 2012 and I will go over each of them. Systems view in SQL 2012 allows views of metadata in the catalog. This allows database administrators to view configuration on specific views such as list of user databases in the instance. Partition views in SQL 2012 are views that connect parallel partitioned data from a group of tables stripped across multiple servers. This allows you to present a single virtual table of the data from different SQL Servers.

How to create a view in SQL 2012

  1. Open SQL Server Management Studio.
  2. On the left pane, rightclick “Views”, click on “Add New View”
  3. On here, select a desired query.

You may also create transactSQL to create the view be running command below.

CREATE VIEW allmarketing_view
AS
SELECT marketing FROM hrtable.Employee

You may also change the view by running below command.

ALTER VIEW allmarketing_view
AS
SELECT IT FROM hrtable.Employee

 

<img src="file:///C|/Documents and Settings/Administrator/My Documents/infotechguyz/SQLServer2012/CreatingViewsandVirtualtablesinSQLServer2012.jpg" width="624" height="402" />