Single CTE with multiple SELECTs, CTE created twice in SQL Server-CodePudding

Requirement is to join(pair) two tables on some fields and then show pair wise results in order. Pairing is being done in CTE. A pair is assigned a GUID which is then used to show each pair in order. It works as expected for Oracle (11.2) but for SQL Server (2017), it seems the CTE is created twice because each row has its own GUID.

How can i solve it using CTE? Why SQL Server is behaving like this?

SQL Server http://sqlfiddle.com/#!18/21c167/1

Oracle http://sqlfiddle.com/#!4/c49dbb/2 (DBMS_RANDOM.RANDOM has been used instead of SYS_GUID() because SYS_GUID() was returning same value for all rows on sqlfiddle.com while working fine on my dev machine)

CREATE TABLE VEHICLE (CODE NUMERIC(3,0), [TYPE] VARCHAR(50), DRIVER_CATEGORY VARCHAR(2));
CREATE TABLE DRIVER (CODE NUMERIC(9,0), NAME VARCHAR(200), CATEGORY VARCHAR(2));
 
INSERT INTO VEHICLE VALUES (1, 'LIMOUSINE', 'A');
INSERT INTO VEHICLE VALUES (2, 'TRUCK', 'B');
INSERT INTO VEHICLE VALUES (3, 'SUV', 'C');
INSERT INTO VEHICLE VALUES (4, 'SEDAN', 'C');
INSERT INTO VEHICLE VALUES (5, 'SPORTS CAR', 'D');
  
INSERT INTO DRIVER VALUES (1, 'James', 'A');
INSERT INTO DRIVER VALUES (2, 'Robert', 'B');
INSERT INTO DRIVER VALUES (3, 'John', 'C');
INSERT INTO DRIVER VALUES (4, 'Jennifer', 'C');
INSERT INTO DRIVER VALUES (5, 'Patricia', 'D');
INSERT INTO DRIVER VALUES (6, 'Susan', 'A');     
INSERT INTO DRIVER VALUES (7, 'Lisa', 'B');     

  ;WITH MATCHPAIRS AS 
  ( 
      SELECT V.CODE VEHICLE_CODE, D.CODE DRIVER_CODE, V.TYPE TYPE, V.DRIVER_CATEGORY, D.NAME, D.CATEGORY, NewID() PAIRID
      FROM VEHICLE V INNER JOIN DRIVER D ON V.DRIVER_CATEGORY = D.CATEGORY
  )
    SELECT p.VEHICLE_CODE, p.DRIVER_CODE, P.PAIRID, V.TYPE TYPE_NAME, 0 RECORD_ORDER  
    FROM VEHICLE V INNER JOIN MATCHPAIRS P ON V.CODE = P.VEHICLE_CODE 
    UNION ALL  
    SELECT p.VEHICLE_CODE, p.DRIVER_CODE, P.PAIRID, D.NAME TYPE_NAME, 1 RECORD_ORDER  
    FROM DRIVER D INNER JOIN MATCHPAIRS P ON D.CODE = P.DRIVER_CODE
    ORDER BY PAIRID, RECORD_ORDER

CodePudding user response：

CTEs are not cached, they are recalculated for every reference.

In any case, NEWID does weird stuff sometimes, when not used on the outermost SELECT, so best to use a temp table. CROSS APPLY or CROSS JOIN will also not work sometimes with NEWID.

Alternatively, use a different uniquifier instead. For example

;WITH MATCHPAIRS AS (
    SELECT
      V.CODE VEHICLE_CODE,
      D.CODE DRIVER_CODE,
      V.TYPE TYPE,
      V.DRIVER_CATEGORY,
      D.NAME,
      D.CATEGORY,
      ROW_NUMBER() OVER (ORDER BY v.CODE, D.CODE) PAIRID
    FROM VEHICLE V
    INNER JOIN DRIVER D ON V.DRIVER_CATEGORY = D.CATEGORY
)
SELECT
  p.VEHICLE_CODE,
  p.DRIVER_CODE,
  P.PAIRID,
  V1.TYPE_NAME,
  v1.RECORD_ORDER  
FROM MATCHPAIRS P
CROSS APPLY (VALUES
  (p.TYPE, 0),
  (p.NAME, 1)
) V1(TYPE_NAME, RECORD_ORDER)
ORDER BY
  PAIRID,
  RECORD_ORDER;

Note the use of CROSS APPLY (VALUES to unpivot, which saves reading the CTE twice.

SQL Fiddle

CodePudding user response：

Easiest solution is to use a temp table instead of a CTE. If needed, you can wrap the code inside a stored procedure.