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 123»»»

Best Practices/Standards - to include or not to include database owner for table names Expand / Collapse
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
Posted Wednesday, April 29, 2009 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 6, 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
Posted Wednesday, April 29, 2009 9:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Wednesday, April 29, 2009 9:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
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
Posted Wednesday, April 29, 2009 10:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 6, 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
Posted Wednesday, April 29, 2009 10:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
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
Posted Wednesday, April 29, 2009 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 5,364, Visits: 8,952
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
Posted Wednesday, April 29, 2009 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Wednesday, April 29, 2009 12:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
Thanks Wayne.... And Thanks GSquared for clearing it up for me.. You put it better than how I could explain it.

-Roy
Post #707146
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: Tuesday, October 21, 2014 1:40 PM
Points: 233, Visits: 924
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
Posted Wednesday, April 29, 2009 2:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 6, 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 123»»»

Permissions Expand / Collapse