[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

Best Practices on TEMPDB

Hello Experts!
    users in my organisation want to create user-tables or temp-tables on tempdb (SYSTEM DATABASES).
These tmp-tables will be used across servers, is it a good practice..?
0
shanj
Asked:
shanj
2 Solutions
 
kamindaCommented:
instead of temp tables you can always create table variables to store tempory data. But tempory tables are good in performance perspective. For tempory tables SQL Server maintains the statistics so the query optimizer can select an optimized plan. But table variables are always treated as single row tables. I am recommending table variables if they hold huge number of rows otherwise table variables are better.
0
 
Alpesh PatelAssistant ConsultantCommented:
You can not use temp table outside the SEssion or Server also.
0
 
David ToddSenior DBACommented:
Hi

>>I am recommending table variables if they hold huge number of rows otherwise table variables are better.

I think that the intent is that Temp Tables should be used for large numbers of rows, otherwise table variables for small numbers of rows.

It is easier to add indexes to temp tables.

HTH
  David
0
 
shanjAuthor Commented:
iT DID NOT SOLVE MY SPECIFIC PROBLEM.
0
 
David ToddSenior DBACommented:
Hi,

Sorry that we didn't solve your specific problem.

tempdb is a system database, you are correct, but it is precisely for users to create temp tables, there is some possible use by very large table variables, and its used behind the scenes for row versioning.

Best practice is to have tempdb on its own disks, so that it does cause disk contention. so best practice would have a number of disks OS, system DBs (apart from tempdb), tempdb, data, transaction logs, backups.

HTH
  David

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now