Table Elimination In a View

  • 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.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

  • 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.

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

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