Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Best Practices/Standards - to include or not...
Add to briefcase
30 posts, Page 1 of 3
123»»»

Best Practices/Standards - to include or not to include database owner for table names Expand / Collapse
Rate Topic
Display Mode
Topic Options
Should you include database owner when accessing tables in your t-sql code?
Poll ResultsVotes
Include database owner
  		85.19%
23
Don't include database owner
  		14.81%
4
Member Votes: 26, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
nataliehinnen
Posted Wednesday, April 29, 2009 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 06, 2013 8:33 PM
Points: 14, Visits: 94
What's your opinion?

Should you write code like this:
SELECT blah1, blah2, blah3
FROM dbo.tablename

Or like this:
SELECT blah1, blah2, blah3
FROM tablename

This is, of course when your accessing tables in the current database you are in.

Best practice is to be consistent.
This is definately an opinionated poll, and I would like to hear your thoughts.
Post #706913
GSquared
Posted Wednesday, April 29, 2009 9:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571
Including it improves performance. The only reason to not include it is if different users access different tables, and you want that managed by the database. Doing so reduces performance, and there are other ways to achieve the same thing that are more performant.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #706935
Roy Ernest
Posted Wednesday, April 29, 2009 9:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 3,280, Visits: 6,623
Using the Object qualifier. Not just in Select statements but also when you are executing a Stored proc as well.

I did write an article regarding this.

Calling attention to myself....

-Roy
Post #706950
nataliehinnen
Posted Wednesday, April 29, 2009 10:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 06, 2013 8:33 PM
Points: 14, Visits: 94
Thanks for the replies so far.. I actually agree with including the db owner, but I work with a bunch of geeks who are afraid to "hard code" anything... so I need some ammo.

If anyone else has an opinion, please vote.
Post #707019
Roy Ernest
Posted Wednesday, April 29, 2009 10:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 3,280, Visits: 6,623
Go through the article and set up a test db and show them the performance issues due to this. Or give them the article.

-Roy
Post #707023
WayneS
Posted Wednesday, April 29, 2009 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
Hey Roy! Great article.

What happens if a stored procedure is called with the schema qualifier, BUT inside the stored procedure calls to tables aren't schema qualified?

ie.
CREATE PROC dbo.TEST1 As
select * from Table1 INNER JOIN TABLE2 on Table1.Column1 = Table2.Column1
GO

execute dbo.TEST1

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #707137
GSquared
Posted Wednesday, April 29, 2009 12:43 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571
It has to go through a schema/compilation lock every time it's run in that case, which means it can only be run by one connection at a time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #707138
Roy Ernest
Posted Wednesday, April 29, 2009 12:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:27 AM
Points: 3,280, Visits: 6,623
Thanks Wayne.... And Thanks GSquared for clearing it up for me.. You put it better than how I could explain it.

-Roy
Post #707146
Stamey
Posted Wednesday, April 29, 2009 1:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 11, 2013 11:44 AM
Points: 222, Visits: 866
nataliehinnen (4/29/2009)
Thanks for the replies so far.. I actually agree with including the db owner, but I work with a bunch of geeks who are afraid to "hard code" anything... so I need some ammo.

If anyone else has an opinion, please vote.


It's actually a good thing that they don't want to hard code anything. Wish I could find a group of developers like that. But back to the point. If they are putting the name of the procedure in the code then there's no difference to qualifying the name, as long as you won't have any regular users owning the same short-name procedure.
For instance, if every user had to run a procedure called SetPassword, and all users owner their own, because it did something slightly different, then "hardcoding" "dbo." into the code would be a bad thing. If there is as much chance of the procedure owner name changing as there is of the actual procedure name changing, it's a no-brainer.

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #707188
nataliehinnen
Posted Wednesday, April 29, 2009 2:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 06, 2013 8:33 PM
Points: 14, Visits: 94
True, I do agree that hard coding things in general is not a good thing. I recently needed to restore a backup of a db to a different server and call the database a different name than the original. All 200 procs had the database name hardcoded in them when accessing objects... yuck!

Sooooo... is performance and overhead the ONLY reason to not call objects with owner? I can hear them already saying - "well, there isn't enough activity on the database to worry about that".

Roy, I'm not sure what is meant by "compile lock" in your article... does this kind of lock prevent anyone from accessing that object? And does it apply to whole tables as well?
Post #707204
« Prev Topic | Next Topic »

Add to briefcase
30 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.