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"


more ▼

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

avatar image

Sam 1
1 1 1 2

(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

avatar image

Raj More
1.8k 83 89 90

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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


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

avatar image

Tom Staab ♦
14.5k 7 15 21

  • 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

avatar image

191 2 1

(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

avatar image

Rob Farley
5.8k 16 22 28

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

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 12, 2009 at 07:28 PM

Seen: 9338 times

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

Copyright 2018 Redgate Software. Privacy Policy