Questions on TempDB database on Azure SQL instance (not managed instance or SQL Server on VM)

Question

Hi,

Could you please clarify on following questions on TempDB which gets created in the background when Azure SQL Database is created?

1. Why i can't see TempDB on Azure SQL instance hosting my Azure DBs from SSMS?
2. Can I create UDTs (user defined types) on TempDB? I know UDTs are supported on Azure SQL Database but we need to have same UDTs in TempDB also?
3. If UDts are supported in Tempdb, how can we create them?

I tried three options and none worked as expected.
Use tempdb
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

CREATE TYPE tempdb..LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

Changing the connection and specify TempDB database....

Appreciate your insightful response !!

Answer 1

Hello @anil kumar ,

Tempdb visibility in Azure SQL Database is limited, however you can accomplish what you described as follows:

exec tempdb.sys.sp_executesql N'  
CREATE TYPE LocationTableType AS TABLE  
( LocationName VARCHAR(50)  
, CostRate INT );';  
  
select *  
from tempdb.sys.types  
where name = 'LocationTableType';  

Keep in mind that objects in tempdb are not persistent across database failovers, so you should have logic to recreate these types when they are not found, e.g. after database maintenance events that cause the database to fail over.