putting dbo explicitly in select staetement

I have witten a lot of stored procedures in my project where I did not put dbo before the user tables.My colleague told me that I have to put dbo for all statements other there could be a problem.

Any thought?,

Please assist.

Arkhan:

There are at least a couple of places in which the owner name prefix -- dbo -- is required including (1) naming of a scalar functions and (2) objects used with schemabinding. In addition, if your "shop standards" are to always designate object names with the owner name then you need to do so.

I can think of at least one situation in which I prefer that objects NOT be owned by dbo. This is at least somewhat controversial so take it with a grain of salt. In DTS "staging" tables I like to have a designated table owner so that that owner has the right to truncate the table without needing the database owner privilege. This does not figure to be relevant to your problem. It is good practice to include the owner name as part of your qualified name. One thing that worries about your question has to do with the practice of deployment of privileges.

I do not like it when I see scores of tables or other database objects that are owned by many different database users. When I see this type of stuff my knee-jerk reaction is that privileges are beging deployed to liberally. And I would guess that if you and your colleagues are seeing many problems from NOT including the owner name that you likely have this privilege problem.

Short answer: Include the dbo portion of the name.

Dave

|||

I've heard it said that not including the schema owner (usually DBO) on your object prefix can result in a "Compile lock" against your stored procedure while the client determines whether there is an object in existence for its own schema. In this situation, multiple users executing the same stored procedure would suffer from a queuing effect as each would place a compile lock on the procedure (or statement).

That being said, I've never been able to repro this is a testing environment and I have yet to see any white papers or KB articles that discuss this so I'd love it if somebody could chime in on this.

|||

Specifying the owner can help the system find the stored procedure faster. It also prevents issues if someone creates the same name procedure with a different owner (or schema in 2005) by mistake. At least if everyone uses two-part names, they will be less likely to make mistakes.

As for the compile locks, I have reproduced recompile locks in my stored procedures. I'm not 100% how I did it, but it seemed to be with temporary tables stored procedures. I added dbo in the front of each stored procedure and table name within the stored procedure and it elliminated most of the problems we had.

sql