Fake Temporary Tables and SQL Server 2000

You can use SQL Server 2000's table data type as an alternative to temporary tables in some cases. Table variables point to table structures in memory, so they produce less overhead than temporary tables. However, table variables are always local to the process that created them, and you can't define a stored procedure parameter as a table data type to send or receive data sets.

Using the new sql_variant data type, you could generalize a fake temporary table definition to hold different data types in a single field depending on the process that you need to trace.

You can create new user-defined functions (UDFs) to return resultsets. Just use a table variable as the return value, and define the UDF as either an inline or multistatement table-valued function. UDFs can be flexible, but they can't directly modify actual data, so their use is limited to READ operations. To use UDFs to implement a fake temporary table strategy that involves data modifications, you need to combine the UDFs with stored procedures, which would handle the actual modifications.

With SQL Server 2000, you can use the CREATE TABLE statement inside triggers. So, you can create a temporary table inside a trigger, if necessary.