Re: SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 17 Dec 2005 04:03:06 GMT
> How important is to specify the schame (dbo. in my case) in
> stored procedures? Will it really improve performance if I go
> and fix each object that is missing "dbo."?
It will improve performance but the 'slight performance improvement'
probably doesn't justify a significant effort to implement the
recommendation for thousands of instances. However, you should
schema-qualify objects for new development and perhaps as you perform
maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"serge" <sergea@xxxxxxxxxxxxxxxxx> wrote in message
news:gOrof.14720$yf7.259350@xxxxxxxxxxxxxxxxxxxxxx
> SQL BPA says the following:
>
> "One or more objects are referencing tables/views without
> specifying a schema! Performance and predictability of the
> application may be improved by specifying schema names."
>
> "When SQL Server looks up a table/view without a schema
> qualification, it first searches the default schema and then the
> 'dbo' schema. The default schema corresponds to the current
> user for ad-hoc batches, and corresponds to the schema of a
> stored procedure when inside one. In either case, SQL Server
> incurs an additional runtime cost to verify schema binding of
> unqualified objects. Applications are more maintainable and
> may observe a slight performance improvement if object
> references are schema qualified."
>
> How important is to specify the schame (dbo. in my case) in
> stored procedures? Will it really improve performance if I go
> and fix each object that is missing "dbo."?
>
> The problem is I have thousands and thousands of them
> with no schemas. Before I invest a lot of time fixing them
> I am trying to determine if it's really worth it or not?
>
> Thank you
>
>
.
- References:
- Prev by Date: Re: Multiple Apps on System DSN on Server from One PC
- Next by Date: Maximum UNION statements in a query
- Previous by thread: SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views
- Next by thread: Regarding table Order
- Index(es):
Relevant Pages
|
