Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Difference between @TableName and #tableName

    Whats the difference between @TableName and #TableName,

    Where one will work in the following syntax and the other won't

    INSERT INTO #TableName(Field)
    EXEC SPROC @Param

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    @t is a table variable, #t is a temp table.

    insert/exec does not work with table variables.

    here are some other differences:
    http://www.google.com/search?q=diffe...able+variables

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For smaller datasets, use a table variable.
    For larger datasets, (1000+ rows, for example) use a temp table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    That's good to know, its just in a SPROC, was trying to determine which to use, as only the #temp would accept the information from another, it now makes sense why that is, don't like doing things that I don't understand,

    Those links where helpful, and will remember to use temp tables for large datasets, and variables for small, thank you

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One other key point:
    Table variable can have only one index; the clustered index.
    Temporary tables can have any number of indexes, making them more appropriate for datasets that are filtered or joined on different columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •