System databases are databases that SQL Server Parallel Data Warehouse (PDW) uses to manage users, databases, and temporary data. SQL Server PDW includes the following system databases: master and tempdb.
- 06/03/2025
tempdb is a SQL Server PDW system database that stores local temporary tables for user databases. Temporary tables are often used to improve query performance. For example, you can use a temporary table to modularize a script, and reuse computed data.
local temporary table
A local temporary table uses the # prefix before the table name and is a temporary table created by a local user session. Each session can only access the data in local temporary tables for its own session.
Each session can view the metadata for local temporary tables in all sessions. For example, all sessions can view the metadata for all local temporary tables with the SELECT * FROM tempdb.sys.tables query.
pdwtempdb
pdwtempdb is the database that stores local temporary tables.
PDW does not implement temporary tables by using the SQL Server tempdb database. Instead, PDW stores them in a database called pdwtempdb. This database exists on each Compute node and is invisible to the user through the PDW interfaces. In the Admin Console, on the storage page, you will see these accounted for in a PDW system database called tempdb-sql.
tempdb
tempdb is the SQL Server tempdb database. It uses minimal logging. SQL Server uses tempdb on the Compute nodes to store temporary tables that it needs in the course of performing SQL Server operations.
Permissions
Any user can create temporary objects in tempdb. Users can only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but this is not recommended as some routine operations require the use of tempdb.
Related Tasks
Expand table
| Tasks | Description |
|---|---|
| Create a table in tempdb. | You can create a user temporary table with the CREATE TABLE and CREATE TABLE AS SELECT statements. For more information, see CREATE TABLE and CREATE TABLE AS SELECT. |
| View a list of existing tables in tempdb. | SELECT * FROM tempdb.sys.tables; |
| View a list of existing columns in tempdb. | SELECT * FROM tempdb.sys.columns; |
| View a list of existing objects in tempdb. | SELECT * FROM tempdb.sys.objects; |
https://learn.microsoft.com/en-us/sql/analytics-platform-system/system-databases?view=aps-pdw-2016-au7
https://www.sqlshack.com/automate-rebuild-indexes-of-sql-database-using-maintenance-plans
https://www.epiusers.help/t/sql-performance-tune/38032/5
