The view is an object of the database used for accessing the data from different database tables. The view does not contain data but has tabular structure. Using views, the data security can be achieved by restricting the access on specific rows and columns of the table, rows retrieved using joins, statistical data summary, subsets of views and subsets of another view. The queries can be easily saved using views. The query containing different joins can be added to the table. The query needed very often in the coding are added in the view. View creation The views can be created using the CREATE VIEW statement in SQL. Syntax for view creation Code: CREATE VIEW viewname [ column_list ] [ WITH ENCRYPTION [ , SCHEMABINDING ] As select_statement [ WITH check option ] Where, view_name: It is the name of the view used column_list: Used for declaring the name of the columns used WITH ENCRYPTION: the text of the view is encrypted in the syscomments view SCHEMABINDING: The view is bind to the specific schema As: The action to be executed on the view select_statement: The statement used for defining the view WITH check option: the data modifications are satisfied using the corresponding criteria The view can be created for the tables which have SELECT statement on them. The data cannot be accessed from the temporary tables. The ORDERBY clause cannot be used on the statement. Code: Create view vworderdata As select o.orderid, o.quantity from orderdata o join productdata p on o.quantity = p.quantity Alter view Once user defines a view using the SELECT statement, and later alter the structure of the table by adding columns, the change is not reflected in the view. For adding a new column to the existing view, alter the view. Syntax for alter view Code: ALTER VIEW viewname [ ( columnname) ] [ WITH [ ENCRYPTION ] [ SCHEMABINDING ] As select_statement [WITH CHECK OPTION] Where, viewname is the view to be modified columnname is the name of the column used in the view ENCRYPTION is used for text encryption of the view used in the syscomments view SCHEMABINDING is used for binding the view to the schema related to the tables As defines the actions to be performed on the views select_statement defines the SELECT statement on the view WITH CHECK OPTION is used for restricting the data modification statements on the specific criteria Example: Code: alter view vworderdata as select o.orderid, o.quantity, o.location from orderdata o join productdata p on o.quantity = p.quantity Rename view For changing the name of the view, you need to rename the view. The renaming of view is can be done using the sp_rename system stored procedure. Syntax: Code: sp_rename old_view, new_view where, old_view is the view which needs to be renamed new_view is the new view The view can be renamed if it is present in the same databse. Only the owner or database owner can rename the view. Example: Code: sp_rename vwdata, vwdetails Drop views The DROP VIEW statement is used for removing the view from the database. The definitions and permissions assigned to the view are removed. An error occurs if the view is referencing the dropped table. But dropping the table does not drop the view. Syntax: Code: DROP VIEW view_name Where, view_name is the name of the view Example: Code: DROP VIEW vwdata Indexing views A nonindexed view is created on a table. The indexing of views on the table is possible if the data is very large. The performance of the query is improved using indexes. The optimizer uses the view index in queries when the view is not available in the FROM clause. Create a unique clustered index on a view. The nonclustered index can be created on the view once a unique clustered index is created. The rows of the view are added in the database in the table structure. The unique clustered index is the first index created on the view. The reference from a view to another view in not allowed. The SCHEMABINDING option must be used along with the view. Code: Alter view vworderdata WITH SCHEMABINDING AS select o.orderid, o.quantity, o.location from orderdata o join productdata p on o.quantity = p.quantity Now, create the unique clustered index on the vworderdata. CREATE UNIQUE CLUSTERED INDEX idx_vworderdata on vworderdata ( orderid ) Partitioned views The database contains huge data resulting on the performance of the query. It is necessary to distribute the data over different servers for query performance. The partition of tables is done for distributing the data across servers. Data is added on the basis of the range defined in a column. The distributed partitioned views are used. Partition the views based on the tables for accessed the data available at different servers. Before implementing the view, the table must be partitioned according to the range of key values. Consider the table of employee containing the salary of employees on the basis of empid. The salary of the employee < 100000 are kept in the table as EmpData1 and the one greater than 100000 are kept in EmpData2. The following query are used for executing the values on table Employee1 and Employee2. Code: Select * into Employee1 from employee where salary < 100000 Execute the below query on server2. Code: Select * into Employee2 from employee where salary > 100000 Both the tables are located on isolated servers. So we need to access the data from them. The task can be achieved by performing the following operations. Create linked servers Create the view Linked servers The linked servers are used for accessing the data from distributes servers. User can use the linked server to connect to an instance of the SQL Server and easily access the data. The linked server can be added to the system using the following syntax. Code: sp_addlinkedserver [ @server= ] ‘server’ [ , [ @srvproduct= ] ‘product_name’ ] [ , [ @provider= ] ‘provider_name’ ] [ , [ @datasrc= ] ‘data_source’ ] Where, [ @server= ] is the name of the linked server [ @srvproduct= ] ‘productname’ is the productname of the OLEDB data source to be added as a linked server [ @provider= ] ‘providername’ is the unique identifier for the provider [ @datasrc= ] data_source is the name of the server from where the data is accessed Execute the following statement on the server2 for creating linked server as server1. Code: EXEC sp_linkedserver @server = ‘server1’ @srvproduct = ‘SQLServer OLEDB provider’ @provider = ‘SQLOLEDB’ @datasrc = ‘server01’ The distributed partitioned view is created for accessing the data from the tables. Code: CREATE VIEW View1 AS Select * from Employee1 UNION ALL Select * from Employee2 Catalog views The information about the SQL configuration, objects, databases, etc are in system tables. All users cannot access the data. The catalog views helps user to view the data saved in the system tables. User can view the sys.objects catalog view for displaying the objects of database. Code: select * from sys.objects where create_date < modify_date order by modify_date;
