Vertical Partitioning Using sp_tableOption
Published: 26th May 2014
This was an incredibly cool thing that I learnt the other week whilst attending a SQLSkills course. I have previously made mention of Vertical Partioning and how useful it can be, BUT it does have the downside of re-architecting… this can change that.

Now, before you get too excited, you do need to have proper LOB columns in your table for this to work. Therefore if you have a few hefty varchar(1000) columns then you’re stuck with the re-architecting method you’ll find in an earlier blog post, but if you’re using a true LOB like varchar(max) then this could be for you.

So what is it? Well effectively if you’re using a LOB column then you can inform SQL Server, at table creation, that you want it to store these all “off row”. This means that, as long as the data is more than 256 characters (I think… I’ll check that and amend if needed), then SQL Server will force those LOB columns off the data page main data page and link to them with a pointer.

The big advantage here is that this is all done by SQL Server in the background meaning that there’s no difference to you at all… except better performance.

So how do we do this?

Well here’s an example:

We’re going to create a table with a varchar(max) column. However, the usage of this table is such that we employ lots of queries on the id columns, narrow down the required data, and then pull out the relevant varchar(max) value.

You’ll see what I mean.

Here’s the conventional approach…

-- create a big table

create table bigTable
(
  
productID int,
  
name varchar(100),
  
productNumber varchar(25),
  
color varchar(15),
  
reorderPoint smallint,
  
miscData varchar(max),
  
constraint pk_bigTable primary key clustered(productID)
)
insert into bigTable
select ProductID, Name, ProductNumber, Color, ReorderPoint, replicate('a', 7000)
from Production.Product
go

-- use the table a lot to obtain a subset - THEN get the large data out

if object_id('tempDB..#id') is not null drop table #id
go

create table #id
(
  
productID int
)
insert into #id
select productID
from bigTable
where color in ('Black', 'Silver', 'Red')

insert into #id
select productID
from bigTable
where name like 'S%'
or name like 'C%'

insert into #id
select productID
from bigTable
where productNumber like 'CR%'

select b.productID, b.miscData
from
(
  
select productID
  
from #id
  
group by productID
  
having count(productID) > 2
) i
join bigTable b
on i.productID = b.productID
go

drop table #id
go


Running this generated reads of 3961 on my machine based on it having 1 row per data page.

Now let’s tell SQL Server to push the LOB data off row. In this example SQL Server can fit a huge amount of non-LOB records per page and therefore this is reflected in the reads:

-- create a big table

create table bigTable
(
  
productID int,
  
name varchar(100),
  
productNumber varchar(25),
  
color varchar(15),
  
reorderPoint smallint,
  
miscData varchar(max),
  
constraint pk_bigTable primary key clustered(productID)
)

exec sp_tableoption 'bigTable', 'large value types out of row', 1
go

insert into bigTable
select ProductID, Name, ProductNumber, Color, ReorderPoint, replicate('a', 7000)
from Production.Product
go

-- use the table a lot to obtain a subset - THEN get the large data out

if object_id('tempDB..#id') is not null drop table #id
go

create table #id
(
  
productID int
)
insert into #id
select productID
from bigTable
where color in ('Black', 'Silver', 'Red')

insert into #id
select productID
from bigTable
where name like 'S%'
or name like 'C%'

insert into #id
select productID
from bigTable
where productNumber like 'CR%'

select b.productID, b.miscData
from
(
  
select productID
  
from #id
  
group by productID
  
having count(productID) > 2
) i
join bigTable b
on i.productID = b.productID
go

drop table #id, bigTable
go


Running this code with just the one amendment (adding sp_tableOption) means we now have just 453 reads.

Vertical partitioning under the covers using sp_tableOption… very powerful and handy indeed!!!
Comments:
NB: Comments will only appear once they have been moderated.