Post reply

Difference between temp table and table variable

  • November 9, 2012 at 2:04 am

    #284688

    I've heard both temp table and table variable are created in tempdb. However, one thing i don't understand is related to collation. When my db has a collation different from tempdb, join with temp table will generate collation error while join with table variables won't. Does that mean the table variable is actually NOT created in tempdb? Thank you in advance if any one could help to clarify it.

  • November 9, 2012 at 2:10 am

    #1557496

    http://support.microsoft.com/kb/305977/en-gb

  • November 9, 2012 at 2:52 am

    #1557511

    I read the article. However, I'm still confused. My question is on the collations created with either type of temporary tables. Will table variable always use the collation in user db while temp table use the collation in tempdb?

  • November 9, 2012 at 2:56 am

    #1557513

    Sorry forgot to add this one in on the definition of a table variable

    http://msdn.microsoft.com/en-us/library/ms188927.aspx

    COLLATE section

    If not specified, the column is assigned either the collation of the user-defined data type (if the column is of a user-defined data type) or the collation of the current database.

    So it takes the collation of the database to which the context of the SPID was in when the table variable was created if you do not specify the specific collation for the column at the table variable declaration.

  • November 9, 2012 at 3:34 am

    #1557524

    Thanks Anthony. It's very clear!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply