Can I split a single SQL 2008 DB Table into multiple filegroups, based on a discriminator column?
- by Pure.Krome
Hi folks,
I've got a SQL Server 2008 R2 database which has a number of tables. Two of these tables contains a lot of large data .. mainly because one of them is VARBINARY(MAX) and the sister table is GEOGRAPHY. (Why two tables? Read Below if you're interested***)
The data in these tables are geospatial shapes, such as zipcode boundaries.
Now, the first 70K odd rows are for DataType = 1
the rest 5mil rows are for DataType = 2
Now, is it possible to split the table data into two files? so all rows that are for DataType != 2 goes into File_A and DataType = 2 goes into File_B?
This way, when I backup the DB, I can skip adding File_B so my download is waaaaay smaller? Is this possible?
I guessing you might be thinking - why not keep them as TWO extra tables? Mainly because in the code, the data is conceptually the same .. it's just happens that I want to split the storage of this model data. It really messes up my model if I now how two aggregates in my model, instead of one.
***Entity Framework doesn't like Tables with GEOGRAPHY, so i have to create a new table which transforms the GEOGRAPHY to VARBINARY, and then drop that into EF.