How to Create a View in T-SQL

Why use a view

• Hide columns or rows. vw_EmployeeDirectory leaves out Salary. Grant users SELECT on the view and nothing on the table, and they can read the directory but never the salaries.
• Give a complicated query a name. Joins and filters that many reports share are written once.
• Keep a stable interface. You can restructure the tables underneath and keep the view's column list unchanged for the applications that read it.

A view is not a performance feature. Querying an ordinary view costs the same as running its SELECT.

Syntax and options

CREATE [OR ALTER] VIEW schema_name.view_name [(column_list)]
[WITH SCHEMABINDING | ENCRYPTION | VIEW_METADATA]
AS
select_statement
[WITH CHECK OPTION];

• CREATE VIEW must be the first statement in its batch. In a script, put GO before it, or you get "Msg 111: 'CREATE VIEW' must be the first statement in a query batch."
• CREATE OR ALTER VIEW creates the view if it is missing and replaces its definition if it exists, keeping the permissions already granted on it. It is available from SQL Server 2016 SP1. Dropping and recreating a view loses its permissions.
• Every column must have a unique name. Give expressions an alias: Salary * 12 AS AnnualSalary.
• A view can reference tables, other views and functions, but not temporary tables or table variables, and it cannot use SELECT ... INTO.
• A view takes no parameters. If you need them, write an inline table-valued function.

Always schema-qualify the view and the tables inside it (dbo.Employees, not Employees).

Updating data through a view

INSERT, UPDATE and DELETE work against a view when SQL Server can map the change unambiguously to one base table:

CREATE VIEW dbo.vw_ActiveEmployees
AS
SELECT EmployeeId, FullName, Email, DepartmentId, IsActive
FROM dbo.Employees
WHERE IsActive = 1;
GO

UPDATE dbo.vw_ActiveEmployees
SET Email = N'ajay.kumar@example.com'
WHERE EmployeeId = 1;

The rules:

• A single statement can change columns of only one base table, even if the view joins several. Otherwise: "Msg 4405: View or function is not updatable because the modification affects multiple base tables."
• Columns built from expressions, aggregates or window functions cannot be modified.
• The view cannot be updatable if it uses GROUP BY, HAVING, DISTINCT, UNION, or TOP together with WITH CHECK OPTION.
• An INSERT must be able to supply every base-table column that has no default and does not allow NULL. vw_ActiveEmployees cannot accept inserts, because it does not expose the required Salary column.

When the built-in rules are too strict, an INSTEAD OF trigger on the view lets you write the logic yourself.

WITH CHECK OPTION

Without it, you can change a row through a view so that the row no longer matches the view's filter, and it vanishes from the view:

UPDATE dbo.vw_ActiveEmployees SET IsActive = 0 WHERE EmployeeId = 3;   -- succeeds; row 3 disappears from the view

Adding WITH CHECK OPTION to the end of the view definition rejects such changes with Msg 550, "The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint."

ORDER BY is not allowed

A view is an unordered set, so this fails:

CREATE VIEW dbo.vw_Sorted AS
SELECT EmployeeId, FullName FROM dbo.Employees ORDER BY FullName;

Msg 1033: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,
and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The old workaround, SELECT TOP (100) PERCENT ... ORDER BY, compiles but does not sort: the optimizer ignores it. Put the ORDER BY in the query that selects from the view.

SELECT * and stale views

Column metadata is captured when the view is created. If a view is defined with SELECT * and someone later adds a column to the table, the view does not pick it up. If columns are dropped or reordered, the view can return data under the wrong column names.

List columns explicitly in view definitions. To repair a view after its tables change, refresh its metadata:

EXEC sys.sp_refreshview N'dbo.vw_EmployeeDirectory';

SCHEMABINDING

WITH SCHEMABINDING ties the view to the objects it references. Nobody can drop those tables, or alter the columns the view uses, until the view is dropped or changed. That prevents the stale-view problem entirely.

CREATE OR ALTER VIEW dbo.vw_EmployeeDirectory
WITH SCHEMABINDING
AS
SELECT e.EmployeeId, e.FullName, e.Email, d.DepartmentName
FROM dbo.Employees AS e
JOIN dbo.Departments AS d ON d.DepartmentId = e.DepartmentId;

A schema-bound view must use two-part names (dbo.Employees) and cannot use SELECT *.

Indexed views

Creating a unique clustered index on a schema-bound view materializes it. The result set is stored on disk and maintained automatically whenever the base tables change:

CREATE VIEW dbo.vw_HeadcountByDepartment
WITH SCHEMABINDING
AS
SELECT DepartmentId, COUNT_BIG(*) AS Headcount, SUM(Salary) AS TotalSalary
FROM dbo.Employees
GROUP BY DepartmentId;
GO

CREATE UNIQUE CLUSTERED INDEX CIX_vw_HeadcountByDepartment
    ON dbo.vw_HeadcountByDepartment (DepartmentId);

This can make an expensive aggregate nearly free to read, at the cost of extra work on every write to the base table. The restrictions are long: no outer joins, no DISTINCT, no subqueries, no MIN or MAX, COUNT_BIG(*) is required with GROUP BY, and several session SET options must have specific values. Enterprise edition uses an indexed view automatically when it helps a query. On Standard and Express, reference the view by name with the NOEXPAND hint:

SELECT DepartmentId, Headcount
FROM dbo.vw_HeadcountByDepartment WITH (NOEXPAND);

Permissions

To create a view you need the CREATE VIEW permission in the database and ALTER permission on the schema.

Users who query the view need SELECT on the view only. When the view and its tables have the same owner, which is the normal case when everything is in dbo, SQL Server does not check permissions on the tables. This is called ownership chaining, and it is what makes views usable as a security layer.

GRANT SELECT ON dbo.vw_EmployeeDirectory TO [hr_readers];

Ownership chaining does not cross databases by default. A view that selects from another database requires the user to have access there too.

Finding, changing and dropping views

-- list views
SELECT SCHEMA_NAME(schema_id) AS SchemaName, name, create_date, modify_date
FROM sys.views;

-- show a definition
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.vw_EmployeeDirectory'));

-- what does the view depend on?
SELECT referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.vw_EmployeeDirectory');

-- remove it (IF EXISTS needs SQL Server 2016 or later)
DROP VIEW IF EXISTS dbo.vw_ActiveEmployees;

Dropping a view does not touch the underlying data. It does remove the permissions granted on the view and any indexes on it.

WITH ENCRYPTION hides the definition from OBJECT_DEFINITION and the catalog views. It is obfuscation, not security, and it also leaves you unable to script the view later. Keep the source in version control if you use it.

Common mistakes

• Stacking views on views. Each layer looks simple, but SQL Server has to expand all of them, and a query that needs two columns can end up joining a dozen tables. If a query against a view is slow, look at the execution plan for tables you did not expect.
• Assuming a view caches results. Only an indexed view stores data.
• Filtering a view that contains aggregates or window functions. The filter cannot always be pushed down to the base tables, so the view may compute everything before discarding most of it. An inline table-valued function with a parameter is often a better fit.
• Calling the kinds "simple" and "complex" views. That terminology comes from Oracle. SQL Server distinguishes ordinary views, indexed views and partitioned views.

Further reading

• CREATE VIEW (Transact-SQL)
• Create indexed views
• Views

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting