Worst Practice - Not Qualifying Objects With The Owner

  • imhappy

    SSC Journeyman

    Points: 95

    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

    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

    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

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

     

     

  • chrisleonard

    SSCertifiable

    Points: 5000

    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

    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: 134280

    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


    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[/url] :alien: but most of the time this is me :hehe:

Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply