March 10, 2008 at 1:50 pm
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
March 10, 2008 at 11:05 pm
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/
March 10, 2008 at 11:15 pm
Nice article and easy to understand.
"Keep Trying"
March 11, 2008 at 7:03 am
Thank You all for the feedback. I appreciate it very much.
-Roy
March 15, 2008 at 7:47 pm
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 😀
April 30, 2008 at 8:23 am
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
April 30, 2008 at 8:51 am
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
Consultant - Straight Path Solutions
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
April 30, 2008 at 9:16 am
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
January 8, 2010 at 7:30 am
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 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply