Skip to content

Latest commit

 

History

History
90 lines (61 loc) · 3.2 KB

create-views.md

File metadata and controls

90 lines (61 loc) · 3.2 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Create views
Create views in the Database Engine with SQL Server Management Studio or Transact-SQL.
WilliamDAssafMSFT
wiassaf
randolphwest
05/10/2023
sql
table-view-index
conceptual
views [SQL Server], creating
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Create views

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

You can create views in the [!INCLUDE ssdenoversion-md] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. A view can be used for the following purposes:

  • To focus, simplify, and customize the perception each user has of the database.

  • As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.

  • To provide a backward compatible interface to emulate a table whose schema has changed.

Limitations and restrictions

A view can be created only in the current database.

A view can have a maximum of 1,024 columns.

Permissions

Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

Use SQL Server Management Studio

  1. In Object Explorer, expand the database where you want to create your new view.

  2. Right-click the Views folder, then select New View....

  3. In the Add Table dialog box, select the element or elements that you want to include in your new view from one of the following tabs: Tables, Views, Functions, and Synonyms.

  4. Select Add, then select Close.

  5. In the Diagram Pane, select the columns or other elements to include in the new view.

  6. In the Criteria Pane, select additional sort or filter criteria for the columns.

  7. On the File menu, select Save view name.

  8. In the Choose Name dialog box, enter a name for the new view and select OK.

    For more information about the query and view designer, see Query and View Designer Tools (Visual Database Tools).

Use Transact-SQL

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2022;
GO

CREATE VIEW HumanResources.EmployeeHireDate
AS
SELECT p.FirstName,
    p.LastName,
    e.HireDate
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
    ON e.BusinessEntityID = p.BusinessEntityID;
GO

-- Query the view
SELECT FirstName,
    LastName,
    HireDate
FROM HumanResources.EmployeeHireDate
ORDER BY LastName;
GO

Next steps