Why Object Qualification is important.

  • I agree with sreinster. If there are really no compelling reason for this, then they should be completely qualified. In your case 3 part qualification. Especially if your DBs are quite busy. Then every bit of performance you can gain counts.

    Also keep in mind that if by any chance it does recompile, there is no guarantee that it will take the right execution plan. It might take a bad execution plan. That is something that you really need to avoid. I have seen stored procs that take bad query plan increase the CPU use of the SQL Server by 10%.

    -Roy

  • This was a beautiful article. Thank you for both testing and quantifying what is normally assumed.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Nice article and easy to understand.

    "Keep Trying"

  • Thank You all for the feedback. I appreciate it very much.

    -Roy

  • Great article. Good to see some research into this oft-argued topic (particularly the sp_ prefix!).

    Following on from a few questions back, I'm still not sure if the answer was yes to multiple tables in the same database within different schemas, or if the answer was yes to same table within multiple databases. If the former, then do what works for you. If the latter then the next question would be...

    You have databases A & B. Are they completely separate - Does A access B's tables or vice-versa? If so then you need three part naming, otherwise stick with two part naming.

    Somewhere on this site there's a "worst practices" series of articles. One of them was having objects not owned by dbo. Direct flames in that article's forum if you don't agree 😀

  • Nice article but now I am wondering how to 'fix' my db!

    Are there any utilities out there that will bulk 2-part name my table references in all my stp's? -

    ie replace sometablename with dbo.sometablename

    Thanks

    Bill

  • Bill Marriott (4/30/2008)


    Nice article but now I am wondering how to 'fix' my db!

    Are there any utilities out there that will bulk 2-part name my table references in all my stp's? -

    ie replace sometablename with dbo.sometablename

    Thanks

    Bill

    Not to pimp the owners of this site, but RedGate does offer SQL Refactor which claims to be able to do this at least on an object at a time.

    You could always script all your sp's as alter and the run find and replace.

  • I will check Refactor.

    Find and replace is a really hard way to do it since I would have to 'find' each table name in my db - about 40.

    Bill

  • Just wanted to ask; These are the list of SQL Server editions that the original KB article applied to:

    Microsoft SQL Server 7.0 Standard Edition

    Microsoft SQL Server 2000 Standard Edition

    Microsoft SQL Server 2005 Standard Edition

    Microsoft SQL Server 2005 Developer Edition

    Microsoft SQL Server 2005 Enterprise Edition

    Microsoft SQL Server 2005 Express Edition

    Microsoft SQL Server 2005 Workgroup Edition

    Does this mean it don't apply to the SQL Server 2000 Enterprise Edition?

    I know it's a dumb question but it just popped up and its got me thinking!

Viewing 9 posts - 31 through 39 (of 39 total)

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