Views in SQL Server

View can be described as virtual table which derived its data from one or more than one table columns. It is stored in the database. It is used to implements the security mechanism in the Sql Server

Types of Views

Standard Views

Combining data from one or more tables through a standard view lets you satisfy most of the benefits of using views. These include focusing on specific data and simplifying data manipulation.

Indexed Views

An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

Partitioned Views

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.

Advantages: 

• Simplify a complex table structure

• Simplify your security model by allowing you to filter sensitive data and assign permissions in a simpler fashion

• Allow you to change the logic and behavior without changing the output structure (the output remains the same but the underlying SELECT could change significantly)

• Increase performance (Sql Server Indexed Views)

Remarks:

• A view can have a maximum of 1,024 columns.

• If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.

• A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

•  The Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are used to parse the view when the view is used. Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed.

Remarks:

• A view can have a maximum of 1,024 columns.

• If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.

• A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

•  The Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are used to parse the view when the view is used. Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed

Creating a View

Create view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId,
     EmployeeInfo.EmpName,
     EmpProjInfo.Projectname
  from
    EmployeeInfo inner join EmpProjInfo    
   
    on EmployeeInfo.EmpId=EmpProjInfo.EmpId


Altering an View

Alter view Vw_EmployeeProj As

  Select
    EmployeeInfo.EmpId,
    EmployeeInfo.EmpName,
    EmpProjInfo.Projectname
  from
    EmployeeInfo inner join  EmpProjInfo

    on EmployeeInfo.EmpId=EmpProjInfo.EmpId
   
    where EmployeeInfo.EmpId in (2,3,4)

Getting Information about the Views
We can use the System Procedure Sp_Helptext to get the definition about the views.

For example, we can use the sp_helptext command to get the information about the view Vw_EmployeeProj
sp_helptext Vw_EmployeeProj


Renaming the View

If we want to rename our view View_Employeeinfo to Vw_EmployeeInfo,we can write the sp_rename command as follows:

sp_rename 'View_Employeeinfo', 'Vw_EmployeeInfo'


Dropping a View
We can use the Drop command to drop a view. For example, to drop the view Vw_EmployeeInfo, we can use the following statement

Drop view Vw_EmployeeInfo