如何在SQL Server中创建复杂视图？视图创建的性能与限制问题咨询

Hey there! Let's break down how to solve this problem since you're stuck between two less-than-ideal options for creating your Result view.

First, let's recap the pain points:

• Option 1 (direct view from source) is slow because every time you query Result, the database re-runs all the JOIN and GROUP BY logic from the source view plus any additional aggregation in Result—that's redundant computation killing performance.
• Option 2 (temp table + view) doesn't work because SQL Server won't let you reference temporary tables (#temp/##temp) in a view definition—views rely on permanent database objects, and temp tables vanish when your session ends.

Here are the practical, performant alternatives you can use:

1. Merge Query Logic to Avoid Redundant Computation #

Instead of nesting views, combine the source view's JOIN/GROUP BY logic directly into the Result view's definition. This lets the SQL Server query optimizer generate a single, efficient execution plan instead of re-running the source view's logic multiple times.

Example: #

If your original source view looks like this:

CREATE VIEW SourceView AS
SELECT a.id, b.category, SUM(a.value) AS total
FROM TableA a
JOIN TableB b ON a.b_id = b.id
GROUP BY a.id, b.category;

And your Result view was:

CREATE VIEW Result AS
SELECT category, SUM(total) AS grand_total
FROM SourceView
GROUP BY category;

Rewrite Result to skip the nested view entirely:

CREATE VIEW Result AS
SELECT b.category, SUM(SUM(a.value)) AS grand_total
FROM TableA a
JOIN TableB b ON a.b_id = b.id
GROUP BY b.category;

This cuts out the redundant GROUP BY step and lets the optimizer compute the final aggregation in one pass.

2. Use an Indexed View (Materialized View) for Reusable Results #

If your source view's data doesn't change constantly, an indexed view is a game-changer. It physically stores the source view's JOIN/GROUP BY results in the database, so queries against Result will pull pre-computed data instead of re-running the heavy logic every time.

Steps to Create an Indexed View: #

1. First, create the source view with SCHEMABINDING (required for indexed views) and include COUNT_BIG(*) (also mandatory for aggregation views):

   CREATE VIEW dbo.SourceView
   WITH SCHEMABINDING
   AS
   SELECT 
       a.id, 
       b.category, 
       SUM(a.value) AS total,
       COUNT_BIG(*) AS row_count -- Required for indexed views with aggregation
   FROM dbo.TableA a
   JOIN dbo.TableB b ON a.b_id = b.id
   GROUP BY a.id, b.category;
   

2. Add a unique clustered index to the view to materialize its data:

   CREATE UNIQUE CLUSTERED INDEX IX_SourceView ON dbo.SourceView(id, category);
   

3. Now your Result view can reference this indexed view, and performance will be drastically better because the heavy lifting is already done.

3. Optimize Underlying Indexes #

Even with the above fixes, make sure your base tables have proper indexes to speed up the JOIN and GROUP BY operations:

• Add indexes on the columns used for JOIN (e.g., TableA.b_id and TableB.id)
• Create covering indexes for the GROUP BY columns and aggregated values to avoid key lookups in the execution plan

Final Notes #

• Merging query logic is the simplest fix if your Result view's logic isn't overly complex.
• Indexed views are best for data that's read-heavy and updated infrequently—they do add overhead when the base tables are modified, so balance that against your performance needs.

内容的提问来源于stack exchange，提问作者Arun