ALTER TABLE on temp tables, table variables

ALTER TABLE on temp tables, table variables

Post by Etienn » Thu, 01 Jan 2004 19:04:33



2 questions:
1. ALTER TABLE statements cannot be issued for temporary
tables. True of False?
2. ALTER TABLE statements cannot be issued for table
variables. True of False?

I'm pretty sure 2 is False. I get an error when I try 1 so
I'm thinking it's false too but would like a confirmation.
Thanks for your help.

 
 
 

ALTER TABLE on temp tables, table variables

Post by Vishal Parka » Thu, 01 Jan 2004 19:11:26


  hi Etienne,

  1) false, you can alter temporary tables.
  2) true, table variables can not be altered. they are static. this provides
performance gain over temporary table

  --
  - Vishal

 
 
 

ALTER TABLE on temp tables, table variables

Post by Anith Se » Thu, 01 Jan 2004 19:13:40


1. True & 2. False

You can alter #temp tables, however there is a common issue people get
caught upon. I think you are hitting it too. If you are doing it in a stored
procedure/batch where you are accessing a newly added column immediately
after the ALTER TABLE statement, you'll get an error. This is because, at
the compile time, SQL Server is not aware of the newly added column.

----#1 works
CREATE TABLE #t (c CHAR(1))
SELECT * FROM #t
ALTER TABLE #t ADD x INT
SELECT * FROM #t ;

DROP TABLE #t

----#2 error
CREATE TABLE #t (c CHAR(1))
SELECT * FROM #t
ALTER TABLE #t ADD x INT
SELECT x FROM #t
SELECT * FROM #t WHERE x = 1 ;

--
- Anith
( Please reply to newsgroups only )

 
 
 

ALTER TABLE on temp tables, table variables

Post by Etienn » Thu, 01 Jan 2004 19:16:44


Correction.
I'm pretty sure 2 is true (not false). I get an error when
I try 1 so I'm thinking it's true too (not false).
Duh.

Quote:>-----Original Message-----
>2 questions:
>1. ALTER TABLE statements cannot be issued for temporary
>tables. True of False?
>2. ALTER TABLE statements cannot be issued for table
>variables. True of False?

>I'm pretty sure 2 is False. I get an error when I try 1
so
>I'm thinking it's false too but would like a
confirmation.
>Thanks for your help.

>.

 
 
 

ALTER TABLE on temp tables, table variables

Post by Vishal Parka » Thu, 01 Jan 2004 19:25:47


  you can run "alter table" against temporary tables. probably you are facing
problem what anith has specified.

  --
  - Vishal

 
 
 

ALTER TABLE on temp tables, table variables

Post by Etienn » Thu, 01 Jan 2004 19:31:38


Makes a lot of sense.
Thanks Anith, Vishal.
Etienne
Quote:>-----Original Message-----
>1. True & 2. False

>You can alter #temp tables, however there is a common
issue people get
>caught upon. I think you are hitting it too. If you are

doing it in a stored
Quote:>procedure/batch where you are accessing a newly added
column immediately
>after the ALTER TABLE statement, you'll get an error.
This is because, at
>the compile time, SQL Server is not aware of the newly
added column.

>----#1 works
>CREATE TABLE #t (c CHAR(1))
>SELECT * FROM #t
>ALTER TABLE #t ADD x INT
>SELECT * FROM #t ;

>DROP TABLE #t

>----#2 error
>CREATE TABLE #t (c CHAR(1))
>SELECT * FROM #t
>ALTER TABLE #t ADD x INT
>SELECT x FROM #t
>SELECT * FROM #t WHERE x = 1 ;

>--
>- Anith
>( Please reply to newsgroups only )

>.