April 29, 2009 at 8:57 am
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.
April 29, 2009 at 9:21 am
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
April 29, 2009 at 9:30 am
April 29, 2009 at 10:16 am
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.
April 29, 2009 at 10:19 am
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
April 29, 2009 at 12:41 pm
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
April 29, 2009 at 12:43 pm
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
April 29, 2009 at 12:52 pm
Thanks Wayne.... And Thanks GSquared for clearing it up for me.. You put it better than how I could explain it. 🙂
-Roy
April 29, 2009 at 1:52 pm
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.
April 29, 2009 at 2:15 pm
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?
April 29, 2009 at 2:25 pm
let me see how I can put this.
It does not affect the table when there is a compile lock. What happens is that the compile lock is pretty hard on your resources. The more compile locks, the worse your performance will be. It can cause a massive slow down if an extensively used Stored proc has to do a compile lock every time it is called.
Let me put it this way... I learnt that fact the hard way. In my former company, one of the developers released an extensively used stored proc with a Hint of recompile. This brought our website down to a grinding halt.
-Roy
April 29, 2009 at 2:25 pm
I like to include the schema (dbo.) in case I have multiple schemas in one database and want to make sure they are using the right set of tables when running the code. I have heard it increases performance to use it but haven't ever tested it out. So I would call using the schema in my case a personal preference. 🙂
April 29, 2009 at 3:16 pm
[font="Verdana"]I think it's best to qualify it from the security point of view as well (thinks of potential object injection attacks). If you need to move things around at a later date, you can always use synonyms to track the movement.
I remember doing performance tests in SQL Server 2000, and discovering that it can make a good 2% difference in performance. Yes, it's only 2%, but it's also pretty much just for the cost of typing "dbo." in front of object names.
[/font]
April 29, 2009 at 7:44 pm
Hi,
Just agreeing with everyone else - but strictly the poll should ask whether to include the schema name, rather than 'database owner'. On first read, I was wondering why the current owner of the database was important!
Note also that the owner of the database is different from members of the db_owner fixed database role, which are both different from the special user 'dbo'.
See this excellent SSC article[/url] for details.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2009 at 8:29 pm
GSquared (4/29/2009)
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.
Hmmm. In my new job, there are hundreds of stored procedures. The only one's (that I've seen) that are calling schema qualified objects are the ones that I've already touched for other performance reasons.
Would the stored procedure go through one schema lock for all of the objects referenced within it, or would it go through a separate schema lock for each object referenced? ie. would a procedure that selects from three tables without schema qualifications go through one schema lock, or three?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply