By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,965 Members | 787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,965 IT Pros & Developers. It's quick & easy.

SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views

P: n/a
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
Dec 16 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
> 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" <se****@nospam.ehmail.com> wrote in message
news:gO********************@weber.videotron.net... 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

Dec 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.