{"id":680,"date":"2005-05-15T15:20:00","date_gmt":"2005-05-15T15:20:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Clarifying-LEFT-and-RIGHT-in-the-defintion-of-a-PARTITION-FUNCTION-in-SQL-Server-2005.aspx"},"modified":"2013-01-11T23:13:47","modified_gmt":"2013-01-12T07:13:47","slug":"clarifying-left-and-right-in-the-defintion-of-a-partition-function-in-sql-server-2005","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/clarifying-left-and-right-in-the-defintion-of-a-partition-function-in-sql-server-2005\/","title":{"rendered":"Clarifying LEFT and RIGHT in the defintion of a PARTITION FUNCTION in SQL Server 2005"},"content":{"rendered":"

\nNOTE: <\/strong>Generally, I recommend RIGHT-based partition function so that you don't have to deal with datetime timetick issues at all. However, this post can really help you if you still want to use LEFT-based partition functions. Enjoy!!! \n<\/p>\n

\nWhen creating partitioned tables in SQL Server 2005, a partition function requires a LEFT or RIGHT designation. In general, I recommend that you choose to create a LEFT-based partition. However, choosing LEFT always creates some confusion because a left-based partition function requires upper boundaries. This results in a more complicated partition function definition (than a RIGHT partition function).\n<\/p>\n

\nBackground First<\/strong>
\nIn SQL Server 2005, you can create truly Partitioned Objects (objects are inclusive to Tables and Indexes) and to create a partitioned table you must base that table on a Partition Scheme (PS) and the PS must be based on a Partition Function (PF). Now, if you're new to the concept of partitioning, you should seriously consider reading the whitepaper I wrote for MSDN which is posted here<\/a>.\n<\/p>\n

\nRegardless of your having read the whitepaper, I want to add a few basic principles and make sure that some of the key points are clear. Simply put, a PF is the logical definition of your boundary cases (or possibly better described as the intervals over which each partition covers). A PF must cover the entire domain from negative infinity to positive infinity. If a value doesn't have a place to go then you'd have a problem with the logic of your partition function. Having said that, when you define the PF you are ONLY stating values, not where they will physically will reside. Futhermore, the expression on which the data will be based has to be somewhat straightforward and consistent. In other words, simple. If the PF were extremely complex it could compromise the usage and performance of the Partitioned Object. Finally, the definition of the PF is really just a way to easily define these boundaries. As a result, you can choose to enter a specific value (like June 1, 2005) or a function like the following to create a PF over 4 months with each month being computed from current:\n<\/p>\n

\nCREATE PARTITION FUNCTION TestFunctionValues(datetime)
\nAS
\nRANGE RIGHT FOR VALUES (
\n   CONVERT(char(4), year(dateadd(m, 1, getdate()))) + right('0' + convert(char(1), month(dateadd(m, 1, getdate()))), 2) + '01'
\n    — next month (executed in May, 2005 yields June 2005)
\n   , CONVERT(char(4), year(dateadd(m, 2, getdate()))) + right('0' + convert(char(1), month(dateadd(m, 2, getdate()))), 2) + '01'
\n    — executed in May, 2005 yields July 2005
\n   , CONVERT(char(4), year(dateadd(m, 3, getdate()))) + right('0' + convert(char(1), month(dateadd(m, 3, getdate()))), 2) + '01'
\n    — executed in May, 2005 yields August 2005
\n   , CONVERT(char(4), year(dateadd(m, 4, getdate()))) + right('0' + convert(char(1), month(dateadd(m, 4, getdate()))), 2) + '01'
\n    — executed in May, 2005 yields September 2005
\n)
\nGO\n<\/p>\n

\nSince all of these complex expressions are evaluated to a constant at creation, you can immediately verify your boundary cases by accessing some catalog views. Here is the catalog view query which helps you see what your partition boundaries are:\n<\/p>\n

\nSELECT PF.[name], RV.boundary_id, RV.[value]
\nFROM sys.partition_range_values AS RV
\n JOIN sys.partition_functions AS PF
\n  ON RV.function_id = PF.function_id
\nWHERE PF.[name] = 'TestFunctionValues'\n<\/p>\n

\nIn the above partition function, you'll notice that in addition to the values, I also have a RIGHT designation. The choice of using RIGHT, helps to target whether or not the value is a member of the first partition or the second partition. In the case of RIGHT, this says that the value is a lower boundary of the second partition. In an example that involves something like "sales" where data rows are ever increasing and their usage is for more recent rather than older data you migth want to always have the most recent four months of data available (in the real world you'd probably want more than 4 months but I'm going to simplify to four so that we have fewer boundaries with which to work). So, if we want 4 partitions – one for June, July, August and September – we need to define the partitions in the form of intervals (using boundaries). The first boundary point will either reside in the first partition (as an upper boundary) or the second partition (as a lower boundary). For four partitions, there are numerous ways in which you could define the partition function such that the logical\/physical placement of the data is THE SAME however, the behavior of the partition function during later SPLIT and MERGE operations is also impacted by the choice of LEFT\/RIGHT so understanding how they work is critical in making your decision to use one or the other. More than anything, will need to respect the full domain from negative infinity to positive infinity.\n<\/p>\n

\nThe following partition function is one of the easiest and most logical to understand. We need four partitions so let's just enter our boundary points and be done.\n<\/p>\n

\nCREATE PARTITION FUNCTION RightPartitionFunction1(datetime)
\nAS
\nRANGE RIGHT FOR VALUES (
\n   '20040601', — Jun 2004
\n   '20040701', — Jul 2004
\n   '20040801', — Aug 2004
\n   '20040901' — Sep 2004
\n)\n<\/p>\n

\nHowever, remember that we have the full domain to cover. As a result, the above partition function will create 5 partitions where the partitions will be:
\n Partition 1: all data less than 20040601
\n Partition 2: all data greater then\/equal to 20040601 and less than 20040701
\n Partition 3: all data greater then\/equal to 20040701 and less than 20040801
\n Partition 4: all data greater then\/equal to 20040801 and less than 20040901
\n Partition 5: all data greater then\/equal to 20040901\n<\/p>\n

\nThis really isn't too bad but it seems to waste the first partition because it's completely empty… so, you could remove the first boundary point and just supply the second, third and forth. In that case, the first partition would cover June (instead of being empty) and you could get down to 4 partitions with only 3 boundary points.\n<\/p>\n

\nCREATE PARTITION FUNCTION RightPartitionFunction2(datetime)
\nAS
\nRANGE RIGHT FOR VALUES (
\n   '20040701', — Jul 2004
\n   '20040801', — Aug 2004
\n   '20040901' — Sep 2004
\n)\n<\/p>\n

\nThis will create 4 partitions where the partitions will be:
\n Partition 1: all data less than 20040701
\n Partition 2: all data greater then\/equal to 20040701 and less than 20040801
\n Partition 3: all data greater then\/equal to 20040801 and less than 20040901
\n Partition 4: all data greater then\/equal to 20040901\n<\/p>\n

\nOK, so this seems a bit more optimal. And by definition alone, I would agree with you. Where it becomes problematic is in the merge and split operations. When a partition function goes through a merge of a boundary point, that boundary point is essentially removed. This seems simple but also has an impact on the data which resides in that partition (the partition which includes that boundary point). So, if this partitioned table is NEVER going to be modified and you never need to plan for a merge or split, then you can choose whatever definition is easier for you to use. However, if you plan to do a merge or split, you need to understand the ramifications of the LEFT\/RIGHT decision. If November 1 comes around and you want the data for October to be added to your partitioned table and you ONLY want to show the most four months, then you will need to add October and remove June. The process will essentially consist of the following:\n<\/p>\n

    \n
  1. Create a place for new data (Oct) to reside <\/li>\n
  2. Add this location to the partition scheme (more details on the scheme v. the function shortly) <\/li>\n
  3. Split the last boundary to add the new month (Oct) <\/li>\n
  4. Switch-in the staging table of October data so it becomes part of the partitioned table <\/li>\n
  5. Create a staging table into which the old data (Jun) will go <\/li>\n
  6. Switch-out the June data into the staging table <\/li>\n
  7. Merge the boundary point to remove June from the table<\/li>\n<\/ol>\n

    \nMore background first – Partition Schemes
    \n<\/strong>You will want to have a place to put the new data (there are lots of things we could look at here… do you want to ALWAYS only use 4 filegroups and when you remove June's data do you want to reuse that space – or do you have a new location on which October can be placed). This is a whole different topic over which we should chat… but for simplicity let's just say that we want to use a new location. To tell the PF where this location is, you need to work with the physical definition of the partition function. OK, that might not be clear. So far, I've only told you about the PF and so far, I've only said that the PF described the logical boudaries. You may have been wondering how SQL Server maps these logical boundaries to the phyiscal files and the answer is through the Partition Scheme. The partition scheme (at initial creation) sets up the mapping of the boundaries to their phyiscal locations (through filegroups). For the first and second PFs created above the respective partition schemes would be:\n<\/p>\n

    \nCREATE PARTITION SCHEME [PartitionScheme1]
    \nAS
    \nPARTITION [RightPartitionFunction1] TO
    \n  ( [Primary]    — should be empty?
    \n  , [PartitionedTablesFG1]  — for June
    \n  , [PartitionedTablesFG2]  — for July
    \n  , [PartitionedTablesFG3]  — for Aug
    \n  , [PartitionedTablesFG4]  — for Sept
    \n  )
    \nand\/or\n<\/p>\n

    \nCREATE PARTITION SCHEME [PartitionScheme2]
    \nAS
    \nPARTITION [RightPartitionFunction2] TO
    \n  ( [PartitionedTablesFG1]  — for June
    \n  , [PartitionedTablesFG2]  — for July
    \n  , [PartitionedTablesFG3]  — for Aug
    \n  , [PartitionedTablesFG4]) — for Sept\n<\/p>\n

    \nIn both cases, it seems as though these do the same thing with the exception that the first PF ends up with an extra partition which is essentially empty. In this <\/strong>case (in a RIGHT-based PF), the second is a better choice (coming up there will be a reason for why we create an empty partition).\n<\/p>\n

    \nBack to SPLIT and MERGE
    \n<\/strong>OK, so back to the issue of spliting and merging. Our goal is to remove June and add October so that the "current" data shows the four months of July, Aug, Sept and Oct. If we want to create a location on which October can be placed, we need to add another filegroup to the scheme. The way we do this is that we first need to add the filegroup\/file to the database and then we need to add this new filegroup to our partition scheme.\n<\/p>\n

    \nStep 1 – Create a place for new data (Oct) to reside
    \n<\/strong>Create a new filegroup and new file.\n<\/p>\n

    \nALTER DATABASE CreditPT
    \n ADD FILEGROUP PartitionedTablesFG5
    \nGO\n<\/p>\n

    \nALTER DATABASE CreditPT
    \n ADD FILE
    \n  (NAME = N'CreditPTFG5File1',
    \n  FILENAME = N'C:\\SQLTemp\\CreditPTFG5File1.ndf',
    \n  SIZE = 30, FILEGROWTH = 10, MAXSIZE = 50)
    \n  TO FILEGROUP [PartitionedTablesFG5]
    \nGO\n<\/p>\n

    \nStep 2 – Add this location to the partition scheme
    \n<\/strong>The way to do this is to add a "NEXT USED" filegroup. Syntax:\n<\/p>\n

    \nALTER PARTITION SCHEME PartitionScheme2 NEXT USED [PartitionedTablesFG5]
    \nGO\n<\/p>\n

    \nOnce the partition scheme has a new filegroup to use (and you can only have one at a time – note that if you add a second it solely replaces the first and does not warn you that you already had a next used), then the next time a partition is needed (which will be on the next split) then this "next used" partition will be used.\n<\/div>\n

    \nStep 3 – Split the last boundary to add the new month (Oct)
    \n<\/strong>Now that we have a filegroup AND we have told the partition scheme to use it for the next split operation, we can split the PS to include this 5th filegroup\n<\/p>\n

    \nALTER PARTITION FUNCTION RightPartitionFunction2()
    \nSPLIT RANGE ('20041001')
    \nGO\n<\/p>\n

    \nStep 4 – Switch-in the staging table of October data so it becomes part of the partitioned table
    \n<\/strong>This step really needs more to it. To switch in your staging table you need to already have a staging table. To make this an optimal operation, you MUST create your staging table on the same fielgroup on which the partition will reside. What you're trying to do is make the switch optimal by making it a metadata only operation. This step really consists of creating an identical table with identical indexes and creating it with a constraint that matches the partitions' definition (so that SQL Server can trust that it only includes data within the interval for that partition).\n<\/p>\n

    \nCREATE TABLE ChargesPTRightOct
    \n(  charge_no  int   NOT NULL IDENTITY,
    \n member_no  int   NOT NULL
    \n      CONSTRAINT ChargesPTRightOctMemberNoFK
    \n       REFERENCES dbo.Member(Member_No),
    \n provider_no  int   NOT NULL
    \n      CONSTRAINT ChargesPTRightOctProviderNoFK
    \n       REFERENCES dbo.Provider(Provider_No),
    \n category_no  int   NOT NULL
    \n      CONSTRAINT ChargesPTRightOctCategoryNoFK
    \n       REFERENCES dbo.Category(Category_No),
    \n charge_dt  datetime  NOT NULL
    \n      CONSTRAINT ChargesPTRightOctChargeDtCK
    \n       CHECK (Charge_dt >= '20041001'
    \n         AND Charge_dt < '20041101'),
    \n charge_amt  money  NOT NULL,
    \n statement_no int   NOT NULL,
    \n charge_code  char(2)  NOT NULL
    \n) ON [PartitionedTablesFG5]
    \nGO\n<\/p>\n

    \nALTER TABLE ChargesPTRightOct
    \nADD CONSTRAINT ChargesPTRightOctPK
    \n  PRIMARY KEY CLUSTERED (charge_dt, charge_no)
    \n—   ON Credit4MonthPFN (charge_dt)
    \nGO\n<\/p>\n

    \nINSERT ChargesPTRightOct (member_no, provider_no, category_no
    \n      , charge_dt, charge_amt
    \n      , statement_no, charge_code)
    \n SELECT member_no, provider_no, category_no
    \n   , dateadd(yy, 5, charge_dt), (charge_amt + charge_no)\/10
    \n   , statement_no, charge_code
    \n FROM CreditPT.dbo.Charge
    \n WHERE month(charge_dt) IN (10)
    \n ORDER BY charge_dt, charge_no
    \nGO\n<\/p>\n

    \nALTER TABLE ChargesPTRightOct
    \nSWITCH TO ChargesPTRight PARTITION 5
    \nGO\n<\/p>\n

    \nStep 5 – Create a staging table into which the old data (Jun) will go
    \n<\/strong>Similarly, you will want to create a place for the June data to go – kind of like a “staging” table but more for the exit of the table.\n<\/p>\n

    \nCREATE TABLE ChargesPTRightJun
    \n(  charge_no  int   NOT NULL IDENTITY,
    \n member_no  int   NOT NULL
    \n      CONSTRAINT ChargesPTRightJunMemberNoFK
    \n       REFERENCES dbo.Member(Member_No),
    \n provider_no  int   NOT NULL
    \n      CONSTRAINT ChargesPTRightJunProviderNoFK
    \n       REFERENCES dbo.Provider(Provider_No),
    \n category_no  int   NOT NULL
    \n      CONSTRAINT ChargesPTRightJunCategoryNoFK
    \n       REFERENCES dbo.Category(Category_No),
    \n charge_dt  datetime  NOT NULL,
    \n charge_amt  money  NOT NULL,
    \n statement_no int   NOT NULL,
    \n charge_code  char(2)  NOT NULL
    \n) ON [PartitionedTablesFG1]
    \nGO\n<\/p>\n

    \nALTER TABLE ChargesPTRightJun
    \nADD CONSTRAINT ChargesPTRightJunPK
    \n  PRIMARY KEY CLUSTERED (charge_dt, charge_no)
    \n—   ON Credit4MonthPFN (charge_dt)
    \nGO\n<\/p>\n

    \nStep 6 – Switch-out the June data into the staging table
    \n<\/strong>This must also be a metadata only step. You need to make sure that it's created on the same filegroup as where the data currently resides (see the ON [ParitionedTablesFG1] clause. This is the current location of this partition. As a result, the switch out will be very fast.\n<\/p>\n

    \nALTER TABLE ChargesPTRight
    \nSWITCH PARTITION 1
    \nTO ChargesPTRightJun
    \nGO\n<\/p>\n

    \nStep 7 – Merge the boundary point to remove June from the table
    \n<\/strong>This last step is the whole reason for this blog entry… yes, we're getting there ;).\n<\/p>\n

    \nALTER PARTITION FUNCTION RightPartitionFunction2()
    \nMERGE RANGE ('20040701')
    \nGO\n<\/p>\n

    \nWhen you merge this boundary point, you will remove the value AND the parition in which it resides. Since this boundary point resides in the second partition (PartitionedTablesFG2) then it's actually PartitionedTablesFG2 that gets removed. As a result ALL of the data that resides on it, must be moved to the filegroup that now covers this boundary (which is PartitionedTablesFG1 and which was just emptied). As a result, MERGE does NOT optimally merge with a RIGHT-based partition on the rolling range scenario.\n<\/p>\n

    \nAnd – as a result, I recommend LEFT-based partition functions… Since the whitepaper tends to focus on using LEFT-based parition functions, please review that for more details. You can access the “Paritioned Tables” whitepaper on MSDN, here<\/a>.\n<\/p>\n

    \nHope this helps and thanks for reading!
    \nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

    NOTE: Generally, I recommend RIGHT-based partition function so that you don't have to deal with datetime timetick issues at all. However, this post can really help you if you still want to use LEFT-based partition functions. Enjoy!!!  When creating partitioned tables in SQL Server 2005, a partition function requires a LEFT or RIGHT designation. In general, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25,27,49,51,65,78],"tags":[],"class_list":["post-680","post","type-post","status-publish","format-standard","hentry","category-design","category-events","category-opinions","category-partitioning","category-sql-server-2005","category-tips"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/680","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=680"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/680\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=680"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}