Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Why Object Qualification is important. Expand / Collapse
Author
Message
Posted Monday, March 10, 2008 1:50 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
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
Post #466923
Posted Monday, March 10, 2008 11:05 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
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/
Post #467165
Posted Monday, March 10, 2008 11:15 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Nice article and easy to understand.



"Keep Trying"
Post #467171
Posted Tuesday, March 11, 2008 7:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
Thank You all for the feedback. I appreciate it very much.


-Roy
Post #467352
Posted Saturday, March 15, 2008 7:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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 :D



Post #469944
Posted Wednesday, April 30, 2008 8:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 28, 2010 6:10 PM
Points: 5, Visits: 45
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
Post #492971
Posted Wednesday, April 30, 2008 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 10,342, Visits: 13,352
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #493015
Posted Wednesday, April 30, 2008 9:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 28, 2010 6:10 PM
Points: 5, Visits: 45
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
Post #493052
Posted Friday, January 8, 2010 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 18, 2010 4:43 PM
Points: 23, Visits: 57
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!
Post #844304
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse