locked
Table partitioning in SQL server 2008 R2 RRS feed

  • Question

  • Hi,

    Is table partitioning feature is not available in SQL 2008 R2? (I am using Enterprise edition)

    here is my sql server info:

    Microsoft SQL Server Management Studio      10.50.1600.1
    Microsoft Analysis Services Client Tools      10.50.1600.1
    Microsoft Data Access Components (MDAC)      6.1.7600.16385
    Microsoft MSXML      3.0 4.0 5.0 6.0
    Microsoft Internet Explorer      8.0.7600.16385
    Microsoft .NET Framework      2.0.50727.4963
    Operating System      6.1.7600

    I am just looking a feature like: right click on table and select partiotion option.. some thing like this.. but i could not find any of these options in SQL 2008 R2. Am I missing some thing? please guide me.


    Venkat

    Sunday, March 25, 2012 3:07 PM

Answers

  • So, looks like my sql is Standard Editiion and not an enterprise edition?

    and, the partitoning feature is not available in standard edition?

    Table partitioning is available only in Data Center, Enterprise, PDW, Developer and Evaluation editions.  That explains why the option is unavailable in the SSMS GUI when connected to a Standard instance.

    You can still use a partition SWITCH to move all data from one table to an identical empty table, such as during a load process.  But you can't create partition boundaries using partition functions and schemes with Standard edition.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by amber zhang Tuesday, March 27, 2012 2:30 AM
    • Marked as answer by amber zhang Monday, April 2, 2012 3:35 AM
    Monday, March 26, 2012 12:11 PM

All replies

  • Right-click your table and select storage / create partition option and there you go with a wizard used for partitioning.

    Marek Chmel, WBI Systems (MCTS, MCITP, MCT, CCNA)
    Please Mark As Answer if my post solves your problem or Vote As Helpful if a post has been helpful for you.

    Sunday, March 25, 2012 3:20 PM
  • Hi,

    I am not getting the storage option when I right click on table. please see the attached diagram.


    Venkat

    Sunday, March 25, 2012 3:57 PM
  • Hi,

    I am not getting the storage option when I right click on table. please see the attached diagram.


    Venkat

    Sunday, March 25, 2012 3:58 PM
  • Are you certain the server is SQL Server 2008 R2 Enterprise Edition?  What does SELECT @@VERSION return?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, March 25, 2012 4:12 PM
  • The  SELECT @@VERSION returned the following value:

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Standard Edition on Windows NT 6.1 <X86> (Build 7600: )

    So, looks like my sql is Standard Editiion and not an enterprise edition?

    and, the partitoning feature is not available in standard edition?


    Venkat

    Monday, March 26, 2012 7:04 AM
  • Yep, you are running Standard edition, where partitioning in not available. Its an enterprise feature.

    Marek Chmel, WBI Systems (MCTS, MCITP, MCT, CCNA)
    Please Mark As Answer if my post solves your problem or Vote As Helpful if a post has been helpful for you.

    Monday, March 26, 2012 7:05 AM
  • So, looks like my sql is Standard Editiion and not an enterprise edition?

    and, the partitoning feature is not available in standard edition?

    Table partitioning is available only in Data Center, Enterprise, PDW, Developer and Evaluation editions.  That explains why the option is unavailable in the SSMS GUI when connected to a Standard instance.

    You can still use a partition SWITCH to move all data from one table to an identical empty table, such as during a load process.  But you can't create partition boundaries using partition functions and schemes with Standard edition.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by amber zhang Tuesday, March 27, 2012 2:30 AM
    • Marked as answer by amber zhang Monday, April 2, 2012 3:35 AM
    Monday, March 26, 2012 12:11 PM
  • Partitioning is very useful but the pricey licensed editions offer this only.  If you see where this could be useful to your organization, use the developer editions of SQL and spin up in a test/developer environment to practice / test / evaluate as a proof-of-concept before presenting to management.  The developer edition is $50 per license for one server, offers all the Enterprise functionality and gives you more time than the evaluation versions.  Usually, there is quite a bit of thought, trial and error when using partitioning.  Cheers
    Friday, March 28, 2014 7:02 PM