Month: March 2024

Practical use of indexes

I treat non-clustered indexes as “shadow copies” of a table that are ordered differently (thank you MongoDB).

Just like in a spreadsheet, a database table is a grid of data. But unlike a spreadsheet you cannot change the order by clicking on a heading with a mouse.

So by creating differently ordered copies of a table you can improve the performance of specific queries.

Additionally, you can pare down these shadow copies to just the columns and rows your query needs. Like fish in a barrel 🙂

The down side of having multiple copies of the same table, is that they each individually need to be kept in sync whenever the data changes.

“Authors” quiz with answers

For Cliffton. I hope it helps.

/* AuthorsWithMoreThanOneBook.sql */


/* 1 create the table */
DROP TABLE IF EXISTS #authors;
CREATE TABLE #authors 
(auth_name VARCHAR(50),
 book_title VARCHAR(50));


/* 2 add some data */
INSERT INTO #authors VALUES
('Isaac Asimov', 'iRobot'),
('Ben Bova', 'The towers of Titan'),
('Ben Bova', 'A long way back');


/* 3 confirm the table contents */
SELECT * FROM #authors;


/* 4 answer using the HAVING clause */
SELECT auth_name
FROM #authors
GROUP BY auth_name
HAVING COUNT(*) > 1;


/* 5 answer using a WINDOW function */
SELECT auth_name
FROM
(SELECT auth_name,
    ROW_NUMBER() OVER (PARTITION BY auth_name 
    ORDER BY (SELECT book_title)) AS book_count
 FROM #authors
) AS qty
WHERE book_count > 1;