CREATE VIEW

Create a View, a virtual table that represents the data in one or more tables in an alternate way.

Syntax
      CREATE VIEW [schema.] view [ (column [,...n] ) ] 
         [WITH [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA] [,...n] ] 
            AS select_statement [;]
               [WITH CHECK OPTION]

Key
   view    Name of the view to be created.

   column  Name to be used for a column in a view. 
           defaults to the same name as the column(s) in the SELECT statement.

   select_statement The view definition. This SELECT statement can reference
                    more than one table/view. Appropriate permissions are required.
                    cannot include a COMPUTE or ORDER BY clause (unless SELECT TOP..)

   CHECK OPTION   Enforce the criteria set with select_statement even
                  when modifying the data. This only affects the view not the underlying table.

   ENCRYPTION     Encrypts the text of the CREATE VIEW statement. 

   SCHEMABINDING  Bind the view to the schema of the underlying table or tables.

   VIEW_METADATA  Return metadata information about the view to client APIs
                  this allows updatable client-side cursors to address the view.

A view will always return rows in essentially random order, like a table, unless explicitly sorted: SELECT.. from View Order By..

Example

CREATE VIEW emp_view
AS
SELECT c.FirstName, c.LastName, s.StaffID, s.HireDate
FROM MySchema.Staff S JOIN MySchema.Customers c on c.CustomerID = c.StaffID ;
GO

# We always did feel the same, We just saw it from a different point of view# - Bob Dylan, Tangled Up In Blue

Related commands:

ALTER VIEW
DROP VIEW
sp_refreshview
sys.views
Equivalent Oracle command: CREATE VIEW


© Copyright SS64.com 1999-2014
Some rights reserved