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

    Question

  • I need to create partition on temp table. Can anyone please suggest me is it possible to create.
    Thursday, September 05, 2013 9:52 AM

Answers

  • 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.

    • Proposed as answer by vr.babu Thursday, September 05, 2013 10:12 AM
    • Marked as answer by Sanchayeeta Gaine Friday, September 06, 2013 11:34 AM
    Thursday, September 05, 2013 10:10 AM
  • 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

    • Proposed as answer by Shanky_621 Thursday, September 05, 2013 12:12 PM
    • Marked as answer by Sanchayeeta Gaine Friday, September 06, 2013 10:55 AM
    Thursday, September 05, 2013 12:06 PM

All replies

  • 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.

    • Proposed as answer by vr.babu Thursday, September 05, 2013 10:12 AM
    • Marked as answer by Sanchayeeta Gaine Friday, September 06, 2013 11:34 AM
    Thursday, September 05, 2013 10:10 AM
  • 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

    • Proposed as answer by Shanky_621 Thursday, September 05, 2013 12:12 PM
    • Marked as answer by Sanchayeeta Gaine Friday, September 06, 2013 10:55 AM
    Thursday, September 05, 2013 12:06 PM
  • Thanks for your answer.

    Friday, September 06, 2013 10:56 AM
  • Thanks for your suggestion.

     
    Friday, September 06, 2013 10:57 AM