Table Elimination In a View

Question

Post reply

Table Elimination In a View

RonMexico

Hall of Fame

Points: 3474

January 14, 2021 at 3:42 pm

#3833414

I'm experiencing a situation similar to the overly simplified example provided here

CREATE TABLE dbo.Sales (
DateKey INT
,Quantity INT
)

INSERT INTO dbo.Sales
VALUES (
20210101
,5
)
,(
20210102
,15
)
,(
20210103
,4
)
,(
20210104
,8
)
,(
20210105
,23
)
,(
20210106
,47
)
,(
20210107
,31
)
,(
20210108
,8
)
,(
20210109
,10
)

CREATE TABLE dbo.Dates (
DateKey INT
,Day_of_Week VARCHAR(10)
)

INSERT INTO dbo.Dates
VALUES (
20210101
,'Friday'
)
,(
20210102
,'Saturday'
)
,(
20210103
,'Sunday'
)
,(
20210104
,'Monday'
)
,(
20210105
,'Tuesday'
)
,(
20210106
,'Wednesday'
)
,(
20210107
,'Thursday'
)
,(
20210108
,'Friday'
)
,(
20210109
,'Saturday'
)

CREATE VIEW dbo.SalesDayOfWeek
AS
SELECT b.Day_of_Week
,a.Quantity
FROM dbo.Sales a
INNER JOIN dbo.Dates b ON a.DateKey = b.DateKey

You can see the view simply joins the two tables together. Is there a way to force the view to not complete the join if the query is only pulling data from one of the tables? For example

select Quantity from dbo.SalesDayOfWeek

The execution plan shows a hash match between the Sales and Dates tables even though the query doesn't return a field in the Dates table. I hope this doesn't sound foolish given the real application has a lot more fields which makes the view more necessary.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

You specified an INNER JOIN. That means SQL must check to see if a matching row(s) exist in the other table because, if not, SQL can't return the row. YOU "told" SQL there had to be a match between the tables or you didn't want to see the row.

If you don't want to force a check on the other table, use a LEFT OUTER JOIN. Then SQL can completely ignore the table if you don't reference a column from it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

if there is a FK column on that column and the column is not null(not sure if this one required) then if no column is retrieved from the other table SQL can ignore it

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

ScottPletcher wrote:

You specified an INNER JOIN. That means SQL must check to see if a matching row(s) exist in the other table because, if not, SQL can't return the row. YOU "told" SQL there had to be a match between the tables or you didn't want to see the row.
If you don't want to force a check on the other table, use a LEFT OUTER JOIN. Then SQL can completely ignore the table if you don't reference a column from it.

Hmm.... SQL server will not ignore tables in left outer join just because they're not included in the output of the select, including through a view. I would imagine that's especially if the view itself actually does include columns from everything and only the select from the view is excluding certain tables.