Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Saving Space – Vertical Partitioning

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.

Comments

Posted by Jason Brimhall on 14 February 2010

This technique works quite well.  I hadn't thought of it as "Partitioning" but it fits the mold. This is a technique that we use frequently for similar table types and data - as you presented. Thanks Steve.

Posted by Steve Jones on 14 February 2010

Vertical partitioning has worked well for me when I understand the data model and query patterns well. When you see something like this, even scans don't give you the penalty you might have in an (otherwise) wide table..

Glad you liked it.

Posted by peterhe on 22 February 2010

I guess you have a copy/paste issue. The CustomerID in the detail table is not of identity.

Posted by Bhuvnesh on 5 February 2013

How we are going to manage customerID values in both tables when in one of table it is identity ?

Leave a Comment

Please register or log in to leave a comment.