Partitioning line items in SQL Server-CodePudding

There are three values for column named custom_value, Viable, Non-Viable or none. I want to partition the Viable values because it can have multiple Viables. (To understand this Viable choice is just multiple forms of a paper to be approved for a process. A person can apply multiple times and be accepted multiple times).

Select *
From Sample.database.tbl_custom
Where ben_ID in ('568764','798426') AND ID = '123456'
Order By ID;

Create Partition Function viable_non_viable_line_items(char20)
--I am stuck with what should I do to partition off these multiple forms of a paper.

CodePudding user response：

SQL Server partition supports a single column only as a Partition Key. If you want to support multiple columns in a Partition Key then you have to define the computed column first then apply the partitioning on that column.

CodePudding user response：

CREATE PARTITION FUNCTION PF_VIALBILITY (CHAR(20))
AS RANGE LEFT 
FOR VALUES ('Viable', 'Non-Viable' 'none')

To enforce the partitionning mechanism, add a CHECK constraint to the table as :

ALTER tbl_custom 
   ADD CONSTRAINT CK_VIALBILITY
   CHECK (custom_value IN ('Viable', 'Non-Viable' 'none'));