Views: name and reuse a query

The same selection can appear in different reports, integrations, and screens. A view is an object that assigns a name to a query and presents its result as a reusable set.

The view can simplify access and permissions, but it does not automatically store a separate copy of the data.

Create a view

CREATE VIEW dbo.vw_ProductosActivos
AS
    SELECT
        p.IdProducto,
        p.IdCategoria,
        p.Nombre,
        p.Precio,
        p.Existencia
    FROM dbo.Producto AS p
    WHERE p.Activo = 1;
GO

CREATE VIEW must appear as the first statement in its batch, the group of statements sent together to SQL Server. GO is not a Transact-SQL statement; it is a separator recognized by tools such as SQL Server Management Studio.

Query the view

SELECT
    v.IdProducto,
    v.Nombre,
    v.Precio
FROM dbo.vw_ProductosActivos AS v
WHERE v.Precio <= 800
ORDER BY v.Precio;

The outer filter can be combined with the definition during optimization. Still, a complex view does not guarantee a fast query; we must review the execution plan, the strategy chosen by SQL Server.

Modify it

ALTER VIEW dbo.vw_ProductosActivos
AS
    SELECT
        p.IdProducto,
        p.IdCategoria,
        p.Nombre,
        p.Precio,
        p.Existencia,
        p.FechaActualizacion
    FROM dbo.Producto AS p
    WHERE p.Activo = 1;
GO

We can also use CREATE OR ALTER VIEW to have the same script create the view when it is missing or update its definition when it already exists.

What a view does not offer

A normal view does not accept parameters. If we need to receive values, a table-valued function or procedure may better represent the interface.

Nor should we depend on a written order within the view. The consumer uses his own ORDER BY to define the final presentation.

Explicit columns

Avoiding SELECT * helps keep the view contract stable: the names, types, and meaning expected by its consumers. Adding a column to the table should not expose it accidentally to all of them.

When underlying objects—the tables or views used by the definition—change, we review and update the view. SCHEMABINDING binds that definition to their schema and prevents certain incompatible changes, but it introduces additional requirements.

Security and modifications

We can grant access to a view without directly exposing every table column. Its effectiveness depends on ownership, permissions—the authorizations granted to an account or role—and the access path.

Some views are updatable and allow the underlying table to be modified; others don’t. We do not assume that a view is read-only just because it is a view.

What should be checked

We review dependencies, permissions, exposed columns, plans, and consumers before changing a view. An apparently compatible change can break applications that expect specific names, types, or nullability; nullability indicates whether a column accepts NULL.

We also avoid deep layers of views upon views. They can hide joins and filters until it is difficult to understand the true cost of the query.