Description

Discuss and demonstrate good techniques for writing complex queries and ensuring they are SARGable whenever possible. Show how Dynamic SQL and QueryHints can help with performance. 

 

Time Allotment

120 Minutes

 

Expected Outcomes

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

 

  • Synonyms
  • How to craft Dynamic SQL statements
  • Cursors vs Set-Based methods
  • Pivot without using PIVOT
  • Derived Tables
  • CTE - Common Table Expressions  - CTE example
  • How to analyze a query plan
  •  SARGable/non-SARGable queries
  •  How SQL indexes work 


Content:


Synonyms:

These can be used within a [AeriesCurrent] database as an example to point to all tables in the current year DST18000YourDistrict for instance. Scripts made against that database need never be changed to point to the current year if you simply drop the synonyms and recreate them. The following SQL can help with this task in a simplistic way...


 

use master;
create database AeriesCurrent;

use AeriesCurrent;
go

SELECT 'CREATE SYNONYM ['+TABLE_NAME+'] FOR DST18000AeriesDemo_ID.dbo.['+TABLE_NAME+']' FROM DST18000AeriesDemo_ID.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SELECT 'DROP SYNONYM IF EXISTS ['+TABLE_NAME+']' FROM DST18000AeriesDemo_ID.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'


Dynamic SQL:

We can also enhance this using dynamic SQL so we do not have to copy and paste the output and execute it. This can be suitable to drop in to a SQL job so the synonyms always include all tables. I also went a little further and tied to more than the current year. This can aid you in doing longitudinal queries. 


USE AeriesCurrent

IF EXISTS (SELECT 1 WHERE DB_NAME() = 'AERIESCURRENT') -- I wrapped this to protect against dropping synonyms on anything but a DB called AeriesCurrent
BEGIN
DECLARE @S NVARCHAR(MAX) = N''

SELECT @S += N'
DROP SYNONYM IF EXISTS ['+[NAME]+N']' 
FROM SYS.SYNONYMS ORDER BY [NAME]

SELECT @S += N'
CREATE SYNONYM ['+TABLE_NAME+N'] FOR DST18000AeriesDemo.dbo.['+TABLE_NAME+N']' 
FROM DST18000AeriesDemo.INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

SELECT @S += N'
CREATE SYNONYM ['+TABLE_NAME+N'_LastYear1] FOR DST17000AeriesDemo.dbo.['+TABLE_NAME+N']' 
FROM DST18000AeriesDemo.INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'
AND LEN(TABLE_NAME) < 118 --Beacause adding the ten characters '_LastYear1' cannot exceed max object name length of 128 characters. 

--print @S
--select @S
exec (@S)
end


Here's a simple example using the synonyms above now connecting to the current and prior year...


--What was their Total Weighted GPA in this year's DB compared to last year's DB
--This will only become interesting after they accomplish something this current year and their GPA may actually be different.
SELECT STU.SC, STU.ID, STU.FN, STU.LN, STU.TP, S1.SC, S1.TP
FROM STU LEFT JOIN STU_LastYear1 S1 ON STU.ID = S1.ID


 



Another Dynamic SQL Example...

--Select Table Counts even if there are other schemas owning them
create table dbo.#TableCounts (
SchemaName varchar(255) null, 
TableName varchar(255) null, 
Counts bigint null)

declare @SQL nvarchar(max) = N''

select @SQL += N'insert #TableCounts (SchemaName, TableName, Counts)
select ''' + TABLE_SCHEMA + N'''  SchemaName, ''' + TABLE_NAME + N''' TableName,count(*) Counts from [' + TABLE_SCHEMA + N'].[' + TABLE_NAME + N']
' from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE Table'
order by TABLE_NAME

--print @SQL
--select @SQL
exec (@SQL)

select * from #TableCounts

drop table #TableCounts


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
*/

--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

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.



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 = ''



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


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



Query Plan Analysis, SARGability, and understanding indexes coming soon to this article. - Check back.