Is it possible to create partition on Temp table in sql server??

_{Thursday, September 5, 2013 9:52 AM}

I need to create partition on temp table. Can anyone please suggest me is it possible to create.

_{Thursday, September 5, 2013 10:10 AM ✅Answered}

Thats meaningless for quite few reason.

1. Partition is for really huge data(May be 100 GB or more). If you think one temp table has this size, then I wonder there is something wrong on your approach.

2. Temp tables are residing at TEMPDB, I mean, they are not persistent as other user database. So No point.

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Thursday, September 5, 2013 12:06 PM ✅Answered}

I need to create partition on temp table. Can anyone please suggest me is it possible to create.

You can partition a table in tempdb exactly as you would partition a table in a user database; create a partition function and scheme and specify the partition scheme on the ON clause of CREATE TABLE/INDEX.  Note that tempdb is recreated each time SQL Server is restarted so you'll need to recreate the partition function and scheme after each restart.

Can you elaborate on why you want to partition the temp table?  Table partitioning can improve manageability but not necessarily performance.  Index and query tuning is most often the key to performance regardless of table size.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

_{Friday, September 6, 2013 10:56 AM}

Thanks for your answer.

_{Friday, September 6, 2013 10:57 AM}

Thanks for your suggestion.