# CREATE VIEW

# CREATE Indexed VIEW

To create a view with an index, the view must be created using the WITH SCHEMABINDING keywords:

CREATE VIEW view_EmployeeInfo
WITH SCHEMABINDING
AS   
    SELECT EmployeeID,
        FirstName,
        LastName,
        HireDate  
    FROM [dbo].Employee
GO

Any clustered or non-clustered indexes can be now be created:

CREATE UNIQUE CLUSTERED INDEX IX_view_EmployeeInfo
ON view_EmployeeInfo
(
     EmployeeID ASC
)

There Are some limitations to indexed Views:

  • The view definition can reference one or more tables in the same database.
  • Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
  • You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.
  • You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
  • It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
  • For more information about creating indexed Views you can read this MSDN article (opens new window)

    # CREATE VIEW

    CREATE VIEW view_EmployeeInfo
    AS   
        SELECT EmployeeID,
            FirstName,
            LastName,
            HireDate  
        FROM Employee
    GO
    
    

    Rows from views can be selected much like tables:

    SELECT FirstName
    FROM view_EmployeeInfo
    
    

    You may also create a view with a calculated column. We can modify the view above as follows by adding a calculated column:

    CREATE VIEW view_EmployeeReport
    AS   
        SELECT EmployeeID,
            FirstName,
            LastName,
            Coalesce(FirstName,'') + ' ' + Coalesce(LastName,'') as FullName,
            HireDate  
        FROM Employee
    GO
    
    

    This view adds an additional column that will appear when you SELECT rows from it. The values in this additional column will be dependent on the fields FirstName and LastName in the table Employee and will automatically update behind-the-scenes when those fields are updated.

    # CREATE VIEW With Encryption

    CREATE VIEW view_EmployeeInfo
    WITH ENCRYPTION
    AS   
    SELECT EmployeeID, FirstName, LastName, HireDate  
    FROM Employee
    GO
    
    

    # CREATE VIEW With INNER JOIN

    CREATE VIEW view_PersonEmployee
    AS  
        SELECT P.LastName,
            P.FirstName,
            E.JobTitle
        FROM Employee AS E
        INNER JOIN Person AS P  
            ON P.BusinessEntityID = E.BusinessEntityID
    GO
    
    

    Views can use joins to select data from numerous sources like tables, table functions, or even other views. This example uses the FirstName and LastName columns from the Person table and the JobTitle column from the Employee table.

    This view can now be used to see all corresponding rows for Managers in the database:

    SELECT *
    FROM view_PersonEmployee
    WHERE JobTitle LIKE '%Manager%'
    
    

    # Grouped VIEWs

    A grouped VIEW is based on a query with a GROUP BY clause. Since each of the groups may have more than one row in the base from which it was built, these are necessarily read-only VIEWs. Such VIEWs usually have one or more aggregate functions and they are used for reporting purposes. They are also handy for working around weaknesses in SQL. Consider a VIEW that shows the largest sale in each state. The query is straightforward:

    https://www.simple-talk.com/sql/t-sql-programming/sql-view-beyond-the-basics/ (opens new window)

    CREATE VIEW BigSales (state_code, sales_amt_total)
    AS SELECT state_code, MAX(sales_amt)
         FROM Sales
        GROUP BY state_code;
    
    

    # UNION-ed VIEWs

    VIEWs based on a UNION or UNION ALL operation are read-only because there is no single way to map a change onto just one row in one of the base tables. The UNION operator will remove duplicate rows from the results. Both the UNION and UNION ALL operators hide which table the rows came from. Such VIEWs must use a , because the columns in a UNION [ALL] have no names of their own. In theory, a UNION of two disjoint tables, neither of which has duplicate rows in itself should be updatable.

    https://www.simple-talk.com/sql/t-sql-programming/sql-view-beyond-the-basics/ (opens new window)

    CREATE VIEW DepTally2 (emp_nbr, dependent_cnt)
    AS (SELECT emp_nbr, COUNT(*)
          FROM Dependents
         GROUP BY emp_nbr)
       UNION
       (SELECT emp_nbr, 0
          FROM Personnel AS P2
         WHERE NOT EXISTS 
              (SELECT *
                 FROM Dependents AS D2
                WHERE D2.emp_nbr = P2.emp_nbr));