Views — CREATE VIEW, Updatable Views and Indexed Views

A view is a named SELECT query stored in the database — it looks like a table to the caller but executes the underlying query each time. Views encapsulate complex joins, provide a stable API surface over changing tables, and restrict column access. Indexed views (materialised views) pre-compute and store the result, making aggregate queries instant. EF Core can query views exactly like tables using modelBuilder.Entity<T>().ToView("viewName"), making them a clean integration point between complex SQL and the application layer.

Creating and Using Views #

-- ── Basic view — published post summary ────────────────────────────────────
CREATE OR ALTER VIEW dbo.vw_PublishedPostSummary
WITH SCHEMABINDING    -- prevents dropping referenced tables/columns
AS
SELECT
    p.Id,
    p.Title,
    p.Slug,
    p.Excerpt,
    p.CoverImageUrl,
    p.ViewCount,
    p.CommentCount,
    p.IsFeatured,
    p.PublishedAt,
    p.UpdatedAt,
    u.DisplayName   AS AuthorName,
    u.AvatarUrl     AS AuthorAvatarUrl,
    c.Name          AS CategoryName,
    c.Slug          AS CategorySlug
FROM   dbo.Posts      p
JOIN   dbo.Users      u ON u.Id  = p.AuthorId
LEFT   JOIN dbo.Categories c ON c.Id  = p.CategoryId
WHERE  p.IsPublished = 1
  AND  p.Status      = 'published';
GO

-- ── Query the view — same syntax as a table ───────────────────────────────
SELECT Id, Title, AuthorName, ViewCount
FROM   dbo.vw_PublishedPostSummary
WHERE  CategorySlug = 'dotnet'
ORDER  BY PublishedAt DESC;

-- ── Author statistics view ────────────────────────────────────────────────
CREATE OR ALTER VIEW dbo.vw_AuthorStats
WITH SCHEMABINDING
AS
SELECT
    u.Id            AS AuthorId,
    u.DisplayName,
    u.AvatarUrl,
    COUNT_BIG(p.Id) AS TotalPosts,       -- COUNT_BIG required for indexed views
    SUM(p.ViewCount)    AS TotalViews,
    SUM(p.CommentCount) AS TotalComments,
    MAX(p.PublishedAt)  AS LastPublishedAt
FROM   dbo.Users u
JOIN   dbo.Posts p ON p.AuthorId = u.Id
WHERE  p.IsPublished = 1
GROUP  BY u.Id, u.DisplayName, u.AvatarUrl;
GO

-- ── Indexed view — materialise the aggregate ──────────────────────────────
-- Requirements: SCHEMABINDING, deterministic expressions, specific options
CREATE UNIQUE CLUSTERED INDEX IX_vw_AuthorStats
ON dbo.vw_AuthorStats (AuthorId);
-- SQL Server now stores the pre-computed result and updates it on DML changes
-- Querying the view uses the index rather than re-running the aggregation

-- ── WITH CHECK OPTION — prevent inserts/updates that violate the view filter ─
CREATE OR ALTER VIEW dbo.vw_DraftPosts
WITH SCHEMABINDING
AS
SELECT Id, Title, Status, AuthorId
FROM   dbo.Posts
WHERE  Status = 'draft'
WITH   CHECK OPTION;  -- prevents UPDATE that would change Status away from 'draft'
GO

-- ── View metadata ─────────────────────────────────────────────────────────
SELECT name, create_date, modify_date, is_schema_bound
FROM   sys.views
WHERE  name LIKE 'vw_%';

-- ── Drop view safely ──────────────────────────────────────────────────────
DROP VIEW IF EXISTS dbo.vw_OldView;

EF Core Integration #

// ── Map a keyless entity to a view ────────────────────────────────────────
public class PublishedPostSummary  // no Id property needed for keyless
{
    public int     Id            { get; init; }
    public string  Title         { get; init; } = "";
    public string  Slug          { get; init; } = "";
    public string? Excerpt       { get; init; }
    public int     ViewCount     { get; init; }
    public string  AuthorName    { get; init; } = "";
    public string? CategorySlug  { get; init; }
    public DateTime? PublishedAt { get; init; }
}

// In DbContext:
protected override void OnModelCreating(ModelBuilder mb)
{
    mb.Entity<PublishedPostSummary>()
      .HasNoKey()
      .ToView("vw_PublishedPostSummary");
}

// Query the view:
var posts = await _db.Set<PublishedPostSummary>()
    .Where(p => p.CategorySlug == "dotnet")
    .OrderByDescending(p => p.PublishedAt)
    .ToListAsync(ct);

Common Mistakes #

Mistake 1 — SELECT * in a schema-bound view (syntax error) #

❌ Wrong — CREATE VIEW vw_Posts WITH SCHEMABINDING AS SELECT * FROM dbo.Posts; SELECT * not allowed with SCHEMABINDING.

✅ Correct — always list columns explicitly: SELECT p.Id, p.Title, ....

Mistake 2 — Assuming views automatically improve query performance #

❌ Wrong — creating a view expecting it to be faster; without an index it is just syntactic sugar over the same query.

✅ Correct — non-indexed views are for encapsulation; add a clustered index (indexed view) for materialised performance gains.