February 14, 2008 at 4:36 am
Reading through the db owner topic I found that many prefer all objects being only qualified by dbo schema.
can anybody tell me a reason for that ?
i moved from other databases to sql server. I am used to strictly define schema names and always using fully qualified objects in sql and applications.
now I find applications not using schemas at all but only dbo. then they ended up with 15 or so databases with all the same table names and content, I mean only about 10 tables per database! using 15 schemas would save a lot of hassle in administration, but what has grown historically would not change that easily. starting with one they just added databases for more clients instead of using schemas.
I can't imagine that being best practice for sql server.
please tell me what I am missing here that all sql server pros prefer having everything in the dbo schema.
that comes to another topic: is there any option to set a schema qualifier in applications or connections which will be used ? that would make life easier too for all those who are too lazy to type fully qualified names.
February 14, 2008 at 5:26 am
You can set a default schema for a login.
As far as using schemas - it is a split topic. I like schemas and enforce my consultants to not put any object in the dbo schema. This does mean that you have to type out the schema name, but typing the dbo is good practice anyway.
I have always thought that to be a really good dba, you need to be at least a little compulsive. I like to organize things into groups that can be managed more easily and I like to ensure garbage is cleaned up. Using schemas lets me organize objects that should be bundled together and also has a tendency to have trash end up in the dbo schema (if someone creates a table for some "temporary purpose") so it is easily found.
February 14, 2008 at 5:40 am
Schema qualification..Thats a large topic. But this is what I can say, When executing any stored proc, if you are not schema qualifiying, then you will definetly have a performance hit. Basically it is because it is not the owner who is going to execute the object. It will be a normal user. That would mean that it has to do couple of additional look ups before able to find the right cached Plan. This can also cause unwanted recompiles.
Also while creating objects, it is good practice to specify the schema to which the object should be created. This reduces the chance of having orphan objects.
-Roy
February 14, 2008 at 4:39 pm
Generally i go with the dbo schema. so that i can get hold on things easily and know what is happening in my server and moreover implementation in the production server is easy if it happens to be dbo schema.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 14, 2008 at 9:47 pm
fully qualifying the owner "dbo" is good practice to lower IO. The same way specifying http:// or https:// in front of a web address makes the call faster because it does not need to look it up.
It does not need to be "dbo" it could be "MySqlOwner" or anything else, as well as one database can have multiple owners of objects. Which is all the more reason to have the practice of fully qualifying the object owner.
here is a pretty good article about sql server best practices
http://www.sql-server-performance.com/articles/dev/sql_best_practices_p1.aspx
Hope that helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply