CODEWITHSUNDEEP
sqlsql-serversql-server-2005stored-proceduresview
Beth
Beth

Reputation: 9617

SQL Server stored procedures contain SQL or call to view

Is it better practice to have references to views in your SQL Server stored procedures instead of the SQL code the view contains?

I see a lot of code like:

ALTER PROCEDURE [dbo].[Report_65PlusLivingAlone]

As
Begin

---- Select all 3 groups from base table
Select 
    * 
INTO 
    #Temphouseholds
FROM
(
select  b.ReportYearDescription as CensusYear
        , Case When a.AggregationLevel = 'Minnesota' then 'Statewide'
               Else a.AggregationLevel
          End as AggregationLevel
        , a.PopulationType
        , a.PopulationSize
        , Case When a.PopulationType in 
                    ('Non-Family Households, Living Alone, Age 65 +'
                        ,'Living alone, age 65 and older') then 'households_livingalone'
                When a.PopulationType = 'Householders age 65 and older' then 'householders_65plus'
                Else 'total_households'
          End as PopulationGroups
        , Case  When b.ReportYearType = 'C'
                Then 'Current'
                Else 'Projected'
          End as censusgroups   
from    PublicReport_DSD.dbo.HouseholdCensusCountyInformation a
        , PublicReport_DSD.dbo.ReportYear b
where   a.PopulationType in ('Non-Family Households, Living Alone, Age 65 +'
                             ,'Living alone, age 65 and older'
                             ,'Householders age 65 and older'
                             ,'Total'
                            )
        and cast(a.CensusYear as varchar) = b.ReportYearDescription
        and b.ReportID = 18
        and b.IsActive = 1
) as Temphouseholds

that I want to rewrite so the SQL is in a view. Does a view add overhead, or does, as I suspect, it give the server more information about what the sp is doing so it can optimize the sp better?

Thanks for the info,

-Beth

Upvotes: 3

Views: 250

Answers (1)

amelvin
amelvin

Reputation: 9061

The Sql Server optimizer will probably (t-sql is usually a case of probably) treat both queries the same - the view won't offer anything extra to the compiler unless there is additional t-sql code, hints or SARGs. Unless the view is indexed properly it may indeed perform worse.

Upvotes: 3

Related Questions