x

DBO database owner in select statement

do I need to include database owner "dbo" in select from a table in stored procedure where the owner of the stored procedure is the same? I know it works both ways but is it necessary?

For example : select * from dbo.table Is it important to write dbo.table instead of just table?

Note: the database has only one owner the default one "dbo"

Thanks

more ▼

asked Nov 12, 2009 at 07:28 PM in Default

Sam 1 gravatar image

Sam 1
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

You may have schemas in the future that have the same table name and this may cause code to break.

Also, SQL Server will resolve object names to the actual objects much faster if the object name is qualified so it is a good idea to do this for performance reasons.

more ▼

answered Nov 12, 2009 at 07:31 PM

Raj More gravatar image

Raj More
1.7k 80 82 84

+1 - good points :)
Nov 12, 2009 at 09:11 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
  • Is it necessary for it to work? That depends on the EXECUTE AS assignment to your stored procedure.

If it is EXECUTE AS Caller (which is the default, by the way) and a new schema gets added later, then someone executing that procedure with a different schema could get a different table, if a table of the same name exists in that other schema.

  • Is it necessary for maintaining good performance? Very often, yes.

The problem is that cache lookups fail first time if the object is not schema-qualified. If you get into the habit of referring to objects without their owner qualification, then you will start executing procedures that way etc. And this is when the cache lookup becomes a problem. When the first cache lookup fails, SQL Server acquires an exclusive compile lock on the procedure and does a secondary cache lookup. This is unnecessary work for it, and can even cause blocking on some systems. There is a good bit from an MSDN article which explains it here, and an excellent article by Roy Ernest here.

  • Is it a good habit to get into? Absolutely.
more ▼

answered Nov 12, 2009 at 08:38 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

I agree with the general consensus that including the schema in your queries is better for performance. I also believe it is better for code readability. But there is a situation where it is actually required.

WITH SCHEMABINDING

The most important example of schema binding in my own experience is with indexed views. If you are not familiar with schema binding, there are some good answers here. For more on that or on the differences and advantages of indexed views vs. traditional ones, you can find good information in BOL. In short, an indexed view is more like a table (i.e. data is stored in the database) rather than a query. But indexed views having many requirements, and one of them is that all objects in the query must be use two-part names (schema.object).

I realize you asked about a query inside a stored procedure, but I believe consistency is always a good thing (and can actually improve performance due to better cache reusability--see Matt's answer). Since indexed views already require inclusion of the schema name, why not include it all of the time? Then, if you later decide to convert your query into an indexed view, it will be a simpler process (assuming the query passes the other 999 restrictions for indexed views).

more ▼

answered Nov 13, 2009 at 05:05 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

+1 - definitely a good point
Nov 13, 2009 at 05:49 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

If you're not using a super-heavy used database, you don't really have to worry about it.

Your SQL will assume the dbo by default, and if you add other schemas later those objects can still reference objects using the dbo notation.

more ▼

answered Nov 12, 2009 at 08:12 PM

jjerome gravatar image

jjerome
191

(comments are locked)
10|1200 characters needed characters left

Whether it will continue to work or not depends on the default schema of the user executing the stored procedure. But for performance reasons (and overall correctness), you should always specify the schema of every object you ever refer to. If you don't, the system needs to take a moment to look to see what your default schema is, then check that the object's in there. If it's not, it has to go to the dbo schema to see if it's in there... and all this is checking that is not required if you've specified the schema in the first place.

Also, remember that users don't own objects any more. They only own the schemas that contain objects.

more ▼

answered Nov 12, 2009 at 10:03 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

It would continue to work if the procedure was marked as EXECUTE AS OWNER, but still, all good points.
Nov 12, 2009 at 10:15 PM Matt Whitfield ♦♦
Sure - but you're still assuming that the tables are in the default schema of the stored procedure owner. Suppose a stored procedure in the schema Reports tries to access a table in the schema Reports, but the Reports schema is owned by dbo, who has a default schema of dbo...
Nov 13, 2009 at 04:09 AM Rob Farley
Not really - EXECUTE AS OWNER says that it would use the default schema of the owner of the object, not the default schema of the owner of the schema that the object belongs to... (try saying that 5 times fast! :) )
Nov 13, 2009 at 05:52 AM Matt Whitfield ♦♦
But that default schema could still be different to the one the object is in.
Nov 13, 2009 at 09:15 PM Rob Farley
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x241
x5

asked: Nov 12, 2009 at 07:28 PM

Seen: 6974 times

Last Updated: Nov 12, 2009 at 07:28 PM