Dynamic Allocation Into Table Partition

Hi,

I am trying to partition the table i have into 10 partitions based on the last digit of the employee ID.
The following is the logic I tried to use but was not successful.



Code Snippet
CREATE PARTITION FUNCTION PartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8)


CREATE PARTITION SCHEME PartitionScheme AS
PARTITION PartitionRange
ALL TO ([PRIMARY])

create TABLE PartitionedTable
(
counter INT ,
string CHAR(8000) ,
EmpID INT,
UpdateDate datetime
)
ON
PartitionScheme (EmpID % 10)





Any suggestions on how I can make the logic work as required.


Thanks in advance.

With regards,