partition a table based on VersionNumber which is Binary(8) RRS feed

  • Question

  • We have a very huge table and lloading the delta based on the VersionNumber  column. we wanted to partition the table based on this VersionNumber   which is of data type binary(8). can someone help me with the ranges of binary I can split the partition schema to.
    Thursday, November 26, 2015 9:55 AM


  • Hi Srikanth,

    Firstly, binary(8) is an timestamp data type, and based on my test, we can’t partition on the timestamp column. Because columns that participate in a partition function must be explicitly marked PERSISTED. However, the value in the timestamp column is updated every time when a row containing a timestamp column is inserted or updated.

    Secondly, you can count the records in your table using  the following code, then determine that how many file groups you want to split table into and the range value in every file group according to records and data size in the table. Generally, you can averagely distribute records to partitions. For more information about partition, please refer to this article .

    Select count(*) from table

    Besides, you can follow the steps in this blog  to partition an existing table in SQL Server.

    Ice Fan 

    Ice Fan
    TechNet Community Support

    Monday, November 30, 2015 8:01 AM