TEMPDB – the most important system database in SQL Server

微信扫一扫,分享到朋友圈

TEMPDB – the most important system database in SQL Server

Introduction

If you had anytime worked in SQL Server, you would be knowing about a system database – TEMPDB. TEMPDB is one of the most important system databases in SQL Server. As the name denotes TEMPDB database is a temporary database to SQL Server to do certain operations. As the topic is vast and its impossible to cover all aspects of TEMPDB in a single blog, planning to cover in multiple blogs to explain about TEMPDB.

Characteristics of TEMPDB

1. TEMPDB is automatically created/recreated every time you start/restart your SQL Service

That means, data anything reside in the tempdb is not persistent forever. If the service has been restarted, all the data will be lost.

2. Without TEMPDB, you will not be able to start your SQL Server

3. TEMPDB cannot be dropped or created manually

4. TEMPDB cannot be backup up

5. TEMPDB can have ONLY on SIMPLE recovery model

6. TEMPDB can have only one filegroup(PRIMARY Filegroup)

7. TEMPDB is a shared database

8. TEMPDB never stored redo information, That means, you can ONLY rollback with help of undo information

9. TEMPDB databse cannot be take to OFFLINE

10. DBCC CHECKALLOC and CHECKCATALOG cannot be issued

Usages of TEMPDB

TEMPDB has been used in SQL Server for various purposes.

1. Global/Temporary tables/Temporary procedures are created in TEMPDB

2. Table Variable

3. Cursors and internal objects created by DB engine to store intermediate results

SQL Server database engine will create internal work tables as required as per the instruction from the relational engine. This is to improve the performance of operations like sorting, spooling, hashing etc.

4. Online index operations needed TEMPDB

5. Snapshot and Read Committed Snapshot Isolation requires TEMPDB

6. Triggers

7. Version store

I am sure this not an exhaustive list, If I miss anything, please feel free to add as comments.

Hope, you had a good read and stay tuned for more about TEMPDB. I will explain each one in detail in coming posts.

微信扫一扫,分享到朋友圈

TEMPDB – the most important system database in SQL Server

“Can you hear me?” —your customer

上一篇

2018Gdevops广州站:一览BAT等名企大牛前沿技术成果

下一篇

你也可能喜欢

TEMPDB – the most important system database in SQL Server

长按储存图像,分享给朋友