http://www.sqlservercentral.com/blogs/steve_jones/2010/02/14/saving-space-_1320_-vertical-partitioning/

Printed 2014/08/23 01:02PM

Saving Space – Vertical Partitioning

By Steve Jones, 2010/02/14

I wrote about data types recently, referencing Michelle Ufford’s presentation at the PASS 2009 Summit. In that post I talked about how using a smaller data type can make a difference in the way your indexes work and improve performance.
However there’s another thing you can do as well: vertically partition your tables.
A vertical partition is a way of dividing up your table. I’ll give you an example from my past. I used to work for an online training company, and we had all sorts of education products. We had a table similar to this one used for our customers.
create table Customers
( CustomerID int identity(1,1)
, Firstname varchar(50)
, Lastname varchar(50)
, Address1 varchar(50)
, Address2 varchar(50)
, city varchar(50)
, state varchar( 50)
, postalcode varchar(20)
, countryID int
, province varchar(50)
, status tinyint
, active tinyint
, balance numeric( 10, 4)
, CompanyID int
, notes text
)


This was on SQL 2000, so TEXT was the data type we used.


In this table, we often joined our customers with other tables to display things on various pages. We wanted to grab their name almost all the time, but we also regularly queried with companyID, balance, status, and active. Those flags and FKs were used in different queries, so building a covering index could work, but we couldn’t cover all queries. We could have multiple indexes, and we did, but since there were always varying queries coming up, we did this instead.


create table Customers 
( CustomerID int identity(1,1)
, Firstname varchar(50)
, Lastname varchar(50)
, status tinyint
, active tinyint
, balance numeric( 10, 4)
, CompanyID int
)


create table Customer_Details 
( CustomerID int identity(1,1)
, Address1 varchar(50)
, Address2 varchar(50)
, city varchar(50)
, state varchar( 50)
, postalcode varchar(20)
, countryID int
, province varchar(50)
, notes text
)


If you notice we’ve essentially split this table into two tables, keeping CustomerID in both, but otherwise separating out fields. In the main Customers table we keep those often queries values, the ones that are used in most queries. In the Customer_Details table we have moved those fields that are less often queried.


This does two things. One is that indexes, clustered and non-clustered, are smaller in the first table. Even a lookup to the data means that more rows are pulled in with every page read. That translates into more information for every read, and quite possibly less reads to get to your data. Less space in memory needed to cache things, and likely better performance.


And the larger the table, the more increases in performance you might see.


This isn’t necessarily the best design for all tables, but if you have busy tables, and there are disparities on how often you use some fields, but not others, this is a technique to consider.


Note that you can always build a view that combines these tables as a way to still see the same view as you have now.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.