February 15, 2005 at 5:08 pm
Someone was talking about qualifying objects in code and the fact that they reference all objects by the owner, such as
select *
from dbo.mytable
when writing code. This is to prevent SQL Server from having to check if you are querying sjones.mytable (my login) and then checking dbo.mytable for permissions. By qualifying the owner of the table, you save a tiny, minute, ever-so-small use of resources that one most servers would never be noticed, but in a highly transactional environment with lots of users, can add up to some amount of performance loss.
Then they mentioned that they also qualified code such as this:
select *
from dbo.#mytemptable
Someone pinged me to see if there was any validity to this and if it was necessary. Since I'm not the world's most expert SQL Server guru, I wasn't quite sure why I got asked, but I weighed in since I wasn't all that busy. My thought was that when you create a table, unless you qualify it, it is created under your login. Assuming you have privileges and all that.
My thought would be that a temp table, which is created in tempdb.dbo.sysobjects and not in your current database, would be owned by the login. However a search of tempdb.dbo.sysobjects shows this object as owned by dbo. Despite the fact that only the creator, sjones in this case, would be able to access it. I should note that tempdb.dbo.sysusers does not contain an entry for sjones. Only Northwind.dbo.sysusers has sjones in it.
My guess here is that SQL Server internally manages the access to tempdb objects, all of which I believe are owned by dbo. Somehow the name of the objects relates back to the user in another database using some mechanism that allows multiple users in multiple databases to somehow "share" the tempdb resources. I admit it's something I haven't thought a lot about, but it was interesting.
Got any more information? Drop it in the forum thread for this editorial.
Steve Jones
February 16, 2005 at 3:30 am
From my experience all objects that are created in tempdb by default are owned by dbo. However I have seen two instances when this is not true. The first is when you explicitly declare an object to be owned by someone else eg
create table sjones.#mytemptable
Or you have created an object in the model database that is not owned by dbo and when tempdb gets created it uses the model database as the template.
February 16, 2005 at 7:18 am
It is based on connection not the specific SPID as attached or the user id. I am sure there is a mechanism somewhere in memory handling mapping to a SPID but not sure if there is a specific way to see it.
The key is this, for each connection when they create a temp table the table name is suffixed a long string of _'s plus a hexidecimal key. The key increments with each new connection, but all temp tables created on the same connection get the same suffix. This is how multiple objects with the same type, name and owner can coexist.
However when you create a global temp table (## varity) the suffix is not appended.
Based on the fact that the local temp table drops when the connection does I suspect the hex id may be stored with the connection state itself.
Beyond that you can name an owner for an object and of course create multiple objects with the same name as long as the owner is different as normal.
What you then end up with in sysobjects of tempdb is tablename + "bunch_'s" + hex id.
I have noted thou the hex id increments each connection, I have noticed it restarts from the begining when the server is restarted.
Also I have seen cases where instead of incrementing by 1 it is incrementing in larger gaps. I also assum that should it exaust it's limit it will recycle from the begining.
The hex id specificially is 12 characters wide at least to begin with, I do not know if it grew beyond 281474976710655 would it expend into the _'s.
February 16, 2005 at 7:38 am
I think Antares is close with the idea that it's tied to connection. However, it is still more complex than that since one spid/connection can create more than one temp table with the same name. See below.
create proc db_maketmp
as
create table #a ( i int )
insert into #a values ( 11 )
insert into #a values ( 22 )
select name from tempdb.dbo.sysobjects where name like'#a%'
select * from #a
go
create table #a
( i int )
select name from tempdb.dbo.sysobjects where name like'#a%'
insert into #a values ( 1 )
insert into #a values ( 2 )
select * from #a
exec db_maketmp
select name from tempdb.dbo.sysobjects where name like'#a%'
select * from #a
drop table #a
February 16, 2005 at 8:25 am
Other weirdness along these lines: You can't turn off the ANSI Warnings in TempDB using sp_dboption.
"sp_dboption Displays or changes database options. sp_dboption should not be used on either the master or tempdb databases. sp_dboption is supported for backward compatibility. Use ALTER DATABASE to set database options."
and if you try
alter database tempdb set ANSI_WARNINGS off
Well, you can probably imagine that it doesn't make a darned bit of difference. I'm getting warnings from SQL Server whenever I execute a stored procedure that creates a temporary table with (possibly) more than 8060 bytes. That would be fine, except that .NET interprets it as a fatal error and hits the catch instead of waiting for the sp to finish.
I wouldn't be having this trouble if I could get a dts or bulk inserts to assess the file before attempting to import it and potentially erroring out.
February 16, 2005 at 11:01 am
Probably most of us have assigned ourselves "db_owner" in the database role area. This would explain why tables we create use dbo as the owner, while those others create may be owned by themselves.
Maybe someone else will curious enough to experiement and verify if this is true?
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
February 16, 2005 at 11:12 am
I didn't get that far into testing. I did notices this however when I ran your example.
I got
#a__________________________________________________________________________________________________________________00000000002B
#a__________________________________________________________________________________________________________________00010000002B
The second set of values changed on the hex section (remember HEX is 00 - FF not 0-FF so look at as sets). May be that part of the number actually is assigned to deal with scope.
So I tried doing with a trigger as well and this is what I did
CREATE TABLE X ( i int )
GO
CREATE TRIGGER tr_X on X
FOR INSERT
AS
create table #a ( i int )
insert into #a values ( 111 )
insert into #a values ( 222 )
select name from tempdb.dbo.sysobjects where name like'#a%'
select * from #a
GO
create proc db_maketmp
as
create table #a ( i int )
insert into #a values ( 11 )
insert into #a values ( 22 )
insert x (i) values (1)
select name from tempdb.dbo.sysobjects where name like'#a%'
select * from #a
go
create table #a
( i int )
select name from tempdb.dbo.sysobjects where name like'#a%'
insert into #a values ( 1 )
insert into #a values ( 2 )
select * from #a
exec db_maketmp
select name from tempdb.dbo.sysobjects where name like'#a%'
select * from #a
drop table #a
This was my result at the point the trigger fired
#a__________________________________________________________________________________________________________________00000000002B
#a__________________________________________________________________________________________________________________00010000002B
#a__________________________________________________________________________________________________________________00020000002B
So it incremented each one in the same location so we can assume the first 2 sets from the hex represent something else and the rest may all represent connection.
I would be curious if anything unusual happens if done across a linked server but I don't have one setup right now.
February 16, 2005 at 11:21 am
Russell, I tried your test with a user in the public role only and here is the result from
select * from INFORMATION_SCHEMA.TABLES:
tempdb dbo #a__________________________________________________________________________________________________________________000000000034 BASE TABLE
February 16, 2005 at 2:43 pm
This is more of a question for my own understanding, but is sjones a local admin on the server where this test was run? If so, wouldn't the temp table automatically map to dbo because of the builtinadmin security account even though sjones was not explicitly granted access to the target db?
February 17, 2005 at 2:04 pm
That's on the same line as my thought. It would make sense to test this using a normal user account, rather than the administrator account.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
February 17, 2005 at 2:05 pm
What happens in a normal database?
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
February 17, 2005 at 2:35 pm
Russell, this was a normal database. I was logged in as a user who only had priveleges in the public role, not dbo. The results shown were from tempdb logged in as a dbo AFTER the public user created the temp table.
dab
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply