Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miri
Starting Member
9 Posts |
 Posted - 2008-09-10 : 06:28:43
|
| Hello!When I write a quary in sql I get automatically the word "dbo" before of the table's name.Is it necessary? what can be if I will delete it?I tried to run a quary without it, and it worked good.but I want to know what are the risks to remove it?Thanks,miri |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-09-10 : 06:37:33
|
| dbo is the owner. The user who created the table must be having sysadmin roles. You needn't try and delete it. |
 |
|
|
miri
Starting Member
9 Posts |
Posted - 2008-09-10 : 07:34:44
|
| I know that dbo is the owner. but I have cases that I don't know the owner's name so I want to ignore it. Can I?miri. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-09-10 : 10:39:53
|
| If you do not specify the owner when calling a procedure, the database first looks for a procedure of the same name that is owned by you. If it cannot find one, then it looks to see if there is a procedure of the same name owned by dbo. If the procedure is owned by another user, the database will not find it and will not execute it.When you specify the owner (whether it be dbo or another owner), the engine looks only for the procedure owned by the owner you specified. No other procedure would run, even if it had the same name.It can be argued the specifying dbo is faster because then database does not bother looking for the procedure in your own schema first, but this advantage is trivial.It can also be argued that specifying dbo prevents you from being able to develop different flavors of a procedure for different logins (to account for different permissions or business rules, for instance), and I personally think this architectural consideration trumps the minuscule cost of not specifying a owner.So, long story short, I do not specify object owners when I call them.Boycotted Beijing Olympics 2008 |
|
|
|
miri
Starting Member
9 Posts |
Posted - 2008-09-11 : 02:05:48
|
| Thanks a lot for the for the long and specifically answer!!!miri |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-09-11 : 02:52:47
|
it matters little for 2005 since we have schemas instead of users as prefixes, but on 2000 if you don't specify the owner, then there was a bit more of a perf hit as I recall. It's not just the extra table lookup, it's that the query plan cache gets bloated unnecessarily. It's because the server would cache a different query plan for each user if you didn't specify the owner on an object. So you'd end up with the query plan cache being more bloated that it needs to be if you don't specify owners on 2000.that is, if user1 and user2 both issued this query there would be two plans in the cache, one for each user:select * from MyTablebut if user1 and user2 both issued this query, there'd only be one plan in the cache:select * from dbo.MyTableso if you have an app using SQL 2000 with 100 different users, your query plan cache is 100 times bigger than it needs to be (or you can fit 100 times less in it)
elsasoft.org |
|
|
|
|
|
|
|
|
writing "dbo" before the name of the table