Delete Qualified Rows From Multiple Tables: A SQL Puzzle

Delete qualified rows from multiple tables with the fewest logical reads: that is the whole puzzle. The rules are short, the data is small and the answer is not obvious. The setup script and a starting query are below.

The Setup: Three Tables

A school keeps three tables. Student holds the student names. Class holds the class names. StudentClass links them, one row for each enrollment. None of the tables has a key or an index. They are heaps, and each one fits on a single page.

The first script creates a database named StudentClassDemo with the tables and a procedure that puts the data back. Call that procedure before every try, so each attempt starts from the same pages. The procedure uses CREATE OR ALTER, which needs SQL Server 2016 SP1 or later.

IF DB_ID(N'StudentClassDemo') IS NULL CREATE DATABASE StudentClassDemo;
GO
USE StudentClassDemo;
GO
DROP TABLE IF EXISTS dbo.StudentClass, dbo.Class, dbo.Student;
CREATE TABLE dbo.Student (ID int, StudentName varchar(100));
CREATE TABLE dbo.Class (ID int, ClassName varchar(100));
CREATE TABLE dbo.StudentClass (ID int, ClassID int, StudentID int);
GO
CREATE OR ALTER PROCEDURE dbo.ResetPuzzle
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE dbo.StudentClass;
    TRUNCATE TABLE dbo.Class;
    TRUNCATE TABLE dbo.Student;
    INSERT INTO dbo.Student (ID, StudentName) VALUES (1, 'Mark'), (2, 'John'), (3, 'Thomas');
    INSERT INTO dbo.Class (ID, ClassName) VALUES (1, 'Maths'), (2, 'English'), (3, 'Science');
    INSERT INTO dbo.StudentClass (ID, ClassID, StudentID) VALUES (1, 1, 1), (2, 2, 2), (3, 1, 3), (4, 2, 1), (5, 2, 3), (6, 3, 1);
END;
GO
EXEC dbo.ResetPuzzle;

This query lists every student with the classes they take.

SELECT DISTINCT s.StudentName, c.ClassName
FROM dbo.StudentClass AS sc
INNER JOIN dbo.Class AS c ON c.ID = sc.ClassID
INNER JOIN dbo.Student AS s ON s.ID = sc.StudentID
ORDER BY s.StudentName, c.ClassName;

What a Logical Read Is

A logical read is one request for one data page from memory. SQL Server counts it every time it touches a page, whether the page was cached or not. Time depends on the machine, the load and the cache. Logical reads do not. For the same data and the same plan, they give the same number on any server. That is why this puzzle scores reads and not seconds.

The Two Rules

The school cancels the English class. Two cases follow for the students who took it.

• One class only. A student whose only class was English leaves the school list. Delete the student and the enrollment.
• More than one class. A student who took English and another class stays. Delete only the English enrollment.

John took English alone. Mark and Thomas took English and other classes. The correct result is easy to read. John is gone from Student. The three English rows are gone from StudentClass. Mark and Thomas keep their other classes.

A student has at most one row for each class. One more rule follows from the first two. A student who never took the cancelled class is not touched. A student with no enrollment at all stays in the table, because the cancellation did not affect that student.

The Starting Script

The script below follows the two rules, with one delete for each table. It works. SET STATISTICS IO ON prints how many pages each table gave up.

SET STATISTICS IO ON;

DELETE s
FROM dbo.Student AS s
INNER JOIN dbo.StudentClass AS sc ON s.ID = sc.StudentID
INNER JOIN dbo.Class AS c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English'
  AND sc.StudentID IN (SELECT sc2.StudentID FROM dbo.StudentClass AS sc2 GROUP BY sc2.StudentID HAVING COUNT(*) = 1);

DELETE sc
FROM dbo.StudentClass AS sc
INNER JOIN dbo.Class AS c ON c.ID = sc.ClassID
WHERE c.ClassName = 'English';

SET STATISTICS IO OFF;

The total is 19 logical reads. The first delete scans StudentClass seven times and reads nine pages. The second delete scans Class once and still reads six pages. A scan and a read are not the same count. Score the puzzle by logical reads, not by scan count.

Check the result. These two queries must show the tables as the rules describe them.

SELECT ID, StudentName FROM dbo.Student ORDER BY ID;

SELECT ID, ClassID, StudentID FROM dbo.StudentClass ORDER BY ID;

Your Task

Write a version that can delete qualified rows with the same result and fewer total logical reads. Rewrite the deletes, combine them, use a common table expression, use a temporary table or create an index. Anything goes. Count the reads of every statement you run, including the cost of an index you create.

The answer must be correct for any class name, not only English. Replace English with Maths, and your version must still apply both rules. A class name that does not exist must delete nothing. For Maths, no student is deleted, because Mark and Thomas have other classes and nobody took Maths alone. The StudentClass table then keeps the rows with IDs 2, 4, 5 and 6.

Run EXEC dbo.ResetPuzzle; before each try. Add up the logical reads from the Messages tab, and compare the two tables with the ones above. Post your version in the comments.

The answer is in Fewest Logical Reads: The Answer to the Delete Qualified Rows Puzzle. A flawed answer is covered in Delete the Wrong Row: A Cheap Answer That Passes the Test. Try the puzzle before you read either one.

A Hint About Where the Reads Go

If you delete the English enrollments first, you lose the evidence of who was alone in English. A version that starts there needs another way to find those students. The order of the work matters, and so does the number of times you look at each table. Every extra look costs a read. Start by asking how many times your version touches StudentClass. A good answer to delete qualified rows touches each table as few times as it can.

Be careful with shortcuts. A delete that passes on this data can still be wrong on other data. Test three cases. A student has no classes. A class has no students. A class name does not exist. The rules above say the right result for each.

When you finish with the puzzle, run the cleanup script.

USE master;
GO
IF DB_ID(N'StudentClassDemo') IS NOT NULL
BEGIN
    ALTER DATABASE StudentClassDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE StudentClassDemo;
END;

A puzzle is not a trick, it is a question with a cheaper answer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.