question

Sam 1 avatar image
Sam 1 asked

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

databaseownership
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Raj More avatar image
Raj More answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jjerome avatar image
jjerome answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
  • 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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - definitely a good point
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.