# 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));

← MERGE Views →