SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why Object Qualification is important.


Why Object Qualification is important.

Author
Message
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 6891
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
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2286 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/
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6371 Visits: 1865
Nice article and easy to understand.

"Keep Trying"
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8237 Visits: 6891
Thank You all for the feedback. I appreciate it very much.

-Roy
Ian Yates
Ian Yates
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4398 Visits: 445
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 BigGrin



Bill Marriott-486259
Bill Marriott-486259
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 47
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42221 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Bill Marriott-486259
Bill Marriott-486259
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 47
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
badkow
badkow
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search