Tip: "Permanent" Temporary Tables

On the SQL Server Most Valuable Professional (MVP) newsgroup, a private forum available to SQL Server MVPs, a recent thread raised an interesting question about the life expectancy of global temporary tables. Suppose you have a stored procedure that you mark as autoexecution by using sp_procoption so that the procedure will automatically execute each time SQL Server starts. You use the marked stored procedure to create a global temporary table in SQL Server 2000. One of the SQL Server MVPs wondered whether such a table would exist for as long as the SQL Server 2000 instance runs.

Listing A shows an example of the type of stored procedure the MVP was asking about. You probably wouldn't use this type of stored procedure regularly. Instead, you might use such a procedure for limited purposes such as creating a table to store global variables, which SQL Server 2000 doesn't implicitly support. However, this technique is flawed if you can't assume that the global temporary table will always exist.

Microsoft says that SQL Server 2000's code base ensures that the temporary table always exists. Microsoft constructed the code base this way to provide backward compatibility with SQL Server 6.5. So, you can use this technique to create global temporary tables and rest assured that the tables will exist as long as the SQL Server instance is running. Keep in mind, however, that Microsoft doesn't guarantee that future versions of SQL Server will behave this way. You should avoid relying on undocumented system behavior to manage your production systems—but you can never tell when such information will come in handy.