database partiton Function (sql server 2008)

Question

HI, I want to write partiton in sql server 2008 for my fleet management system. I want to write a partition function such that --values (vehicle number) like for example mh-30-q-126,mh-30-a-126,mh-12-fc-126 should be moved to respective partiton, depending upon middle values like ,q,a,fc respectively

My trial function:-

CREATE PARTITION FUNCTION [partition](varchar(20))
 AS RANGE RIGHT  
FOR VALUES ('%a%', '%G%', '%Z%')

this function moves all(every value) data to partition 4 .

 CREATE PARTITION FUNCTION [partition](varchar(20))
 AS RANGE RIGHT 
FOR VALUES ('a', 'G', 'Z')

This partition does not satisfy me in terms where i want to sort values which are in middle eg.mh-30-q-126 ..here i want to target value "q" to move to respective partion,,, plz tell tell me how to write this function

Answer 1

You can't directly because of how PARTITION works

"boundary_value" is a constant and you can't use LIKE.

All I can suggest is a computed column that extracts the values you want and you partition on that. However, you may have values which don't lend themselves to consistent parsing.

mh-30-q-126
mh-30-a-126
mh-12-fc-126

Note: I've not tried this. I'd normally partition on a date or something like that.

However, I'd also not partition unless I really had to, like 100 million rows+ because of implied overhead in partitioning. Do you need to partition?