SQL Server CREATE VIEW Guide with Examples in sqlcmd and DbSchema

Someone on the support desk needs to see the country each customer belongs to, and nothing else in the customer record. You can hand them a SELECT and hope they run it correctly every time, or you can store that SELECT in the database under a name and grant them the name. CREATE VIEW is the second option: a view is queried like a table, holds no data of its own, and runs its query each time someone selects from it.

What a view is, and what SQL Server stores for it

A view is a virtual table whose columns and rows are defined by a query. Nothing is copied: SQL Server 2022 stores the definition, not a result, and each query against the view is translated into a query against the underlying tables. A view can have up to 1,024 columns, can be created only in the current database, and its CREATE VIEW statement has to be the first statement in its batch.

The definition itself is readable afterwards. The view appears in sys.views and sys.columns, and the text of the statement that created it is in sys.sql_modules, unless the view was created with ENCRYPTION.

What views are for

The CREATE VIEW page gives three purposes, and they are worth reading as three different jobs rather than one.

The first is to focus, simplify and customize the picture each user has of the database, which is the case where a five-table join lives in the view instead of in every report that needs it. The second is security: you grant access to the view without granting permissions on the base tables, so the support desk sees the two columns in the view and none of the rest. The third is compatibility, where a view emulates a table whose schema has changed, so an application written against the old shape keeps working while the tables underneath move.

Creating a view in sqlcmd

Connect to the instance, substituting your own server, database, login and password. The article on how to create a SQL Server database covers getting that far:

sqlcmd -S servername -d dbname -U username -P password

These examples run against one table of customers, in a few countries:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(50),
    ContactName NVARCHAR(50),
    Country NVARCHAR(50)
);

INSERT INTO Customers VALUES
  (1, 'Alfreds', 'Maria', 'Germany'),
  (2, 'Ana Trujillo', 'Ana', 'Mexico'),
  (3, 'Around the Horn', 'Thomas', 'UK'),
  (4, 'Chop-suey', 'Yang', 'USA'),
  (5, 'Great Lakes', 'Howard', 'USA'),
  (6, 'Island Trading', 'Helen', 'UK');

The SQL Server CREATE TABLE guide covers that statement in full. Now name a query over it:

CREATE VIEW USA_Customers AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'USA';

Select from the view exactly as you would from a table:

SELECT * FROM USA_Customers;

Two columns and two rows come back, because those are the columns the view lists and the rows its WHERE keeps. Change a customer's country in Customers and the next select from USA_Customers reflects it, since the view holds no copy.

View attributes

Three attributes can follow WITH in the CREATE VIEW statement:

SCHEMABINDING is the one to reach for. It stops anyone from altering or dropping a base table in a way that would break the view definition, and it requires the view's SELECT to use two-part names such as dbo.Customers for every object it references, all of them in the same database.

A fourth clause, WITH CHECK OPTION, goes after the SELECT rather than after WITH. It forces every modification made through the view to follow the criteria in the view's own SELECT, so a row changed through USA_Customers is still visible through USA_Customers once the change is committed.

Restrictions on the query inside a view

Four things the SELECT clauses in a view definition cannot include:

• ORDER BY, unless a TOP clause is also in the select list
• The INTO keyword
• The OPTION clause
• A reference to a temporary table or a table variable

The ORDER BY exception is narrower than it looks. Where TOP or OFFSET is present, the ORDER BY decides which rows those clauses return, and it still does not guarantee that a later SELECT from the view comes back sorted. To get sorted output, put the ORDER BY in the query that reads the view.

Aggregates, joins, UNION and UNION ALL, functions and other views are all allowed inside the definition. What they cost is the ability to write through the view.

Permissions required for creating a view

Creating a view requires the CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created. Selecting from the objects the view reads requires the usual permissions on those objects at the time the view is created. Whoever queries the view afterwards needs permission on the view alone, which is the mechanism behind the security purpose above.

Advantages and limitations of using a view

The advantage is one definition in one place, queried by name, with its own permissions. The limitation is that not every view accepts writes.

You can modify data through a view when the modification references columns from only one base table, the columns are not derived from an aggregate or a computation, and they are not affected by GROUP BY, HAVING or DISTINCT. USA_Customers meets all of that, so an update through it reaches Customers:

UPDATE USA_Customers SET ContactName = 'Yang Wang' WHERE CustomerName = 'Chop-suey';
SELECT CustomerID, CustomerName, ContactName, Country FROM Customers WHERE Country = 'USA';

Where a view joins several tables or aggregates them, those conditions fail, and the answer is an INSTEAD OF trigger: SQL Server runs the trigger in place of the INSERT, UPDATE or DELETE, and the trigger decides which base tables to write. A view with an INSTEAD OF trigger for a statement is updatable through that statement.

Creating a view in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

DbSchema connects to SQL Server, reverse-engineers the database into a design model, and draws the tables and their foreign keys on a diagram, which is where you decide what a view should join before you write it. Reverse-engineering reads the database and fills the model; it changes nothing in SQL Server.

To create the view, open the SQL Editor from the Editors menu, paste the CREATE VIEW statement into it, and click Run Script to execute the whole editor content. That statement runs against the connected database, so the view exists in SQL Server as soon as it succeeds, while the design model still shows the schema as it was. Choose Schema → Refresh Schema from Database, described on the Synchronize with the Database page, to read the new view back into the model. Then Execute Query on SELECT * FROM USA_Customers; returns the rows as a table in the result pane, and you can double-click a cell to edit the value inline.

Views cost nothing to keep and are the cheapest way to stop a complicated SELECT from being copied around. Write the definition once, add SCHEMABINDING, and grant the view rather than the tables. Download DbSchema at https://dbschema.com/download.html, connect to your SQL Server database, and run the statements above in the SQL Editor: connecting, reverse-engineering, the diagram and the SQL Editor are all in the free Community Edition, and reading the new view back into the model is part of schema synchronization, in the Pro edition.

Sources

1. CREATE VIEW (Transact-SQL)
2. DbSchema SQL Editor
3. DbSchema Synchronize with the Database