none
Table partition on identity column

    Question

  • Hello All,

    I have table which are having like 5millions records in it. I would like to do table partition in order to increase performance. Can anyone please let me know how to do partition on identity column which primary key for that table (data type INT), because i dont have any date column in my table so i guess i can use only identity column.

    Thanks

    Wednesday, July 24, 2013 1:59 PM

Answers

  • Hi banty1,

    You can refer to the following codes. This is just for testing and I assigned the value ranges as: 1,2,3, please modify them according to your requirements.

    use master;
    go
    create database demo
     
    ALTER DATABASE [demo] ADD FILEGROUP [fg1]
    GO
    ALTER DATABASE [demo] ADD FILEGROUP [fg2]
    GO
    ALTER DATABASE [demo] ADD FILEGROUP [fg3]
    GO
    ALTER DATABASE [demo] ADD FILEGROUP [fg4]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg1', FILENAME = N'E:\Data\fg1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg1]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg2', FILENAME = N'E:\Data\fg2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg3', FILENAME = N'E:\Data\fg3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg3]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg4', FILENAME = N'E:\Data\fg4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg4]
    GO
    use demo;
    go
    CREATE PARTITION FUNCTION MyIdentityPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) 
    CREATE PARTITION SCHEME MyIdentityPartitionScheme AS PARTITION MyIdentityPartitionRange TO ([fg1],[fg2],[fg3],[fg4]) 
    CREATE TABLE MyIdentityPartitionedTable ( i INT IDENTITY (1,1) primary key , s CHAR(10) , PartCol INT ) ON MyIdentityPartitionScheme ( i) 
    --And we add some data 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 1 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 2 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'b', 1 
    INSERT MyIdentityPartitionedTable ( s, PartCol) SELECT 'b', 2 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 3
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 4 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'b', 5 
    INSERT MyIdentityPartitionedTable ( s, PartCol) SELECT 'b', 6 
    SELECT * FROM MyIdentityPartitionedTable
    /*
    1	a         	1
    2	a         	2
    3	b         	1
    4	b         	2
    5	a         	3
    6	a         	4
    7	b         	5
    8	b         	6
    */
    select OBJECT_NAME(object_id) as TableName,partition_number,rows from sys.partitions p 
    where OBJECT_NAME(object_id) = 'MyIdentityPartitionedTable'
    /*
    MyIdentityPartitionedTable	1	1
    MyIdentityPartitionedTable	2	1
    MyIdentityPartitionedTable	3	1
    MyIdentityPartitionedTable	4	5
    */
    drop TABLE MyIdentityPartitionedTable
    drop PARTITION SCHEME MyIdentityPartitionScheme
    drop PARTITION FUNCTION MyIdentityPartitionRange
    use master;
    go
    drop database demo;

    For more detail information, you can refer to the following links:

    CREATE PARTITION FUNCTION (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187802.aspx

    CREATE PARTITION SCHEME (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms179854.aspx



    Allen Li
    TechNet Community Support

    Friday, July 26, 2013 8:03 AM

All replies

  • Show us the table definition.

    What is the performance problem you are trying to solve?

    To partition a table you need to write a function that uses a table column to determine what partition to put a row into. It is unlikely that an identity column will provide that information but it is not possible to know without knowing what your performance problem is?


    Tom G.

    Wednesday, July 24, 2013 3:03 PM
  • Tom it has almost 100 columns in it and performance issue as we have increased our execution so that table will be increasing every month by 0.5 millions from nowonwards.
    Wednesday, July 24, 2013 3:10 PM
  • Is the performance a problem inserting, selecting, deleting?

    With 100 columns I suspect partitioning will not solve what ever your performance problem is. Publish the table definition so somebody here can provide a reasonable answer.


    Tom G.

    Wednesday, July 24, 2013 3:58 PM
  • performance issue with insert and delete

    CREATE TABLE tablename(

    [InternalID] [int] IDENTITY(1,1) NOT NULL,
    [ID] [int] NULL,
    [SeqNum] [nvarchar](25) NULL,
    [Claim] [nvarchar](25) NULL,
    [ChargeLine] [nvarchar](255) NULL,
    [OriginalLine] [nvarchar](255) NULL,
    [ServiceFrom] [nvarchar](255) NULL,
    [ServiceTo] [nvarchar](255) NULL,
    [PlaceServ] [nvarchar](255) NULL,
    [ProcCode1] [nvarchar](255) NULL,
    [Modifier1] [nvarchar](255) NULL,
    [Modifier2] [nvarchar](255) NULL,
    [Modifier3] [nvarchar](255) NULL,
    [Modifier4] [nvarchar](255) NULL,
    [DiagnosisCodePointer1] [nvarchar](255) NULL,
    [DiagnosisCodePointer2] [nvarchar](255) NULL,
    [DiagnosisCodePointer3] [nvarchar](255) NULL,
    [DiagnosisCodePointer4] [nvarchar](255) NULL,
    [TotalCharges] [nvarchar](255) NULL,
    [CoveredCharges] [nvarchar](255) NULL,
    [ServUnits] [nvarchar](255) NULL,
    [ServCode] [nvarchar](255) NULL,
    [RemotePricing] [nvarchar](255) NULL,
    [PricingAllowance] [nvarchar](255) NULL,
    [BundledLine] [nvarchar](255) NULL,
    [EditOverrideCode1] [nvarchar](255) NULL,
    [EOBRemarkCode1] [nvarchar](255) NULL,
    [COBInvestigation] [nvarchar](255) NULL,
    [OtherCarrierAllowance] [nvarchar](255) NULL,
    [OtherCarrierAmountPaid] [nvarchar](255) NULL,
    [OtherCarrierIndicator] [nvarchar](255) NULL,
    [MedicarePaymentDisposition] [nvarchar](255) NULL,
    [MedicareParticipationInd] [nvarchar](255) NULL,
    [COBPricingIndicatorSecondary] [nvarchar](255) NULL,
    [MedRev] [nvarchar](255) NULL,
    [ConCurrCare] [nvarchar](255) NULL,
    [DateOfAccident] [nvarchar](255) NULL,
    [AdjustmentReason] [nvarchar](255) NULL,
    [DonorRecipient] [nvarchar](255) NULL,
    [MacScheduleIdentifier] [nvarchar](255) NULL,
    [ToothNum] [nvarchar](255) NULL,
    [ToothSurf] [nvarchar](255) NULL,
    [DeductibleAmt] [nvarchar](255) NULL,
    [ClaimCopaymentCoinsuranceAmt] [nvarchar](255) NULL,
    [ClaimFEPAmtPaid] [nvarchar](255) NULL,
    [TypeSavingsAmount1] [nvarchar](255) NULL,
    [TypeSavingsAmount2] [nvarchar](255) NULL,
    [TypeSavingsAmount3] [nvarchar](255) NULL,
    [TypeSavingsAmount4] [nvarchar](255) NULL,
    [TypeSavingsCode1] [nvarchar](255) NULL,
    [TypeSavingsCode2] [nvarchar](255) NULL,
    [TypeSavingsCode3] [nvarchar](255) NULL,
    [TypeSavingsCode4] [nvarchar](255) NULL,
    [ClaimPend] [nvarchar](255) NULL,
    [NoOfDays] [nvarchar](255) NULL,
    [MHSAPlanVisits] [nvarchar](255) NULL,
    [MHSAPlanType] [nvarchar](255) NULL,
    [MHSAPlanBeginDate] [nvarchar](255) NULL,
    [MHSAPlanEndDate] [nvarchar](255) NULL,
    [MHSAPlanNote] [nvarchar](255) NULL,
    [RevenueCode] [nvarchar](255) NULL,
    [NonCoveredCharges] [nvarchar](255) NULL,
    [PricingMethod1] [nvarchar](255) NULL,
    [PricingAmount1] [nvarchar](255) NULL,
    [PricingPercentage1] [nvarchar](255) NULL,
    [DrgNumber] [nvarchar](255) NULL,
    [PrivateRoomCovered] [nvarchar](255) NULL,
    [HospitalBillAuditSavingsIndicator] [nvarchar](255) NULL,
    [F70] [nvarchar](255) NULL,
    [HospitalBillAuditAmount] [nvarchar](255) NULL,
    [CodeModifier2] [nvarchar](255) NULL,
    [CodeModifier3] [nvarchar](255) NULL,
    [PlaceofTreatment] [nvarchar](255) NULL,
    [DiagnosisCodes1] [nvarchar](255) NULL,
    [DiagnosisCodes2] [nvarchar](255) NULL,
    [AreaOfOralCavity] [nvarchar](255) NULL,
    [ToothSystem] [nvarchar](255) NULL,
    [ToothOrRange] [nvarchar](255) NULL,
    [ToothRangeBegin] [nvarchar](255) NULL,
    [ToothRangeEnd] [nvarchar](255) NULL,
    [ToothNumbersOrLetters1] [nvarchar](255) NULL,
    [ToothNumbersOrLetters2] [nvarchar](255) NULL,
    [ToothNumbersOrLetters3] [nvarchar](255) NULL,
    [ToothNumbersOrLetters4] [nvarchar](255) NULL,
    [ToothSurface1] [nvarchar](255) NULL,
    [ToothSurface2] [nvarchar](255) NULL,
    [ToothSurface21] [nvarchar](255) NULL,
    [ToothSurface3] [nvarchar](255) NULL,
    [ToothSurface4] [nvarchar](255) NULL,
    [ToothSurface5] [nvarchar](255) NULL,
    [Pricing1Method] [nvarchar](255) NULL,
    [Pricing1Amount] [nvarchar](255) NULL,
    [Pricing1Percentage] [nvarchar](255) NULL,
    [PrivateRMind] [nvarchar](255) NULL,
    [CodeModifier4] [nvarchar](255) NULL,
    [Modifiers1] [nvarchar](255) NULL,
    [Modifiers2] [nvarchar](255) NULL,
    [Modifiers3] [nvarchar](255) NULL,
    [Modifiers4] [nvarchar](255) NULL,
    [Allowance] [nvarchar](255) NULL,
    [AmountPaid] [nvarchar](255) NULL,
    [Indicator] [nvarchar](255) NULL,
    [ProvMedicareParticipationIndicator] [nvarchar](255) NULL,
    [code1] [nvarchar](255) NULL,
    [Code2] [nvarchar](255) NULL,
    [Code3] [nvarchar](255) NULL,
    [Code4] [nvarchar](255) NULL,
    [Code5] [nvarchar](255) NULL,
    [Code6] [nvarchar](255) NULL,
    [Code7] [nvarchar](255) NULL,
    [Code8] [nvarchar](255) NULL,
    [ServiceBeginDate] [nvarchar](255) NULL,
    [TotalChargesAllService] [nvarchar](255) NULL,
    [ClaimPriceLine] [nvarchar](255) NULL,
    [COBAllowance] [nvarchar](255) NULL,
    [COBAmountPaid] [nvarchar](255) NULL,
    [COBPricingIndicatorforSecondary] [nvarchar](255) NULL,
    [ServiceGroup] [nvarchar](255) NULL,
    [ServiceID] [nvarchar](255) NULL,
    [BenefitID] [nvarchar](255) NULL,
    [CatastrophicIndi] [nvarchar](255) NULL,
    [CircumstanceID] [nvarchar](255) NULL,
    [CreatedDate] [datetime] NULL,
     CONSTRAINT [PK_Cha] PRIMARY KEY CLUSTERED 
    (
    [InternalID] ASC
    )
    Wednesday, July 24, 2013 4:26 PM
  • The first thing you need to do is split this table into multiple tables. Every place where you have columns numbered 1 -- n, those columns need to be in a separate table. Simply partitioning this table will not solve your undefined performance problem.

    This is a  very old but still excellent presentation of normal forms.

    http://www.bkent.net/Doc/simple5.htm

    You need to study this and redesign your table.


    Tom G.

    Wednesday, July 24, 2013 4:59 PM
  • Still can you please provice me something that will take care of partition on identity column which is primary key
    Wednesday, July 24, 2013 6:01 PM
  • Hi banty1,

    You can refer to the following codes. This is just for testing and I assigned the value ranges as: 1,2,3, please modify them according to your requirements.

    use master;
    go
    create database demo
     
    ALTER DATABASE [demo] ADD FILEGROUP [fg1]
    GO
    ALTER DATABASE [demo] ADD FILEGROUP [fg2]
    GO
    ALTER DATABASE [demo] ADD FILEGROUP [fg3]
    GO
    ALTER DATABASE [demo] ADD FILEGROUP [fg4]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg1', FILENAME = N'E:\Data\fg1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg1]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg2', FILENAME = N'E:\Data\fg2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg3', FILENAME = N'E:\Data\fg3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg3]
    GO
    ALTER DATABASE [demo] ADD FILE ( NAME = N'fg4', FILENAME = N'E:\Data\fg4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg4]
    GO
    use demo;
    go
    CREATE PARTITION FUNCTION MyIdentityPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) 
    CREATE PARTITION SCHEME MyIdentityPartitionScheme AS PARTITION MyIdentityPartitionRange TO ([fg1],[fg2],[fg3],[fg4]) 
    CREATE TABLE MyIdentityPartitionedTable ( i INT IDENTITY (1,1) primary key , s CHAR(10) , PartCol INT ) ON MyIdentityPartitionScheme ( i) 
    --And we add some data 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 1 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 2 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'b', 1 
    INSERT MyIdentityPartitionedTable ( s, PartCol) SELECT 'b', 2 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 3
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 4 
    INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'b', 5 
    INSERT MyIdentityPartitionedTable ( s, PartCol) SELECT 'b', 6 
    SELECT * FROM MyIdentityPartitionedTable
    /*
    1	a         	1
    2	a         	2
    3	b         	1
    4	b         	2
    5	a         	3
    6	a         	4
    7	b         	5
    8	b         	6
    */
    select OBJECT_NAME(object_id) as TableName,partition_number,rows from sys.partitions p 
    where OBJECT_NAME(object_id) = 'MyIdentityPartitionedTable'
    /*
    MyIdentityPartitionedTable	1	1
    MyIdentityPartitionedTable	2	1
    MyIdentityPartitionedTable	3	1
    MyIdentityPartitionedTable	4	5
    */
    drop TABLE MyIdentityPartitionedTable
    drop PARTITION SCHEME MyIdentityPartitionScheme
    drop PARTITION FUNCTION MyIdentityPartitionRange
    use master;
    go
    drop database demo;

    For more detail information, you can refer to the following links:

    CREATE PARTITION FUNCTION (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187802.aspx

    CREATE PARTITION SCHEME (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms179854.aspx



    Allen Li
    TechNet Community Support

    Friday, July 26, 2013 8:03 AM