Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table between "ProductCategory" and "Sales".
If there was a nonclustered index on pr.ID and pr.CategoryID, the DBMS would do an Index-only-check, which has a very good performance. But, if I am right, a clustered index actually IS the entire table, ordered by the index columns. So, even if the clustered index had pr.ID and pr.CategoryID as it's index columns, the database would still have to load the entire table with all the heavy nvarchar(4000/max) stuff, only for two small columns.
-- get total sales amounts for product categories SELECT pc.ID, pc.Name, SUM(sl.Amount) AS TotalSalesAmount FROM ProductCategory pc INNER JOIN Product pr ON pc.ID=pr.CategoryID INNER JOIN Sales sl ON pr.ID=sl.SoldProductID GROUP BY pc.ID, pc.Name
with Product being a heavy table like this:
CREATE TABLE Product ( ID int not null PRIMARY KEY, -- clustered index 1st column CategoryID int FOREIGN KEY REFERENCES ProductCategory(ID), -- clustered idx 2nd. Name nvarchar(200) RecommendedPrice decimal(11,2), Creator nvarchar(200), SafetyReport nvarchar(4000), FutureDevelopmentsProposal nvarchar(4000), ExpectedSalesSurvey nvarchar(4000), CrystalBallVision nvarchar(4000), -- nonsense to represent a bloated table TarotCardsResult nvarchar(4000), Horoscope nvarchar(4000), FortuneTellerReport nvarchar(max) )
One remarkable thing I found out with a similar query on SQL Server 2008 R2: The query plan contained an index scan on a completely unrelated, nonclustered index, like one on the pr.RecommendedPrice column only.
My idea is that the unrelated, nonclustered index contains references to the clustered index rows (pr.ID, pr.CategoryID), and it's cheaper to get these from a nonclustered index scan, rather than from the actual clustered index.
Am I right in my assumptions?