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


Best Practices/Standards - to include or not to include database owner for table names


Best Practices/Standards - to include or not to include database owner for table names

Poll
Should you include database owner when accessing tables in your t-sql code?

85.19% - 23 votes Include database owner
85.19% 23 votes
14.81% - 4 votes Don't include database owner
14.81% 4 votes
Member votes: 26, Guest votes: 0. You don't have permission to vote in this poll
Author
Message
nataliehinnen
nataliehinnen
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23267 Visits: 9730
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
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 6869
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.... CoolHehe

-Roy
nataliehinnen
nataliehinnen
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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.
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 6869
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
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9723 Visits: 10568
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
Author - SQL Server T-SQL Recipes
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

GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23267 Visits: 9730
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
Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3708 Visits: 6869
Thanks Wayne.... And Thanks GSquared for clearing it up for me.. You put it better than how I could explain it. :-)

-Roy
Stamey
Stamey
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1048
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.
nataliehinnen
nataliehinnen
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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?
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