Thursday, October 25, 2007

SQL Table Variables - Limitations

I've come to really like table variables in SQL. They are a nice and natural development of database programming. And, when used smartly, can greatly improve performance compared to using temporary tables.

Today I've discovered that table variables cannot be truncated. I had no idea...

Anyway, I've found a cool source of more information about table variables, including some stuff that is not or not well documented in Books Online. Check here and here.

When you think of it - it makes sense. As far as I know, table variables are memory constructs whereas temporary tables are kept in tempdb. Truncating a table simply removes the reference of the table's page from the database. However, if the table is only kept in memory and in no actual database - it has no meaning...

No comments: