Geek asks and answers

Indexing Views Using CTE

So, I just found out that SQL Server 2008 does not allow you to index the view using the CTE in the definition, but it does allow you to alterrequest the with schemabindingview to be added to the definition. Is there a good reason for this? Does it make sense for some reason I don't know? I got the impression that the main goal with schemabindingwas for you to index the view

new and improved with lots of query actions

;with x
as
(
    select   rx.pat_id
            ,rx.drug_class
            ,count(*) as counts
            from rx
            group by rx.pat_id,rx.drug_class

)
select   x.pat_id
        ,x.drug_class
        ,x.counts
        ,SUM(c.std_cost) as [Healthcare Costs]
    from x
    inner join claims as c
    on claims.pat_id=x.pat_id
    group by x.pat_id,x.drug_class,x.counts

And the code to create the index

create unique clustered index [TestIndexName] on [dbo].[MyView]
( pat_id asc, drug_class asc, counts asc)
+5
source share
1 answer

  • CTE. SCHEMABINDING. . , ( ): (a) WITH SCHEMABINDING (b), CTE. , , CTE.

  • , , CTE . , , , . , - , , . , , " ". , . , DML , ().

  • . , UDF, , (, UDF , , , DDL). , , , , , . , .

:

CREATE VIEW dbo.PatClassCounts
WITH SCHEMABINDING
AS
  SELECT pat_id, drug_class, 
      COUNT_BIG(*) AS counts
    FROM dbo.rx
    GROUP BY pat_id, drug_class;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.PatClassCounts(pat_id, drug_class);
GO
CREATE VIEW dbo.ClaimSums
WITH SCHEMABINDING
AS
  SELECT pat_id, 
    SUM(c.std_cost) AS [Healthcare Costs], 
    COUNT_BIG(*) AS counts
  FROM dbo.claims
  GROUP BY pat_id;
GO
CREATE UNIQUE CLUSTERED INDEX ON dbo.ClaimSums(pat_id);
GO

, , (, NOEXPAND , ):

CREATE VIEW dbo.OriginalViewName
WITH SCHEMABINDING
AS
    SELECT p.pat_id, p.drug_class, p.counts, c.[Healthcare Costs]
      FROM dbo.PatClassCounts AS p
      INNER JOIN dbo.ClaimSums AS c
      ON p.pat_id = c.pat_id;
GO

, - , , , .

, SUM(std_cost) ClaimSums pat_id + drug_class, pat_id. , claims drug_class, , . , , .

+11

More articles:


All Articles