SQL Server Table partitioning

Question

I have some old project which has multiple databases with same structure. There is database for each language supported, so I have 4 databases with same structure. I think this is not efficient and it's hard to support and make updates to databases, so I want to create one database which will have new table Languages with columns id and code and for each table add foreign key to this table. It should work well, but I also would like to partition every table to separate filegroup based on language.

So for example I have old table Customers with structure (id, name, surname, data), so in new database it will have (id, name, surname, data, langId).

So I want to have something like customer_eng, customer_de, customer_it filegroups based on langId.

Is it possible to do? I have found partitioning function and scheme, but there are only possibility to define some range values, but I don't need ranges, I want to group data to separate files based on langId, that's all what I want to do. If it's possible I would like to make it dynamic, so when new langId (new language added) is added it will automatically create new file for this language.

Although SQL Server technically provides only range partitioning, you can accomplish the desired result by specifying the desired list as a range. You can also add a check constraint to match the list to ensure only exact values are inserted, although that is not needed in this case due to the foreign key to the Languages table. — Dan Guzman
– Dan Guzman, Commented Sep 3, 2014 at 11:49

Gordon Linoff · Accepted Answer · 2014-09-03 11:01:34Z

2

You can define the separate partitions using ranges, by doing something like this:

CREATE PARTITION FUNCTION MyPartitionFunction (varchar(255))
AS RANGE LEFT FOR VALUES ('de', 'eng', 'it');

However, I would suggest using the actual ids instead:

CREATE PARTITION FUNCTION MyPartitionFunction (int)
AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6);

answered Sep 3, 2014 at 11:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Andriy Kizym Over a year ago

but is there a possibility to make it dynamic? I know that it's possible to have some workaround like adding the insert trigger to Languages table and then alter all the partitioning functions to new values, but is it the only solution?

Gordon Linoff Over a year ago

Search for "sql server dynamic partitioning". Here are two results: stackoverflow.com/questions/18842764/… and stackoverflow.com/questions/18842764/….

Andriy Kizym Over a year ago

For those who doesn't know: partitioning is supported only by enterprise version of sql server (what a bummer)

Collectives™ on Stack Overflow

SQL Server Table partitioning

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related