Temp table creation
in [Programming SQL]
|
From: Ralph on 1 Feb 2010 15:26 Are there any differences when creating a local temp table using the syntax create table #temp to create table tempdb..#temp thanks in advance Ralph
From: Tom Cooper on 1 Feb 2010 15:59 Yes, create table #temp will create a local temporary table in tempdb that can only be accessed by your connection. create table tempdb..#temp is illegal and will give you a syntax error. Tom "Ralph" <ralphd42(a)hotmail.com> wrote in message news:8DD1A6D5-A2DE-4578-A4C6-65FD71D3F27E(a)microsoft.com... > Are there any differences when creating a local temp table using the > syntax create table #temp > to create table tempdb..#temp > > > thanks in advance > > Ralph > >
From: Ralph on 1 Feb 2010 16:07 "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:e6aL1F4oKHA.5696(a)TK2MSFTNGP04.phx.gbl... > Yes, > > create table #temp > will create a local temporary table in tempdb that can only be accessed by > your connection. > > create table tempdb..#temp > is illegal and will give you a syntax error. > > Tom > > "Ralph" <ralphd42(a)hotmail.com> wrote in message > news:8DD1A6D5-A2DE-4578-A4C6-65FD71D3F27E(a)microsoft.com... >> Are there any differences when creating a local temp table using the >> syntax create table #temp >> to create table tempdb..#temp >> >> >> thanks in advance >> >> Ralph Thanks Tom small error on my part. I met create table tempdb..temp Without the #
From: Tom Cooper on 1 Feb 2010 16:30 "Ralph" <ralphd42(a)hotmail.com> wrote in message news:edkKwK4oKHA.1552(a)TK2MSFTNGP04.phx.gbl... > > > > "Tom Cooper" <tomcooper(a)comcast.net> wrote in message > news:e6aL1F4oKHA.5696(a)TK2MSFTNGP04.phx.gbl... >> Yes, >> >> create table #temp >> will create a local temporary table in tempdb that can only be accessed >> by your connection. >> >> create table tempdb..#temp >> is illegal and will give you a syntax error. >> >> Tom >> >> "Ralph" <ralphd42(a)hotmail.com> wrote in message >> news:8DD1A6D5-A2DE-4578-A4C6-65FD71D3F27E(a)microsoft.com... >>> Are there any differences when creating a local temp table using the >>> syntax create table #temp >>> to create table tempdb..#temp >>> >>> >>> thanks in advance >>> >>> Ralph > > Thanks Tom small error on my part. > I met create table tempdb..temp > Without the # > > > create table tempdb..temp will create a permananent table in temp db. Unlike local temporary tables, it can be accessed by other connections. Unlike either local or global temporary tables which go away when the creating connection closes and no other connection is using them, a table created explicitely in tempdb will live until the table is either explicitly DROPed, or the SQL Server service is stopped and restarted. (The reason it goes away when the service is stopped and restarted is that that will drop and recreate the whole tempdb database). But except for the fact that it will go away when the service stops and restarts, it is just like any normal permananent table you create. Tom
From: Ralph on 1 Feb 2010 16:43 "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:e63yBX4oKHA.1552(a)TK2MSFTNGP05.phx.gbl... > > "Ralph" <ralphd42(a)hotmail.com> wrote in message > news:edkKwK4oKHA.1552(a)TK2MSFTNGP04.phx.gbl... >> >> >> >> "Tom Cooper" <tomcooper(a)comcast.net> wrote in message >> news:e6aL1F4oKHA.5696(a)TK2MSFTNGP04.phx.gbl... >>> Yes, >>> >>> create table #temp >>> will create a local temporary table in tempdb that can only be accessed >>> by your connection. >>> >>> create table tempdb..#temp >>> is illegal and will give you a syntax error. >>> >>> Tom >>> >>> "Ralph" <ralphd42(a)hotmail.com> wrote in message >>> news:8DD1A6D5-A2DE-4578-A4C6-65FD71D3F27E(a)microsoft.com... >>>> Are there any differences when creating a local temp table using the >>>> syntax create table #temp >>>> to create table tempdb..#temp >>>> >>>> >>>> thanks in advance >>>> >>>> Ralph >> >> Thanks Tom small error on my part. >> I met create table tempdb..temp >> Without the # >> >> >> > > create table tempdb..temp > > will create a permananent table in temp db. Unlike local temporary > tables, it can be accessed by other connections. Unlike either local or > global temporary tables which go away when the creating connection closes > and no other connection is using them, a table created explicitely in > tempdb will live until the table is either explicitly DROPed, or the SQL > Server service is stopped and restarted. (The reason it goes away when > the service is stopped and restarted is that that will drop and recreate > the whole tempdb database). But except for the fact that it will go away > when the service stops and restarts, it is just like any normal > permananent table you create. > > Tom Tom, Thank you very much for that explanation. it makes complete sense and has clarified a few issues I have been dealing with in a few stored procs.
|
Next
|
Last
Pages: 1 2 Prev: Outer Join showing default values instead of Null values Next: MLM - Binary Payout Script |