Description

Discuss and demonstrate good techniques for writing Stored Procedures, Functions, and ensuring queries are SARGable whenever possible. Show how Dynamic SQL and Query Hints can help with performance. 

 

 

Expected Outcomes

Upon completing this Aeries conference session, attendees should be familiar with:

 

  • Tips
  • Querying JSON Data
  • Cursors vs Set-Based methods
  • Pivot without using PIVOT
  • Derived Tables
  • CTE - Common Table Expressions  - CTE example
  • How to analyze a query plan


Content:


Tips:

  1. Ctrl-Shift-Q (Designer!)
  2. Easy Insert statement (Copy & paste SELECT fields for INSERT)
  3. Using TOP (no ORDER BY) to see table structure
  4. Using LIKE effectively  


Querying JSON Data:


--Read JSON data from DistrictCache table
DECLARE @json NVARCHAR(MAX) = (select CONVERT(varchar(MAX),DistrictCache.OBJ)[gjson] from DistrictCache where nm = 'GoogleStudents_DB')
--print @json

--Locate a student by email address
declare @sem nvarchar(50) = 'ab816417@gedu-demo-aeries.com'

--Locate student by ID from previous query or set it here
declare @id as int = (select id from stu where sem = @sem)
--declare @id as int = 816417

-- Locate the EID (Google ID) from ESI table
declare @eid as nvarchar(50) = (select eid from esi where id = @id)

--Display ESI results (if any) for matching Student ID
select * from esi where id = @id

-- Read JSON data from data retrieved from DistrictCache
select id, em[PrimaryEmail],fn[GivenName],ln[FamilyName],OU[OrgUnitPath],suspended[Suspended],customtype[AeriesSIS_StudentID]
from
	(SELECT *
	FROM openjson(@json)
	with (Id nvarchar(100) '$.Id', 
		EM nvarchar(max) '$.PrimaryEmail',
		FN nvarchar(max) '$.Name.GivenName',
		LN nvarchar(max) '$.Name.FamilyName',
		OU nvarchar(max) '$.OrgUnitPath',
		[Suspended] nvarchar(max) '$.Suspended',
		[eids] nvarchar(max) '$.ExternalIds' as json
		) as jsonValues)[gc]
		outer apply 
			openjson(eids)
			WITH(
				CustomTypeName nvarchar(100) '$.CustomType',
				AeriesSIS_StudentID nvarchar(100) '$.Type',
				CustomType nvarchar(100) '$.Value'
)
where id = @eid and (CustomTypeName = 'AeriesSIS_StudentID') 
		or (CustomTypeName is null and CustomType is null and AeriesSIS_StudentID is null)



Cursors VS Set Based:

NOTE: Cursors behave much like programmatic loops and although the cursor is slower in the following example, it is good to keep in mind that a long running set based SQL method could risk lock escalation if it runs very long having escalated to a table lock on STU (that would be very bad). This example is very quick and does not have lock escalation issues.


A Cursor method to remove duplicate records in FRE that may exist for a few reasons...

/*   FRE De-duper
by: James Lancaster - Aeries Software c2018
Revised: Jason Doss 10/31/2022
*/

--First create a backup of FRE in case something goes wrong

declare @BackupFRE nvarchar(max) = '
SELECT *
INTO #FRE_BAK' + (select cast(replace(replace(replace(convert(varchar(20),current_timestamp,20),'-',''),':',''),' ','') as varchar(14))) + ' 
FROM FRE;'
exec (@BackupFRE)

--Grab all fields except SQ, DEL, and DTS to look for true duplicates

DECLARE @id INT,  @esd datetime, @eed datetime, @cd varchar(5), @src varchar(5);

DECLARE qrFRE CURSOR DYNAMIC
FOR
    SELECT DISTINCT --Use "DISTINCT" to prevent us from updating the same rows multiple times
           id,
           ESD,
           EED,
           CD,
           src
    FROM FRE
    WHERE del = 0

OPEN qrFRE;

FETCH NEXT FROM qrFRE INTO @id, @esd, @eed, @cd, @src;

WHILE @@fetch_status = 0
    BEGIN
     --This is where we del-tag...
        UPDATE FRE
          SET
              del = 1
        FROM FRE
        WHERE id = @id
              AND isnull(esd, GETDATE()) = isnull(@esd, GETDATE())
              AND cd = @cd
              AND isnull(eed, GETDATE()) = isnull(@eed, GETDATE())
              AND src = @src
              AND del = 0
              AND sq < --Del-tag all but latest SQ record
        (
            SELECT MAX(sq)
            FROM FRE
            WHERE id = @id
              AND isnull(esd, GETDATE()) = isnull(@esd, GETDATE())
              AND cd = @cd
              AND isnull(eed, GETDATE()) = isnull(@eed, GETDATE())
              AND src = @src
              AND del = 0
        );
        FETCH NEXT FROM qrFRE INTO @id, @esd, @eed, @cd, @src;
    END;

CLOSE qrFRE;

DEALLOCATE qrFRE;



Here's a set based method accomplishing about the same results. It delete tags all sequence numbers lower than the max(sq) unique data. It is broken up in to a select that first displays what will need deletion. The part that does the work is remarked out for safety.

NOTE: The remarked out region also contains dynamic SQL to backup the FRE table with a timestamp as FRE_BAKyyyymmddhhmmss

--Show FRE Duplicates (MAX SQ Omitted)
--Jared Kuszewski - Aeries Software
select * from fre 
where sq <> (select max(sq) from fre f where del = 0
and id = fre.id and FS = fre.fs and AF = fre.af and FC = fre.fc and src = fre.src 
and isnull(dt, '1/1/1900') = isnull(fre.dt, '1/1/1900')
and CD = fre.cd and EC = fre.ec 
and isnull(esd, '1/1/1900') = isnull(fre.esd, '1/1/1900')
and isnull(eed, '1/1/1900') = isnull(fre.eed, '1/1/1900')
and isnull(psd, '1/1/1900') = isnull(fre.psd, '1/1/1900')
and isnull(ped, '1/1/1900') = isnull(fre.ped, '1/1/1900')
)
and del = 0

/*
--First create a backup of FRE in case something goes wrong

--Backup FRE table into a temp table. Note this table will be lost once you close your SQL connection. 
declare @BackupFRE nvarchar(max) = '
SELECT *
INTO #FRE_BAK' + (select cast(replace(replace(replace(convert(varchar(20),current_timestamp,20),'-',''),':',''),' ','') as varchar(14))) + ' 
FROM FRE;'
exec (@BackupFRE)

--Del Tag older duplicate FRE records since the distinct MAX SQ records are likely correct
update fre set del = 1 
from fre inner join (
--Show FRE Duplicates (MAX SQ Omitted)
select * from fre 
where sq <> (select max(sq) from fre f where del = 0
and id = fre.id and FS = fre.fs and AF = fre.af and FC = fre.fc and src = fre.src 
and isnull(dt, '1/1/1900') = isnull(fre.dt, '1/1/1900')
and CD = fre.cd and EC = fre.ec 
and isnull(esd, '1/1/1900') = isnull(fre.esd, '1/1/1900')
and isnull(eed, '1/1/1900') = isnull(fre.eed, '1/1/1900')
and isnull(psd, '1/1/1900') = isnull(fre.psd, '1/1/1900')
and isnull(ped, '1/1/1900') = isnull(fre.ped, '1/1/1900')
)
and del = 0
) a
on fre.id = a.id and fre.sq = a.sq
*/


Pivot without using PIVOT:

Subqueries vs aggregate pivot technique

--subqueries
SELECT CD, NM, 
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -2) AS [PS],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -1) AS [TK],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 0) AS [K],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 1) AS [1],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 2) AS [2],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 3) AS [3],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 4) AS [4],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 5) AS [5],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 6) AS [6],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 7) AS [7],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 8) AS [8],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 9) AS [9],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 10) AS [10],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 11) AS [11],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 12) AS [12],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 13) AS [13],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 14) AS [14],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 15) AS [15],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 16) AS [16],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 17) AS [17],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 18) AS [18]
FROM LOC WHERE DEL = 0
ORDER BY CD

--aggregate style pivot is more efficient
select  max(cd) cd, max(nm) nm, 
isnull(max(case when gr = -2 then countgr else null end),0) [PS],
isnull(max(case when gr = -1 then countgr else null end),0) [TK],
isnull(max(case when gr = 0 then countgr else null end),0) [K],
isnull(max(case when gr = 1 then countgr else null end),0) [1],
isnull(max(case when gr = 2 then countgr else null end),0) [2],
isnull(max(case when gr = 3 then countgr else null end),0) [3],
isnull(max(case when gr = 4 then countgr else null end),0) [4],
isnull(max(case when gr = 5 then countgr else null end),0) [5],
isnull(max(case when gr = 6 then countgr else null end),0) [6],
isnull(max(case when gr = 7 then countgr else null end),0) [7],
isnull(max(case when gr = 8 then countgr else null end),0) [8],
isnull(max(case when gr = 9 then countgr else null end),0) [9],
isnull(max(case when gr = 10 then countgr else null end),0) [10],
isnull(max(case when gr = 11 then countgr else null end),0) [11],
isnull(max(case when gr = 12 then countgr else null end),0) [12],
isnull(max(case when gr = 13 then countgr else null end),0) [13],
isnull(max(case when gr = 14 then countgr else null end),0) [14],
isnull(max(case when gr = 15 then countgr else null end),0) [15],
isnull(max(case when gr = 16 then countgr else null end),0) [16],
isnull(max(case when gr = 17 then countgr else null end),0) [17],
isnull(max(case when gr = 18 then countgr else null end),0) [18]

from loc left join
(select sc, gr, count(gr) CountGR
from stu
where stu.del = 0 --and tg = ''
group by sc, gr
) Tallies on loc.cd = tallies.sc
where loc.del = 0
group by loc.cd
order by loc.cd


Running both these queries arriving at the same relative results and choosing to display the execution plan reveals the more efficient method in this challenge.


Then Kevin Quinn of Placentia-Yorba Linda Unified School District tops my quickly prepared (Jared) way of doing it after our Spring 2020 Aeries Con. It always makes me smile when others contribute...

--subqueries... Yuck!
SELECT CD, NM, 
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -2) AS [PS],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = -1) AS [TK],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 0) AS [K],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 1) AS [1],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 2) AS [2],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 3) AS [3],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 4) AS [4],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 5) AS [5],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 6) AS [6],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 7) AS [7],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 8) AS [8],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 9) AS [9],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 10) AS [10],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 11) AS [11],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 12) AS [12],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 13) AS [13],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 14) AS [14],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 15) AS [15],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 16) AS [16],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 17) AS [17],
(SELECT Count(*) FROM STU WHERE DEL = 0 AND SC = LOC.CD AND GR = 18) AS [18]
FROM LOC WHERE DEL = 0
ORDER BY CD

--aggregate style pivot is more efficient, **note the use of two separate aggregate functions.
select  max(cd) cd, max(nm) nm, 
  isnull(max(case when gr = -2 then countgr else null end),0) [PS],
  isnull(max(case when gr = -1 then countgr else null end),0) [TK],
  isnull(max(case when gr = 0 then countgr else null end),0) [K],
  isnull(max(case when gr = 1 then countgr else null end),0) [1],
  isnull(max(case when gr = 2 then countgr else null end),0) [2],
  isnull(max(case when gr = 3 then countgr else null end),0) [3],
  isnull(max(case when gr = 4 then countgr else null end),0) [4],
  isnull(max(case when gr = 5 then countgr else null end),0) [5],
  isnull(max(case when gr = 6 then countgr else null end),0) [6],
  isnull(max(case when gr = 7 then countgr else null end),0) [7],
  isnull(max(case when gr = 8 then countgr else null end),0) [8],
  isnull(max(case when gr = 9 then countgr else null end),0) [9],
  isnull(max(case when gr = 10 then countgr else null end),0) [10],
  isnull(max(case when gr = 11 then countgr else null end),0) [11],
  isnull(max(case when gr = 12 then countgr else null end),0) [12],
  isnull(max(case when gr = 13 then countgr else null end),0) [13],
  isnull(max(case when gr = 14 then countgr else null end),0) [14],
  isnull(max(case when gr = 15 then countgr else null end),0) [15],
  isnull(max(case when gr = 16 then countgr else null end),0) [16],
  isnull(max(case when gr = 17 then countgr else null end),0) [17],
  isnull(max(case when gr = 18 then countgr else null end),0) [18]
from loc left join
  (select sc, gr, count(gr) CountGR
  from stu
  where stu.del = 0 --and tg = ''
  group by sc, gr
  ) Tallies on loc.cd = tallies.sc
where loc.del = 0
group by loc.cd
order by loc.cd



--Summing a case statement in a derived table by Kevin Quinn for the win
SELECT  loc.cd, loc.nm, 
  isnull(tallies.[PS],0) [PS],
  isnull(tallies.[TK],0) [TK],
  isnull(tallies.[K],0) [K],
  isnull(tallies.[1],0) [1],
  isnull(tallies.[2],0) [2],
  isnull(tallies.[3],0) [3],
  isnull(tallies.[4],0) [4],
  isnull(tallies.[5],0) [5],
  isnull(tallies.[6],0) [6],
  isnull(tallies.[7],0) [7],
  isnull(tallies.[8],0) [8],
  isnull(tallies.[9],0) [9],
  isnull(tallies.[10],0) [10],
  isnull(tallies.[11],0) [11],
  isnull(tallies.[12],0) [12],
  isnull(tallies.[13],0) [13],
  isnull(tallies.[14],0) [14],
  isnull(tallies.[15],0) [15],
  isnull(tallies.[16],0) [16],
  isnull(tallies.[17],0) [17],
  isnull(tallies.[18],0) [18]
FROM loc
left join (select  sc,
    sum(case when gr = -2 then 1 else 0 end) [PS],
    sum(case when gr = -1 then 1 else 0 end) [TK],
    sum(case when gr = 0 then 1 else 0 end) [K],
    sum(case when gr = 1 then 1 else 0 end) [1],
    sum(case when gr = 2 then 1 else 0 end) [2],
    sum(case when gr = 3 then 1 else 0 end) [3],
    sum(case when gr = 4 then 1 else 0 end) [4],
    sum(case when gr = 5 then 1 else 0 end) [5],
    sum(case when gr = 6 then 1 else 0 end) [6],
    sum(case when gr = 7 then 1 else 0 end) [7],
    sum(case when gr = 8 then 1 else 0 end) [8],
    sum(case when gr = 9 then 1 else 0 end) [9],
    sum(case when gr = 10 then 1 else 0 end) [10],
    sum(case when gr = 11 then 1 else 0 end) [11],
    sum(case when gr = 12 then 1 else 0 end) [12],
    sum(case when gr = 13 then 1 else 0 end) [13],
    sum(case when gr = 14 then 1 else 0 end) [14],
    sum(case when gr = 15 then 1 else 0 end) [15],
    sum(case when gr = 16 then 1 else 0 end) [16],
    sum(case when gr = 17 then 1 else 0 end) [17],
    sum(case when gr = 18 then 1 else 0 end) [18]
    from stu 
    where del = 0 --and tg = ''
  group by sc) Tallies ON tallies.sc = loc.cd
where loc.del = 0




Derived Tables:

Derived Tables are subqueries within the from portion of an outer query. These can allow you to think of one query as a modular part of something greater. It can help you avoid relying on temp tables to hold outputs you would use in a later expression for your final result.


--THIS USE OF A DERIVED TABLE EXAMPLE FINDS ALL UNDELETED CONTACTS BY THE ORDER ENTERED AND INCLUDES ONLY THE FIRST IN THE OUTER QUERY 
select STU.SC, STU.SN, STU.ID, STU.LN, STU.FN, STU.GR, STU.SP, STU.TG,
C.FN, C.LN, C.NM, C.TL, C.EM
FROM
STU LEFT JOIN 

--DERIVED TABLE WRAPPED WITH PARENTHESIS IN JOIN
(
	SELECT PID, FN, LN, NM, TL, EM,
	ROW_NUMBER() OVER (PARTITION BY PID ORDER BY SQ) ROWNUM --WINDOWED FUNCTION TO NUMBER CONTACTS BY SQ
	FROM CON 
	WHERE DEL = 0
) C -- DERIVED TABLE ALIASED AS C IN THIS EXAMPLE

ON STU.ID = C.PID 
AND C.ROWNUM = 1

WHERE STU.DEL = 0 AND TG = ''



-- Inefficient method, causing nested loops by querying CON for each row.
select STU.SC, STU.SN, STU.ID, STU.LN, STU.FN, STU.GR, STU.SP, STU.TG,
C.FN, C.LN, C.NM, C.TL, C.EM
FROM
STU outer apply
--DERIVED TABLE WRAPPED WITH PARENTHESIS IN JOIN
( 
	SELECT top 1 PID, FN, LN, NM, TL, EM
	FROM CON 
	WHERE con.DEL = 0 AND STU.ID = CON.PID 
	order by sq 
)[C] -- DERIVED TABLE ALIASED AS C IN THIS EXAMPLE
WHERE STU.DEL = 0 AND TG = ''



This Pivot Example will be used in the following as two different testing administrations on the same one line.

SELECT        STU.ID, STU.FN, STU.LN, TST.ID AS TestID, TST.TA, TST.TD,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT0RS,
ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT0SS,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT0PL,

--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT1RS,
ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT1SS,
--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT1PL,

--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT2RS,
ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT2SS
--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT2PL,

--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT3RS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT3SS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT3PL,

--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT4RS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT4SS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT4PL,

--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT5RS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT5SS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT5PL,

--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT6RS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT6SS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT6PL


FROM            STU INNER JOIN
                         TST ON STU.ID = TST.PID
WHERE        (TST.ID = 'elpac')
group by STU.ID, STU.FN, STU.LN, TST.ID, TST.TA, TST.TD


So we take the example above and throw it in to two derived tables targeting different tests to try and contrast on one line. (NOTE: demo data is not as cool as your real data!)

select STU.SC, STU.ID, STU.FN, STU.LN, STU.SP, STU.TG, ELPAC.TestID, ELPAC.TA, ELPAC.TD, ELPAC.PT0SS, ELPAC.PT1SS, ELPAC.PT2SS,
CELDT.TestID, CELDT.TA, CELDT.TD, CELDT.PT0SS, CELDT.PT1SS, CELDT.PT2SS

FROM STU LEFT JOIN
(
SELECT        STU.ID, STU.FN, STU.LN, TST.ID AS TestID, TST.TA, TST.TD,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT0RS,
ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT0SS,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT0PL,

--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT1RS,
ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT1SS,
--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT1PL,

--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT2RS,
ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT2SS
--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT2PL,

--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT3RS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT3SS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT3PL,

--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT4RS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT4SS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT4PL,

--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT5RS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT5SS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT5PL,

--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT6RS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT6SS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT6PL


FROM            STU INNER JOIN
                         TST ON STU.ID = TST.PID
WHERE        (TST.ID = 'ELPAC')
AND TST.TD BETWEEN '8/1/2018' AND '8/1/2019'
group by STU.ID, STU.FN, STU.LN, TST.ID, TST.TA, TST.TD
) ELPAC
ON STU.ID = ELPAC.ID
LEFT JOIN
(
SELECT        STU.ID, STU.FN, STU.LN, TST.ID AS TestID, TST.TA, TST.TD,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT0RS,
ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT0SS,
--ISNULL(MAX(CASE WHEN TST.PT = 0 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT0PL,

--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT1RS,
ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT1SS,
--ISNULL(MAX(CASE WHEN TST.PT = 1 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT1PL,

--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT2RS,
ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT2SS
--ISNULL(MAX(CASE WHEN TST.PT = 2 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT2PL,

--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT3RS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT3SS,
--ISNULL(MAX(CASE WHEN TST.PT = 3 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT3PL,

--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT4RS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT4SS,
--ISNULL(MAX(CASE WHEN TST.PT = 4 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT4PL,

--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT5RS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT5SS,
--ISNULL(MAX(CASE WHEN TST.PT = 5 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT5PL,

--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.RS AS VARCHAR(20)) ELSE NULL END),'') AS PT6RS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.SS AS VARCHAR(20)) ELSE NULL END),'') AS PT6SS,
--ISNULL(MAX(CASE WHEN TST.PT = 6 THEN CAST(TST.PL AS VARCHAR(20)) ELSE NULL END),'') AS PT6PL


FROM            STU INNER JOIN
                         TST ON STU.ID = TST.PID
WHERE        (TST.ID = 'CELDT')
AND TST.TD BETWEEN '8/1/2018' AND '8/1/2019'
group by STU.ID, STU.FN, STU.LN, TST.ID, TST.TA, TST.TD
) CELDT
ON STU.ID = CELDT.ID
WHERE CELDT.ID IS NOT NULL OR ELPAC.ID IS NOT NULL




CTE (Common Table Expression):

A CTE is a self deallocating temp table. It has the one requirement that it must be used in the very next query. This could be deeply nested in a derived table if needed and also fulfill the immediate use requirement. A CTE also has the benefit of making recursive query possible.


--A CTE (COMMON TABLE EXPRESSION) IS A SELF DEALLOCATING TEMP TABLE THAT MUST BE USED IN THE NEXT QUERY, NO MATTER THE DEPTH.
--BEGIN A CTE WITH THE PHRASE "WITH". PRECEEDING THE WORD "WITH" WITH A SEMICOLON TO ASSURE IT IS FIRST IN A BATCH IS ALSO COMMON 
--IN THIS EXAMPLE WE SELECT ITEMS FROM STU FOR LATER USE. THIS CAN AVOID LOCK ESCALATION REACHING THE STU TABLE!

CREATE VIEW STUview
AS
WITH CTE 

(SC, SN, ID, LN, FN, GR, SP, TG)
AS
(SELECT SC, SN, ID, LN, FN, GR, SP, TG
FROM STU 
WHERE DEL = 0)

SELECT * FROM CTE
GO
--STUview was created!


SELECT STUview.*, CON.NM, CON.TL, CON.EM 
FROM STUview LEFT JOIN CON
ON STUview.ID = CON.PID AND CON.DEL = 0

--DROP VIEW STUview


Another fun Recursive CTE to help evaluate characters within a string

--What Characters are in a string? (Up to 32767 as set in MAXRECURSION below which is MSSQLSERVER's max it appears)
--Apostrophes within the string have to be depicted as a double apostrophe
--Unremark and use the where clause on line 22 to find the character position of specific characters alone
DECLARE @string NVARCHAR(MAX) = N'Whatever ╬™Ω☺☻☼♥♪''♫ 
'

DECLARE @SQL NVARCHAR(MAX) = N'
WITH CHARS (Position, TheCharacter, asASCII, UnicodeNum, ASCIInum) AS (SELECT 1 AS Position
,substring(N''' + replace(@string,'''','''''') + N''',1,1) TheCharacter
,cast(substring(N''' + replace(@string,'''','''''') + N''',1,1) as varchar(1)) asASCII
,unicode(substring(N''' + replace(@string,'''','''''') + N''',1,1)) UnicodeNum
,ASCII(substring(N''' + replace(@string,'''','''''') + N''',1,1)) ASCIInum
 UNION ALL 
 SELECT Position + 1 Position
,substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1) TheCharacter
,cast(substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1) as varchar(1)) asASCII
,unicode(substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1)) UnicodeNum
,ASCII(substring(N''' + replace(@string,'''','''''') + N''',Position + 1,1)) ASCIInum
  FROM CHARS WHERE Position + 1 <= ' + cast(datalength(cast(@string as varchar(max))) as varchar(20)) + ') 
SELECT * FROM CHARS
--where TheCharacter = N''e''
--COLLATE Latin1_General_CS_AS --Case Sensitive comparison
OPTION (MAXRECURSION 32767)
'
--print @SQL
exec (@SQL)


Stored procedure within a cursor:


Stored Procedure:

/* Analytics Grade Indicators where the qualifying value or level is less that the average (by student) */

DROP PROCEDURE IF EXISTS [Analytics_AllGradesIndicator];
GO

CREATE PROCEDURE [dbo].[Analytics_AllGradesIndicator] @ID int
AS
SELECT * FROM (
  SELECT 
     STU.ID, STU.LN, STU.FN, STU.GR, STU.GN, D.TI, D.LVL, D.QV
    ,AVG(D.LVL) OVER (PARTITION BY D.SCL, STU.GR ORDER BY D.SCL, STU.GR) AS 'AVG SCL/GR Qualifying Level' --Average qualifying level by grade
    ,AVG(D.QV)  OVER (PARTITION BY D.SCL, STU.GR ORDER BY D.SCL, STU.GR) AS 'AVG SCL/GR Qualifying Value' --Average qualifying value by grade
  FROM STU INNER JOIN (SELECT I.TI, DAQ.ID, DAQ.SQ, DAQ.AID, DAQ.D1, DAQ.D2, DAQ.SCL, DAQ.GR, DAQ.LVL
              ,CASE
                WHEN DAQ.QV LIKE 'A%' THEN 4
                WHEN DAQ.QV LIKE 'B%' THEN 3
                WHEN DAQ.QV LIKE 'C%' THEN 2
                WHEN DAQ.QV LIKE 'D%' THEN 1
                ELSE 0
              END QV
            FROM DAQ INNER JOIN (SELECT AID, TI, TY FROM DAI WHERE DEL = 0 AND TY = 'Grade') I 
              ON DAQ.AID = I.AID
            WHERE DAQ.ID IN (SELECT ID FROM STU WHERE DEL = 0 AND SC IN (SELECT CD FROM LOC WHERE E IN (2, 3)))
            ) D
    ON STU.ID = D.ID
  WHERE STU.ID = @ID) X
WHERE X.LVL < X.[AVG SCL/GR Qualifying Level] OR X.QV < X.[AVG SCL/GR Qualifying Value]

Cursor:

DECLARE @ID  NVARCHAR(15)  = ''
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @tbl TABLE (ID NVARCHAR(15), LN VARCHAR(60), FN VARCHAR(60), GR VARCHAR(2), GN VARCHAR(1), TI VARCHAR(60), LVL VARCHAR(5), QV VARCHAR(5), AVGLVL VARCHAR(5), AVGQL VARCHAR(5))

DECLARE varCursor CURSOR FOR
  SELECT ID FROM DAQ WHERE AID IN (SELECT AID FROM DAI WHERE TY = 'GRADE') AND ID LIKE '894%'

OPEN varCursor

FETCH NEXT FROM varCursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @SQL = 'EXEC [Analytics_AllGradesIndicator] @ID=' + @ID
  --print @sql

  INSERT INTO @TBL
  exec sp_sqlexec @sql

  FETCH NEXT FROM varCursor INTO @ID

END

SELECT * FROM @tbl

CLOSE varCursor
DEALLOCATE varCursor


Show indexes 

What are the indexes for that table?

exec sp_helpindex 'LOG'


Helpful Info


To see a practical example of Recursive CTE in action, see the Permissions Analysis Queries...

https://support.aeries.com/support/solutions/articles/14000059245-permissions-analysis-queries


To see an example of using a CTE in a view to avoid redundant evaluations of day of the week logic, see the Attendance Percentage by Period (APBP) view in this article...

https://support.aeries.com/support/solutions/articles/14000092446-aeries-query-expansion-through-sql-views


How to read SQL Server graphical query execution plans

https://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/