Is this a bad way to structure my Sql Server database?

Question

Hi folks,

I have a table that contains a few columns and then 2 final (nullable) columns which are varbinary (actually, they are SQL 2008 geography types, but I want to keep this post database agnostic).

I've hit around 500mb with around 200K rows. The varbinary is the problem - and I need the data.

So, I was wondering if it's bad if I do the following:-

• Create a separate FILEGROUP: SpatialData.mdf
• Create a new table, assigned to that new filegroup.
• Move all the spatial data (read: last two fields) out of the original table and into the new table. The new table has a foreign key against the original table.
• Create a view representing both tables.

Now, the view will be a left outer join because the relationship is: the new table has a zero or one row relationship to the original table.

EG.

Original Table

FooId INT PK NOT NULL IDENTITY
Blah VARCHAR(..) NOT NULL
Boo WHATEVER NOT NULL

New Table

FooID PK FK NOT NULL
Spatial_A VARBINARY(MAX)/GEOGRAPHY
Spatial_B VARBINARY(MAX)/GEOGRAPHY

The reason why I want to know if this is bad is because of the view and how the view is doing a join on the spatial table. I'll be using the view a lot. Currently, I'm just doing queries against the original table (because the new table doesn't exist just yet). By adding this join and the PK/FK relationship, will this impact performance?

Why split the data? I need to download the live DB to our dev servers now and then. We don't really care too much about those two spatial fields, so not having them is fine. Therefor, the size of the database to download will be much smaller.

Thoughts?

Answer 1

Instead of creating a second table, joining, and creating a view, a better solution that is possible with SQL Server 2005/2008 is to use table partitioning. To my recollection, you can vertically partition a table, and place some columns (i.e. your geospatial columns) in one file group, while putting the rest in another file group. SQL Server will handle the rest for you, you don't need to bother with a join, and you don't need a view.

Answer 2

The method that you've described is actually fairly common in my experience. Technically, if you were to normalize your database to the fullest extent you would have a lot of tables like that since one of the (usually not used) steps in normalization includes making sure that no columns have NULL values.

In practice it isn't usually carried out to that extent, but for a column (or columns) that is sparsely populated it's not a bad idea to separate it out. As long as the tables share the same primary key (which will of course be indexed), performance shouldn't be a problem.