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"
asked Nov 12, 2009 at 07:28 PM in Default
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.
answered Nov 12, 2009 at 07:31 PM
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.
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.
answered Nov 12, 2009 at 08:38 PM
Matt Whitfield ♦♦
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).
answered Nov 13, 2009 at 05:05 AM
Tom Staab ♦
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.
answered Nov 12, 2009 at 08:12 PM
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.
answered Nov 12, 2009 at 10:03 PM