SQL Server Performance

Alter Table variable

Discussion in 'General Developer Questions' started by Madhivanan, Apr 11, 2005.

  1. Madhivanan Moderator


    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
  2. dineshasanka Moderator

    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.

  3. Adriaan New Member

    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
  4. Madhivanan Moderator

    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
  5. Adriaan New Member

    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.

Share This Page