Why is it not possible to alter the table Variable? I used Declare @t table (i int,n varchar(20)) alter table @t add id int identity Error Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '@t'. and Declare @t table (i int,n varchar(20)) declare @s varchar(100) set @s='alter table '+@t+' add id int identity' exec @s Error Server: Msg 137, Level 15, State 2, Line 5 Must declare the variable '@t'. Madhivanan Failing to plan is Planning to fail
use sp_executesql from BOL Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
A variable of the TABLE type really is a table. You must declare the TABLE type variable in one statement: there is no way to ALTER a TABLE type variable. Also, there are lots of things that are often done with an ALTER TABLE statement, but that can be included in the CREATE TABLE statement (or a DECLARE @T TABLE statement, for that matter) as well: DECLARE @T TABLE (i INT IDENTITY, n VARCHAR(20)) By the way, I don't think you can alter an INT column to an INT IDENTITY column! If you need flexibility in the number of columns, use a temporary table instead: CREATE TABLE #T (n varchar(20)) GO ALTER TABLE #T ADD id INT IDENTITY GO
Thanks for the replies. Adriaan, What are the advantages of Table Variable over temp tables? As we know, temp tables use memory resource, whereas table variable does not. So is it advisable to use the table variable in the application? Madhivanan Failing to plan is Planning to fail
The temp tables are always created in the tempdb database, whereas SQL Server will try to create TABLE type variables in memory - but there is a point where SQL Server decides to use tempdb anyway, probably to do with how many memory pages are expected to be taken up by the table. If SQL Server uses tempdb, I guess you will see some increase in disk IO. It is recommended to use TABLE type variables, but they do have quirks - like they don't seem to appreciate zero-length strings as default value and return NULL anyway. And as I said SQL Server can decide at any moment to create the table in tempdb anyway ... I ran into the problem with the ZLS once, but can't reproduce it at the moment. Still, it is recommended to use TABLE type variables, but SQL Server can decide at any moment to create the table in tempdb anyway, which kind of takes away from the advantages.
