Can I split a single SQL 2008 DB Table into multiple filegroups, based on a discriminator column?
Posted
by
Pure.Krome
on Stack Overflow
See other posts from Stack Overflow
or by Pure.Krome
Published on 2011-01-12T23:28:15Z
Indexed on
2011/01/12
23:54 UTC
Read the original article
Hit count: 200
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.
© Stack Overflow or respective owner