Friday, June 19, 2015

Can I create a permanent table inside the tempdb?

Microsoft SQL Server comes mainly with five system databases: master, model, msdb, tempdb and resources. In addition to that some more databases are created with different implementations such as replications. During a discussion, a question related to tempdb came up: What do not we maintain a permanent table inside the tempdb if we need to maintain some data not related to business-related database?

First thing we need to understand is, how these system tempdb works. When SQL Server services is restarted, tempdb gets recreated using the template which is model. Since it is getting recreated, we lose all our works we have done with tempdb before the restart. Considering this fact, should we create a table inside tempdb, other than temporary tables that are prefixed with either # or ##?

Here is an example, this code creates a table inside the tempdb and then it shuts down the service.

-- Connecting with tempdb
USE tempdb;
GO

-- Creating a table (permanent)
CREATE TABLE dbo.TestTable
(Id int);
GO

-- Query and see, this works
SELECT * FROM dbo.TestTable;

-- Sending shutdown singnal
SHUTDOWN WITH NOWAIT;

Once manually restarted, we can query the same table and see, result is shown below.

-- Connecting with tempdb
USE tempdb;
GO

-- Query and see, this works
SELECT * FROM dbo.TestTable;


Do you still want to create permanent tables inside the tempdb?

No comments: