Ms SQL Server - How to Create a View from a Cte

MS SQL Server - How to create a view from a CTE?

You cannot specify the MAXRECURSION option inside a view.

From http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/:

In order to make use of the MAXRECURSION option you need to first create your view without using the MAXRECURSION option:

USE AdventureWorks;
GO
CREATE VIEW vwCTE AS
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
-- Notice the MAXRECURSION option is removed
SELECT EmployeeID, ManagerID, Title
FROM cte
GO

Then when you query the view include the MAXRECURSION option:

USE AdventureWorks;
GO
SELECT  EmployeeID, ManagerID, Title
FROM    vwCTE
OPTION (MAXRECURSION 2);

See also AaskashM's answer at https://stackoverflow.com/a/7428903/195687

SQL - Creating View with multiple CTEs

To specify multiple steps CTE use ,

CREATE VIEW dbo.VW.SPAg   /* [dbo].[VW_SPAg] Probably you want this name*/
AS 
WITH today as 
(SELECT * FROM dbo.Work_Days
 WHERE [Date] = CAST(GETDATE() AS DATE)
), rd as
(SELECT [DATE] AS REP_DATE
 FROM dbo.Link_Days
 WHERE DAY ([DATE]) = 1
)
SELECT wm *,
gr.DATE_ORDINAL AS Goods_Rcvd_Ordinal
gt.DATE_ORDINAL AS Goods_Trnpt_Ordinal
today.DATE
FROM dbo.SPAg sg
INNER JOIN rd
  ON YEAR(sg.Client_Query)= YEAR(rd.REP_DATE)
LEFT JOIN dbo.Work_DAYS gr
  ON sg.Goods_Rcvd = gr.[DATE]
LEFT JOIN dbo.Work_DAYS gt
  ON sg.Goods_Trnpt = gt.[DATE];

Also I don't like view name dbo.VW.SPAg, do you have database called [dbo]? Or it should be [dbo].[VW_SPAg]?

MS SQL Server - How to create a view using hierarchical query

There's no hierarchical engine. There's the hierarchical type hierarchyid that can represent hierarchies and accelerate performance a lot, but from the comments it looks like you won't want to use it.

To query self-referencing hierarchies like this you can either join the table to itself up to a specific level, like you did, or use a Recursive Common Table Expression. A CTE is somewhat like defining a "view" in the query itself. An important difference is that the CTE can refer to itself, thus creating recursive CTEs.

This article explains how to use CTEs, using a hierarchical query as an example.

One part of the CTE selects the "root" rows. This is called the anchor,as this is where we start from.
The second, the recursive query, selects those related to the "previous" (the anchor's) results

In your case, the query would look something like :

With MyCTE
AS (
    --Anchor. Get the roots
    SELECT
        t.ID,
        NULL as ParentID
    FROM tablex 
    WHERE ParentID is null
    UNION ALL
    --Recursive. Get the direct descendants of the "previous" case
    SELECT 
        t.ID,
        t.ParentID
    FROM tablex t 
        INNER JOIN MyCTE m on m.ID=t.ParentID
    WHERE t.ParentID is NOT NULL
)
SELECT t.ID as CarID, t.ParentID
FROM MyCTE

To to get the level, we can add another column that starts with either 0 or 1, and increment it in the recursive query:

With MyCTE
AS (
    -- Anchor
    SELECT
        ID,
        ParentID,
        1 as Level.           -- Start with Level = 1
    FROM tablex 
    WHERE ParentID is null
    UNION ALL
    -- Recursive
    SELECT 
        t.ID,
        t.ParentID,
        m.Level+1 as Level.   -- Increment the level
    FROM tablex t 
        INNER JOIN MyCTE m on m.ID=t.ParentID
    WHERE t.ParentID is NOT NULL
)
SELECT 
    ID as CarID, 
    Level as CarLevel
FROM MyCTE

Using a recursive CTE in a view

Create a TVF:

CREATE FUNCTION my_function (
    @ProviderId int
)
RETURNS @ProviderTable TABLE 
(
    Id int NULL, 
    ProviderId int NULL,
    ConsumerId int NULL
)
AS 
BEGIN
    WITH cte AS (
        SELECT  Id, 
                ProviderId, 
                ConsumerId
        FROM T1
        WHERE ProviderId in (@ProviderId)
        UNION ALL
        SELECT  t.Id, 
                t.ProviderId, 
                t.ConsumerId
        FROM T1 t 
        INNER JOIN cte c 
            ON t.ProviderId = c.ConsumerId 
    )

    INSERT INTO @ProviderTable
    SELECT * FROM cte;

    RETURN;
END;

Than create a view:

CREATE VIEW my_view
AS 
SELECT m.*
FROM Providers p
CROSS APPLY my_function (p.ProviderId) m

After that you can SELECT from view whatever you need:

SELECT * 
FROM my_view 
WHERE ProviderId in (2,3,9)
OPTION (MAXRECURSION 0)

Error when creating a view with a CTE

You are missing the first AS after the CREATE VIEW:

CREATE VIEW [dbo].[VW_THIRDPARTY_SLA_REPORT_MONTHLY_GP_NONAGGREGATE] 
AS --- this is missing
  With partitioned
  AS 
  (
   Select 
      B.MSH7_DateTimeOfMessage,
      B.PID2x1_PatientIDExternal,
      B.PID3x1_PatientIDInternal,
      B.PID5x1_PatientName_FamilyName,
      B.PV3x2_AssignedPatientLocation_Room,
      A.OBR4x2_UniversalServiceID_Text,
      A.OBX3x2_ObservationIdentifier_Text,
      A.OBR24_DiagnosticServiceSectionID,
      A.OBR6_RequestDateTime,
      C.TestName,
      C.PriceBaseline,
      D.Contract,
      Row_NUMBER() OVER(Partition By [ORC3_FillerOrderNumber], [OBX3x2_ObservationIdentifier_Text] order by [ORC9_DateTimeOfTransaction]) as seq
   From [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_Detail] A
   LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_Header] B ON A.[DETAIL_ID] = B.[HEADER_ID]
   LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_View_TFCData] C ON A.[OBR24_DiagnosticServiceSectionID] + A.[OBX3x1_ObservationIdentifier_Identifier] = C.[KEY]
   LEFT OUTER JOIN [NWLHPathApp_DataWarehouse].[dbo].[PathologyHL7_LocationDetail] D ON B.[PV3x1_AssignedPatientLocation_PointOfCare] = D.[PracticeCode] 

   )
   Select *
   from partitioned  
   where seq =1 

SQL - CTE vs VIEW

Views can be indexed but CTE can't. So this is one important point.

CTE work excellent on tree hierarchyi.e. recursive

Also, consider views when dealing with complex queries. Views being a physical object on database (but does not store data physically) and can be used on multiple queries, thus provide flexibility and centralized approach. CTE, on the other hand are temporary and will be created when they are used; that's why they are called as inline view.

Update

According to your updated question, views will be the right choice. Dealing with 3.5 million rows in CTE will create extra overhead on TempDb which will eventually slow down SQL Server performance. Remember, CTE is a disposable view hence no statistics are stored and you can't create Indexes too. It is just like a sub query.

Indexing views with a CTE

1. You can't index a view with a CTE. Even though the view can have SCHEMABINDING. Think of it this way. In order to index a view, it must meet two conditions (and many others): (a) that it has been created WITH SCHEMABINDING and (b) that it does not contain a CTE. In order to schemabind a view, it does not need to meet the condition that it does not contain a CTE.

2. I'm not convinced there is a scenario where a view has a CTE and will benefit from being indexed. This is peripheral to your actual question, but my instinct is that you are trying to index this view to magically make it faster. An indexed view isn't necessarily going to be any faster than a query against the base tables - there are restrictions for a reason, and there are only particular use cases where they make sense. Please be careful to not just blindly index all of your views as a magic "go faster" button. Also remember that an indexed view requires maintenance. So it will increase the cost of any and all DML operations in your workload that affect the base table(s).

3. Schemabinding is not just for indexing views. It can also be used
   on things like UDFs to help persuade determinism, can be used on
   views and functions to prevent changes to the underlying schema, and
   in some cases it can improve performance (for example, when a UDF is
   not schema-bound, the optimizer may have to create a table spool to
   handle any underlying DDL changes). So please don't think that it is
   weird that you can schema-bind a view but you can't index it.
   Indexing a view requires it, but the relationship is not mutual.

---

For your specific scenario, I recommend this:

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

Now you can create a non-indexed view that just does a join between these two indexed views, and it will utilize the indexes (you may have to use NOEXPAND on a lower edition, not sure):

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

Now, this all assumes that it is worthwhile to pre-aggregate this information - if you run this query infrequently, but the data is modified a lot, it may be better to NOT create indexed views.

Also note that the SUM(std_cost) from the ClaimSums view will be the same for every pat_id + drug_class combination, since it's only aggregated to pat_id. I guess there might be a drug_class in the claims table that should be part of the join criteria too, but I'm not sure. If that is the case, I think this could be collapsed to a single indexed view.

Can i use DECLARE within a view -sqlserver or CTE?

You cannot declare a variable in a view. Why would you not create the view and then SELECT from the view using the string in the WHERE clause?

CREATE VIEW TESTVIEW AS
    SELECT TOP 10 -- Wouldn't recommend hardcoding this in a view
        U.ID
        ,X.NAME AS yyyy
        ,X.E AS zzzz
        ,X.NAME 
    FROM TABLE_A U
    JOIN TABLE_B X ON U.ID=X.ID
    WHERE U.STATUS='HELLO';

SELECT
   *
FROM TESTVIEW
WHERE NAME = 'ABC';

You could emulate it within the view with a CTE like:

CREATE VIEW TESTVIEW AS
    
WITH CTE AS (
   SELECT
       'ABC' as var
)

SELECT TOP 10 --Still wouldn't recommend hardcoding this in a view
        U.ID
        ,X.NAME AS yyyy
        ,X.E AS zzzz
        ,X.NAME 
    FROM TABLE_A U
    JOIN TABLE_B X ON U.ID=X.ID
    WHERE U.STATUS='HELLO'
    AND X.NAME = (SELECT var FROM cte);

SELECT * FROM TESTVIEW;

---

Related Topics