Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
ThanksHi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>|||Why bother about the physical name? Just use the temp table name you gave it
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?

> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, wh
en
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB

> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>
>