Worst Practice - Not Qualifying Objects With The Owner

• imhappy

  SSC Enthusiast

  Points: 105

  More actions

  March 11, 2004 at 8:06 pm

  #498429

  Hey, I am just a newbie.  I also start to use stored procedures and I can't find out what actually cons and pros of using qualified object name except of performance.   It's not my laziness on typing 'dbo.'.  Just my boss asked me any cons and pros of NOT using qualified object name. 

  In my point of view, with qualified object name can let me know what the object is actually used, but I need to amend code before releasing to production environment. A newbie is just a newbie.....

• Denny Figuerres

  Mr or Mrs. 500

  Points: 550

  More actions

  March 10, 2005 at 2:13 am

  #546306

  I see this article has a lot of folks with many of the same comments....

   

  as one person pointed out the !!WORST!!  thing is when some bozo codes with servername and or dbname when you try to setup a new database you need them to not be hardcoded.

   

  as for the owner prefix I will echo the question: 

  WHY ?

  in my experience only one set of tables is allowed in a database and if any other user or admin creates a table with the same name as a production database table they would be in deep hot smelly stuff with me! 

  can anyone provide a compelling case for having multiple database objects with the same base name and different owner names?

  I can think of possibly one case and very minimal at best:  different view and sp versions based on login user id.

  other than that I see the use of more than one object with the same base name and a different owner name as a possible way to confuse users and lead to database problems.

   

• SWELL

  SSC Enthusiast

  Points: 114

  More actions

  November 17, 2005 at 8:37 am

  #604441

  Excellent article but I note the author doesn't follow his own advice in the example code in some of his other articles eg SQL Injection etc !!

• YS Goh

  Grasshopper

  Points: 10

  More actions

  December 1, 2005 at 2:55 am

  #606969

  Instead of user id, can we use the user-defined role id as the table owner? any impact?

   

   

• chrisleonard

  SSCertifiable

  Points: 5030

  More actions

  December 1, 2005 at 7:16 am

  #607034

  Yes, a role can own objects just like a user. This is actually an interesting option. The key to this thread is to specify the owner as part of the object name, whether that is a user or role.

  Cheers,

  Chris

• Vivek Kadam-384996

  SSC Veteran

  Points: 201

  More actions

  December 12, 2006 at 12:22 am

  #677407

  Can I use the orders table like the following:

  databaseName..orders     ????

  if yes then where the SqlServer will search for the object first?

   

  Regards,

  Vivek

• Johan Bijnens

  SSC Guru

  Points: 135368

  More actions

  December 13, 2006 at 1:14 am

  #677713

  yes.

  prior to sql2005 sqlserver would search for ...

  databaseName.YOURLOGINUSER.orders    

  then for databaseName.DBO.orders    

  with sql2005 it will start searching for ...

  databaseName.YOURDEFAULTSCHEMA.orders    

  then for databaseName.DBO.orders    

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me