If I have a VIEW which has an Outer Join to another table, but the columns in the VIEW which reference that table are not used is there a way to a) cause that table to be ignored (i.e. optimised out of the query) or b) to rewrite the VIEW so that that happens?
In the example below my TEMP_VIEW2 uses a nested-select, in the SELECT clause, to get the associated data, and that IS optimised out of the query. BUT ... that's not going to work very well if I want the VIEW to present more than one column form a JOINed table.
I haven;t looked at whether CTEs and the like would solve the problem, I thought I would ask first in case anyone knew
Thanks 
USE tempdb
GO
CREATE TABLE TEMP_PRODUCT
(
TP_ID int,
TP_Category varchar(10),
TP_Price float
)
INSERT INTO TEMP_PRODUCT
SELECT 1, 'CAT1', 200.00 UNION ALL
SELECT 2, 'CAT1', 200.00 UNION ALL
SELECT 3, 'CAT1', 207.00 UNION ALL
SELECT 4, 'CAT2', 576.00 UNION ALL
SELECT 5, 'CAT2', 801.20 UNION ALL
SELECT 6, 'CAT3', 897.60 UNION ALL
SELECT 7, 'CAT3', 876.80 UNION ALL
SELECT 8, 'CAT4', 876.80 UNION ALL
SELECT 9, 'CAT4', 432.20 UNION ALL
SELECT 10, 'CAT4', 432.20
GO
CREATE TABLE TEMP_CATEGORY
(
TC_ID int,
TC_Category varchar(10),
TC_Descripion varchar(30)
)
INSERT INTO TEMP_CATEGORY
SELECT 1, 'CAT1', 'Category One' UNION ALL
SELECT 2, 'CAT2', 'Category Two' UNION ALL
SELECT 3, 'CAT3', 'Category Three' UNION ALL
SELECT 4, 'CAT4', 'Category Four'
GO
CREATE VIEW TEMP_VIEW
AS
SELECT TP_ID,
TP_Category,
TP_Price,
--
TC_ID,
-- TC_Category,
TC_Descripion
FROM TEMP_PRODUCT AS T1
LEFT OUTER JOIN TEMP_CATEGORY AS TC
ON TC_Category = TP_Category
GO
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
-- TEST RIG:
PRINT '#1 Reference both tables, VIEW#1'
SELECT TP_ID, TC_ID
FROM TEMP_VIEW
PRINT '#2 Reference primary table only, VIEW#1'
SELECT TP_ID
FROM TEMP_VIEW
PRINT '#3 Reference both tables, VIEW#2'
SELECT TP_ID, TC_ID
FROM TEMP_VIEW2
PRINT '#4 Reference primary table only, VIEW#2'
SELECT TP_ID
FROM TEMP_VIEW2
GO
DROP TABLE TEMP_PRODUCT
GO
DROP TABLE TEMP_CATEGORY
GO
DROP VIEW TEMP_VIEW
GO
DROP VIEW TEMP_VIEW2
GO
#1 Reference both tables
Table 'TEMP_CATEGORY'. Scan count 1, logical reads 10
Table 'TEMP_PRODUCT'. Scan count 1, logical reads 1
|--Nested Loops(Left Outer Join, WHERE:([tempdb].[dbo].[TEMP_CATEGORY].[TC_Category]
as [TC].[TC_Category]=[tempdb].[dbo].[TEMP_PRODUCT].[TP_Category] as [T1].[TP_Category]))
|--Table Scan(OBJECT:([tempdb].[dbo].[TEMP_PRODUCT] AS [T1]))
|--Table Scan(OBJECT:([tempdb].[dbo].[TEMP_CATEGORY] AS [TC]))
#2 Reference primary table only (same)
Table 'TEMP_CATEGORY'. Scan count 1, logical reads 10
Table 'TEMP_PRODUCT'. Scan count 1, logical reads 1
#3 Reference both tables (same)
Table 'TEMP_CATEGORY'. Scan count 10, logical reads 10
Table 'TEMP_PRODUCT'. Scan count 1, logical reads 1
#4 Reference primary table only
Table 'TEMP_PRODUCT'. Scan count 1, logical reads 1
|--Table Scan(OBJECT:([tempdb].[dbo].[TEMP_PRODUCT] AS [T1]))
My objection to a zero price is that even coupons have to be valued at the 10th of a penny for legal reasons.
) which made for weird math.
