How to optimise unused columns (and their tables) in a view

Transact-SQL
21

Thanks, I'll give that a try and report back.

I improved on

CREATE VIEW TEMP_VIEW2
AS
SELECT	TP_ID,
	TP_Category,
	TP_Price,
	--
	[TC_ID] =
	(
		SELECT	TC_ID
		FROM	TEMP_CATEGORY AS TC
		WHERE	TC_Category = TP_Category
	)
--	TC_Category,
--	TC_Descripion
FROM	TEMP_PRODUCT AS T1
GO

slightly.

Clearly if this needs more than one column, from a sub-table, then each column in the SELECT will re-query the same table, with an linear increase in I/O etc.

That can be improved with OUTER APPLY

SELECT	TP_ID,
	TP_Category,
	TP_Price,
	--
	TC_ID,
	TC_Category,
	TC_Descripion
FROM	TEMP_PRODUCT AS T1
	OUTER APPLY
	(
		SELECT	TC_ID, TC_Category, TC_Descripion
		FROM	TEMP_CATEGORY AS TC
		WHERE	TC_Category = TP_Category
	) AS TC
GO
'''
allowing any number of columns, from the sub-table, to be referenced with no additional I?O ... but the OUTER APPLY still needs I/O on a row-by-row basis (RBAR) which is not much use ...

All sub-tables, whether used or not, are included in the query ... but for a query with few rows matched it might be cost-effective.  Clearly disastrous if anyone accidentally queries a large table with many results.