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
In Object Explorer, expand the database where you want to create your new view.
Right-click the Views folder, then select New View....
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.
Select Add, then select Close.
In the Diagram Pane, select the columns or other elements to include in the new view.
In the Criteria Pane, select additional sort or filter criteria for the columns.
On the File menu, select Save view name.
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
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
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
.
Creating Views and Stored Procedures
Now that Mary can access the TestData database, you may want to create some database objects, such as a view and a stored procedure, and then grant Mary access to them. A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch.
Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.
For this example, you will use CREATE VIEW to create a view that selects only two of the columns in the Products table. Then, you will use CREATE PROCEDURE to create a stored procedure that accepts a price parameter and returns only those products that cost less than the specified parameter value.
Procedures
To create a view
1. Execute the following statement to create a very simple view that executes a select statement, and returns the names and prices of our products to the user.
CREATE VIEW vw_Names
AS SELECT ProductName, Price
FROM Products;
GO
Test the view
1. Views are treated just like tables. Use a SELECT statement to access a view.
SELECT * FROM vw_Names;
GO
Breakdown of your Risk Level
Related Content
