View - Sql server basics
Views in SQL Server

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database.

Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

EXAMPLE :

CREATE VIEW
-----------------

CREATE VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

MODIFY VIEW
----------------
ALTER VIEW "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))

DROP VIEW
-------------

DROP VIEW SampleView



Different Types of VIEW :

There are two different types of VIEW
  • System View
    • Information Schema View
    • Catalog View
    • Dynamic Management View(DMV)
  • User Define View
    • Simple View
    • Complex View
System View

In SQL Server there are few System Database available, like Master, Temp, msdb, tempdb . Each and . Each and every data base having its own responsibility, like master data is one of the template database for all the database which are created in SQL Server 2005. Similarly, System Views are predefined Microsoft created views that are already existed in master DB. These are also used as template View for all new database. These System Views will be automatically inserted into any user created database. There are around 230 System view available.
We can explore the System view from the SQL Server Management Studio. Expand any Database > View > System View



In SQL Server all system view are divided into different schema. These are used for the security container of SQL Server database. We can categorized system view in following way,
  • Information Schema View
  • Catalog View
  • Dynamic Management View (DMV)
Now all above category are itself a huge topic, So I will not going to Details of It. Lets have a look into the over view of those view type

Information View


These are the one of the most important system grouped view. There are twenty different schema views in this group. This are used for displaying most physical information of a database, such as table, columns. Naming Conation of this type of views are

Lets see it with one Example,

I have create on Database named, ViewDemo. It having on table called EmpInfo and below diagram showing you the design of the table,


Now, if we want to know the details information columns of table Empinfo using View we have to run the following query,

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='EmpInfo'
Following will be the output


Similarly we can use all other Schema View to read Database information.

Catalog View


This type of view are introduced in SQL Server 2005. catalog view are categorized in different group also. These are used to show the database self describing information.
As for example, 
select * from sys.tables
and following is the sample output



Dynamic Management View


This is newly introduced in SQL Server 2005.This Views gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. In SQL Server 2005, there are two types of DMV
1. Server-scoped DMV: Stored in Master Database
2. Database-scoped DMV: Specific to each database
As for example if we want to check the all SQL Server connection, we have to use following query, 

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

And following is the sample output



If you want to know details on DMV, Here is an Complete article on Code project , Dynamic Management Views [DMV] - A SQL Server 2005 Feature [^]
Note: There are many things to learn on System View, I have just introduced them for beginners . If any one having more interest can have a look into this article System Views in SQL Server 2005 [^]

User Define View

Still now I have described about System View, Now have a look into User Define View. This views are created by the user as per requirement. There is no such classification of UDV and how to create the view, I have already explained the syntax. Now we can again have a look into another view creation.

CREATE VIEW DemoView
AS
SELECT EmpID, EmpName, Phone
FROM EmpInfFROM EmpInfo

When To Use A View

There are a number of scenarios where we will like to create our own view
  1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
2. To control access to rows and columns of data.

View Creation Option

There are two different option for creating a view.
  • Schema Binding Option 
  • Encryption 

Schema BindSchema Binding  Option : 

If we Creates a view with the SCHEMABINDING option it will locks the tables being referred by the view and restrict  any kinds of  changes that may change the table schema ( No Alter Command) . While creating schema binding view, we can't mention "Select * from tablename" with the query. We have to mention all the column name for reference
As for example, 
CREATE VIEW DemoSampleView
With SCHEMABINDING
As
SELECT
          EmpID,
          EmpName,
FROM DBO.EmpInfo
And  one more things that we need to remember, while specifying the  Database name we have use Dbo.[DbName] .After creating the view, try to alter the table EmpInfo , we cannot do it! This is the power of the SCHEMABINDING option.
If we want to change/Alter the defination of a table which refered by a schema binded view, we will get following error message. 


Encryption :

This option encrypts the definitionThis option encrypts the definition of the view. Users will not be able to see the definition of the View after it is created. This is the main adavatages of view where we can make it secure 
CREATE VIEW DemoView
With ENCRYPTION.EmpInfo
Note:  Once view is  encrypted, there is no way to decrypt it again.


Advantages :

- To hide data complexity

- To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For Example, customer names, addresses and their social security numbers

- A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement. 

- Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.

Some Important Restrictions Or Disadvantages :

- If the new table or view structure changes, the view must be dropped and re-created.

- A SELECT INTO statement cannot be used in view declaration statement

- A trigger or an index cannot be defined on a view

- Views can especially degrade the performance if they are based on other views. Therefore, it is recommended NOT to create views based on other views.

- It is not faster than the query that defines them

-         Some Importants please refer the following link :

                                            

When To Use A View

You need to have a goal in mind when creating a view. There are a number of scenarios where you will want to look for a view as a solution.
  • To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  • To control access to rows and columns of data.
  • To aggregate data for performance.
INFORMATION_SCHEMA.[View Name] . From the System View Image we can get the few name of Information Schema View.
08:32 | Category: | 0 comments

Comments (0)

Post a Comment