none
How can I create partitioned table from scratch?

    Question

  • Hi,

    I would like to create a new partitioned table using this tables couple field will be patitioned function's range.

    Like (1000+ID)+(YYYYMM of datecolumn)

    So How can I create partitioned table with

    CREATE PARTITION FUNCTION  range1 (int)

    ASRANGE RIGHTFORVALUES((1000+ID)+(YYYYMM of datecolumn))

    thanks,

    Gok

    Sunday, August 18, 2013 3:00 PM

Answers

  • So How can I create partitioned table with

    CREATE PARTITION FUNCTION  range1 (int)

    ASRANGE RIGHTFORVALUES((1000+ID)+(YYYYMM of datecolumn))

    A table can have only one partitioning column.  So if you want to partition based on both the ID and datecolumn, you will need to add a computed persisted column to the table based on your formula and specify the desired ranges in the partition function.  Below is an example. 

    Can you elaborate exactly why you want to partition this way?  I just want to make sure you are on the right path as the formula looks a bit odd for the purpose of partitioning.  I think some sample data might help us better understand too.

    CREATE PARTITION FUNCTION  range1 (int)
    AS RANGE RIGHT FOR VALUES(1000, 2000)
    GO
    
    CREATE PARTITION SCHEME scheme1
    AS PARTITION range1 ALL TO ([DEFAULT])
    GO
    
    CREATE TABLE dbo.PartitionedTable(
    	ID int
    	,datecolumn date
    	,partitioning_column AS (1000+ID)+((YEAR(datecolumn)*100)+MONTH(datecolumn)) PERSISTED
    	);
    CREATE CLUSTERED INDEX cdx_PartitionedTable 
    	ON dbo.PartitionedTable(partitioning_column)
    	ON scheme1(partitioning_column);
    


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

    • Marked as answer by Gok Sky Sunday, August 18, 2013 3:57 PM
    Sunday, August 18, 2013 3:20 PM

All replies

  • So How can I create partitioned table with

    CREATE PARTITION FUNCTION  range1 (int)

    ASRANGE RIGHTFORVALUES((1000+ID)+(YYYYMM of datecolumn))

    A table can have only one partitioning column.  So if you want to partition based on both the ID and datecolumn, you will need to add a computed persisted column to the table based on your formula and specify the desired ranges in the partition function.  Below is an example. 

    Can you elaborate exactly why you want to partition this way?  I just want to make sure you are on the right path as the formula looks a bit odd for the purpose of partitioning.  I think some sample data might help us better understand too.

    CREATE PARTITION FUNCTION  range1 (int)
    AS RANGE RIGHT FOR VALUES(1000, 2000)
    GO
    
    CREATE PARTITION SCHEME scheme1
    AS PARTITION range1 ALL TO ([DEFAULT])
    GO
    
    CREATE TABLE dbo.PartitionedTable(
    	ID int
    	,datecolumn date
    	,partitioning_column AS (1000+ID)+((YEAR(datecolumn)*100)+MONTH(datecolumn)) PERSISTED
    	);
    CREATE CLUSTERED INDEX cdx_PartitionedTable 
    	ON dbo.PartitionedTable(partitioning_column)
    	ON scheme1(partitioning_column);
    


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

    • Marked as answer by Gok Sky Sunday, August 18, 2013 3:57 PM
    Sunday, August 18, 2013 3:20 PM
  • Thanks Dan. I want this formula because we have around 50 different productIds with bucnh records everyday so combining with salesdate(YYMM) would be good partition. What do you think?

    Sunday, August 18, 2013 3:56 PM
  • Actually I wanna concat 2 variables not add them.

    like partitioning_column AS CONCAT((1000+ID),((YEAR(datecolumn)*100)+MONTH(datecolumn))) PERSISTED

    How can I do it?

    Sunday, August 18, 2013 4:01 PM
  • Actually I wanna concat 2 variables not add them.

    like partitioning_column AS CONCAT((1000+ID),((YEAR(datecolumn)*100)+MONTH(datecolumn))) PERSISTED

    How can I do it?

    This expression makes a bit more sense; your goal is to partition by both product (ID) and month.  Since this results in a varchar(24), the partition function should be of that data type.  The actual range values depend on you actual product ID values and months.  Is your goal to have 1 partition per product ID and month?  In that case, you would just need to list those in the new range function.  But you need to consider the implications of adding new products and months later.  That will require a SPLIT of existing non-empty partitions, resulting in a lot of unnecessary data movement, unless you plan ahead. 

    How many rows is a bunch?  Partitioning will improve manageability of large tables but performance depends mostly on indexing, partitioned or not, along with your queries.  Are you planning to specify the computed column in query WHERE/JOIN clauses or is it's only purpose to segment the data into multiple partitions?


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

    Sunday, August 18, 2013 4:51 PM
  • Yes My goal is to partition product+date

    I may use computed column in where condition, not sure about it now.

    Also I am having problem to create that table with PERSISTED column

    partitioningID varchar(7) = Select ( cast( 100+productID as char(4)) + convert(char(4), sourceDate, 12))  PERSISTED

    What can be the right syntax to create that column?

    Should I create it with empty table or just add it later on?

    Sunday, August 18, 2013 5:24 PM
  • Below is an example that creates a char(10) persisted column in format nnnnYYYYMM.

    Be aware that if you queries don't specify the partitioning column, all partitions will be touched.  That might not be a big deal if you also have indexes on the column(s) specified in the WHERE/JOIN columns. 

    I suggest you try to create empty partitions for future needs.  But I hesitate to make other recommendations since you haven't answered by questions about the anticipated number of rows and exactly why you want to partition this table.

    CREATE PARTITION FUNCTION  range1 (char(10))
    AS RANGE RIGHT FOR VALUES('1001201307', '1001201308', '1002201307', '1002201308')
    GO
    
    CREATE PARTITION SCHEME scheme1
    AS PARTITION range1 ALL TO ([DEFAULT])
    GO
    
    CREATE TABLE dbo.PartitionedTable(
    	ID int
    	,datecolumn date
    	,partitioning_column AS 
    			  CAST(RIGHT('000' + CAST(ID AS varchar(4)), 4)
    			+ CAST(YEAR(datecolumn) AS char(4))
    			+ RIGHT('0' + CAST(MONTH(datecolumn) AS varchar(2))
    		 , 2) AS char(10)) PERSISTED
    	);
    CREATE CLUSTERED INDEX cdx_PartitionedTable 
    	ON dbo.PartitionedTable(partitioning_column)
    	ON scheme1(partitioning_column);

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


    • Edited by Dan GuzmanMVP Sunday, August 18, 2013 5:58 PM correct script
    Sunday, August 18, 2013 5:56 PM
  • Thanks Dan. I am getting an error when I try to create table

    CREATE TABLE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

    Sunday, August 18, 2013 6:14 PM
  • Thanks Dan. I am getting an error when I try to create table

    CREATE TABLE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

    As the error message indicates, certain SET options must be set to the correct values.  This is required in order to maintain indexes on computed columns and views.  See the SQL Server Books Online: http://msdn.microsoft.com/en-us/library/ms189292.aspx

    The required settings are:

    SET NUMERIC_ROUNDABORT OFF;
    SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;


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

    Sunday, August 18, 2013 9:39 PM
  • thanks
    Monday, August 19, 2013 12:26 AM
  • I am trying to create the table but I am getting non deterministic error

    Msg 4936, Level 16, State 1, Line 11

    Computed column 'partitionID' in table XXXX_Partitoned' cannot be persisted because the column is non-deterministic.


    • Edited by Gok Sky Wednesday, August 21, 2013 6:33 PM
    Wednesday, August 21, 2013 6:29 PM
  • Computed column 'partitionID' in table XXXX_Partitoned' cannot be persisted because the column is non-deterministic.


    I ran the script I posted successfully on SQL 2005, SQL 2008, SQL 2008 R2 and SQL 2012.  Can you post your DDL?


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

    Wednesday, August 21, 2013 11:36 PM
  • Sorry Dan. I had personal emergency so I couldnt answer your question. I had problem creating partition on SS 2012 R2 so I created as regular column

    but when I fill it then I will use that function to create productID+date partition.  Should I also create partition function?

    Tuesday, September 03, 2013 1:24 PM
  • I had problem creating partition on SS 2012 R2 so I created as regular column

    but when I fill it then I will use that function to create productID+date partition.  Should I also create partition function?

    If I understand correctly, you were having problems with the computed column so you instead created a normal column and specified the concatenated value on insert statements.  That should work as well but you will of course need to specify a partition function like the one I provided along with a partition scheme on the new column.


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

    Wednesday, September 04, 2013 10:58 AM