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

gbn · Accepted Answer · 2009-11-02 09:15:34Z

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?

answered Nov 2, 2009 at 9:15

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

database partiton Function (sql server 2008)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related