Views in SQL Server

Discussion in 'SQL Server' started by MinalS, Apr 26, 2016.

  1. 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.
    1. Create linked servers
    2. 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;
    
    [​IMG]
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice