VIEWS in SQL Server

By Admin at 2 Dec 2009, 19:33 PM
  • What is a view?

    The View is a saved SQL statement and referred as a virtual table. When you create a view, Microsoft SQL Server stores only metadata information about the view, describing the object, its columns, security, dependencies, and so on.

    Some limitations to be aware

    • ORDER BY cannot be used in the view's query unless there is also a TOP or FOR XML specification in the definition.
    • All result columns must have names.
    • All result column names must be unique.
    • A SELECT INTO statement cannot be used in view declaration statement.
    • A view can be created only in the current database.
    • The view doesn’t support input/output parameters.

    Example

    1. CREATEVIEW vwCustomer
    2. AS
    3. SELECT Id, CName, Phone
    4. FROM Customers
    5. Go

    The above example creates a view called “vwCustomer” where the data is derived from the columns of the base “Customer” table.

    You can query data from the view in the same way you do from a table.

    1. SELECT * FROM vwCustomer

    The output of the SELECT statement is:

    undefined

    (3 rows affected)

    ORDER BY Clause

    Let’s try to use the ORDER BY clause in a view:

    1. CREATEVIEW vwCustomer
    2. AS
    3. SELECT id, CName, Phone
    4. FROM Customers
    5. ORDERBY CName
    6. Go

    If you try to run the above example to create the view, you will get the following error because, as said above, the ORDER BY clause can be used only if “TOP” or “FOR XML” is specified.

    Msg 1033, Level 15, State 1, Procedure Customer, Line 4
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    But you can still use ORDER BY clause when you query data from the view as illustrated below:

    1. SELECT Id, CName, Phone
    2. FROM vwCustomer
    3. ORDERBY CName

    undefined

    Or you can use ORDER BY clause inside the view if you specify TOP 100 PERCENT as illustrated below:

    1. CREATEVIEW vwCustomer
    2. AS
    3. SELECTTOP 100 PERCENT Id, CName, Phone
    4. FROM Customers
    5. ORDERBY CName
    6. Go

    The above view should return the rows as ordered so you do not need to re-order them again:

    1. SELECT Id, CName, Phone
    2. FROM vwCustomer

    Refreshing Views

    After creating a view and if there is any column changes in its table, your view will not reflect those changes unless you refresh it.

    Example


    Create a view first

    1. CREATEVIEW vwCustomer
    2. AS
    3. SELECT *
    4. FROM Customers
    5. Go

    List all the columns using the view

    1. SELECT * FROM vwCustomer

    undefined

    Then execute the following code to add a new field in the table

    1. ALTERTABLE dbo.Customers ADD Address varchar(100);

    Now query the view again and see if the new column is there

    1. SELECT * FROM vwCustomer

    The above query will show the id, cname, phone fields only. The newly created “Address” field will not be displayed because the schema change in Customers table was not reflected in the view's metadata information. As far as SQL Server is concerned, the view still has just two columns.

    To refresh the Meta data information of the view, you can use the following command

    Syntax

    1. sp_refreshview sp_refreshview 'vwCustomer' go

    Now run the view again and the new column should be there:

    1. SELECT * FROM vwCustomer

    undefined


    Dropping Views


    You can delete the view from your database by using the DROP VIEW command as below

    Syntax

    1. DROPVIEW

    Example

    1. IF OBJECT_ID('vwCustomer') ISNOTNULL
    2. DROPVIEW dbo.vwCustomer

    Command(s) completed successfully.


    Comments

     

    Post a comment

    Please correct the following: