Friday, January 27, 2006

Don't use SQL Server 2000 Table Variables

SQL 2k's table variable is handy in some ways -- less locking required during transactions, fewer sproc recompiles than temp tables. However, I don't recommend using table variables because:
  • Table variables cannot be defined using User Defined Types. We use UDTs frequently to enforce consistency in even the smallest dbs. Table variables force you to break this best practice and reverse its benefits.
  • Unclear that the variable is a table. The # (or ##) prefix of temp tables gives immediate indication that it is a table. Although good naming conventions could be used to reduce this, naming conventions aren't validated by the compiler.

No comments: