2

I want to create a partitioned table to store messages.

If the message belongs to "an even week number" => go to the partEven partition ! If the message belongs to "an odd week number" => go to the partOdd partition !

Here is my broken code :

CREATE TABLE myTable (
    id NUMERIC(18,0) IDENTITY,
    message_date_time DATETIME,
    PRIMARY KEY (id)
    )partition by list (message_date_time)
        (partEven datepart(week, message_date_time) % 2 == 0,
         partOdd datepart(week, message_date_time) % 2 == 1
        )

Could someone help me with the syntax ? (if it's possible this way)

It seems that I'm only allowed to route records on the "message_date_time" constant but I want to do it on the result of a function applied to "message_date_time".

Please help :)

1 Answer 1

0

You cannot write your own partition function. What you need to do is create an additional column, assign a value resulting from the function you want to use, and partition on that column. Unfortunately, you cannot partition on a computed column, which would be the easiest solution.

2
  • Yes I tried this solution, the result is something like this "cannot create a partition using an encrypted/computed column" Jun 21, 2016 at 10:49
  • WHen you create your column as a normal column (not a computed column)< you can partition on it.
    – RobV
    Jun 21, 2016 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.