CREATE VIEW All Versions

Examples

• 2

  CREATE VIEW

  edit move example delete share

  2

  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
  

  edited Aug 2 at 18:06

  

  

  
• 2

  CREATE VIEW With an Index

  edit move example delete share

  2

  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
  )
  

  created Jul 29 at 1:11

  
• 2

  CREATE VIEW With Encryption

  edit move example delete share

  2

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

  edited Jul 25 at 22:38

  

  
• 2

  CREATE VIEW With INNER JOIN

  edit move example delete share

  2

  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%'
  

  edited Aug 2 at 12:46

  

  

  
• 0

  Calculated Columns

  edit move example delete share

  0

  The main reason for hiding computations in a VIEW is so that the computation is done one way, one place, one time and so it gets a unique data element name. One common use for a VIEW is to provide summary data across a row. For example, given a table with measurements in metric units, we can construct a VIEW that hides the calculations to convert them into English units.

  It is important to be sure that you have no problems with NULL values when constructing a calculated column. For example, given a Personnel table with columns for both salary and commission, you might construct this VIEW:

  CREATE VIEW Payroll (emp_nbr, paycheck_amt)
  AS 
  SELECT emp_nbr, (salary + COALESCE(commission), 0.00)
   FROM Personnel;
  

  https://www.simple-talk.com/sql/t-sql-programming/sql-view-beyond-the-basics/

  created Aug 4 at 15:23

  
• 0

  Grouped VIEWs

  edit move example delete share

  0

  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/

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

  created Aug 4 at 15:23

  
• 0

  UNION-ed VIEWs

  edit move example delete share

  0

  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/

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

  created Aug 4 at 15:23