A SQL Azure tip a day (5) – What about tempdb?

Is there a tempdb database in SQL Azure? Well, if you try to find tempdb, you’ll fail. You can’t find the tempdb databas any where in management portal or in SQL Server Management Studio. But is there a tempdb? Yes, there is, but you don’t have direct access to tempdb, only indirect by use of temp tables, order by, group by, version store, etc. And you have no control over the tempdb, it’s part of the cloud concept. Do you need access to the tempdb? No, but you have to be carefull when you design your queries, because heavy use of tempdb (>5Gb) may cause a connection termination.

A common practice in SQL server is to split large and complex queries into smaller blocks and store the results in temp tables, but that practice may have to be reconsidered if you have large recordsets SQL Azure databases have read committed snapshot on and every updated record of a table will be placed in the version store (in tempdb), and large updates may cause the connection to terminate. Update records in small pieces to avoid that. But what about rebuilding indexes in tempdb if you have constraints like this in tempdb? Well, don’t even try it, it’s not supported in SQL Azure. ALTER INDEX pk_table1 ON dbo.table2 REBUILD WITH(SORT_IN_TEMPDB=ON)

This statement will fail with the following text: Msg 40517, Level 16, State 1, Line 2 Keyword or statement option ‘sort_in_tempdb’ is not supported in this version of SQL Server.

Keep reading my blog posts for more SQL Azure information, or contact any of our SQL server consultants.