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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-01-28 : 13:47:12
|
| Common coding practice for tables seems to be to include the owner:FROM dbo.MyTableI see thatFROM dbo.#Temptable is also valid, but I haven't seen anyone code it that way (in this forum).Any pros or cons? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-01-28 : 13:58:37
|
| As I understand it:For non-temp tables its a good idea to include the owner explicitly because when SQLServer generates an execution plan it will first look for the table as owned by whoever the user is, if it can't find it then it looks for the tablename onwned by db owner. If you specify the user by a qualified table name then that's one step less for the optimizer. It's similar to why you shouldn't name your SPs with "sp_" prefix. SqlServer will first look for those in the master database.I don't know if that holds true for temp tables. I would think temp tables would be more session based (processid) then user based. I have used the the fully qualified temp table name when checking for the existance of a temp table using Object_ID() ie: if Object_ID('tempdb.dbo.#temp') > 0 drop table #temp.Be One with the OptimizerTG |
 |
|
|
Lee-Z
Starting Member
7 Posts |
Posted - 2005-01-31 : 06:50:51
|
| If the user creating the temptable is not member of sysadmins it won't be dbo.#tblTempso with temporary tables I use something like thisif exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tblTemp')) drop table #tblHITemp |
|
|
|
|
|
|
dbo.#temptable ???