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

Large Object Data

By Simon Sabin,

Large Object Data can be confusing for many DBAs. How to load it, retrieve it, and optimize queries on your server. New author and MVP Simon Sabin brings us a look at whether or not you should store this data in a row or on separate pages.

Introduction

I have just received the latest copy of SQLMagazine and read with interest the article on "Varbinary(max) tames the BLOB" it discusses in passing the fact that the new max data types hold the data in the row (same page) if possible and only if the data is too large, move it off the page. This is the opposite to the older LOB data types, TEXT, NTEXT and IMAGE which only hold the data in row if you set an option. (Kalen Delaney goes into more depth in another article)

I have a word of caution around the inclusion of any of the max data types in a table with other columns (except the PK). The reason for this is that because the max data types are stored "in row", this means that if you have to read other data that requires the row being accessed i.e. accessing a column that is not in an index you will be reading this large column of data as well.

Imagine a person table

create table Person ( 
PersonId int 
identity(1,1) Primary Key, 
Forename varchar(50),
Surname varchar(50),
DateOfBirth datetime,
Avatar varbinary(max))

Create Index IX_Person_DateOfBirth
   On Person(DateOfBirth)

Let's assume the average size of the Avatar is 4000 bytes (20x20 logo). If you then run the following query

Select Forename, Surname 
 From Person 
 Where DateOfBirth Between '1 Aug 2006' and '1 Sep 2006'

To get the Forename and Surname fields a bookmark lookup that has to go to the main row of the table. Unfortunately this is bloated with the Avatars and so you only get 1 row per page. So if the query returned 80 rows you will probably have had to read at least 80 pages of data.

If the Person table was set to have "large value types out of row" on then each page would contain about 80 rows because only a pointer to the Avatar is stored in the row (it would be > 80 if the forename and surname weren't always 50 characters). So your query would then read 2 or 3 pages (its > 1 due to the index pages being read)

There are other was around this by adding the extra columns onto your indexes by using the new "INCLUDE" clause, but this would have to be done on all indexes on your table.

Conclusion

My preference is to store images and other LOB data on tables with columns that have a common selection policy, the LOB column (s) will always be selected when the other columns are selected i.e. if in the above situation the Avatar was always returned when any of the surname, forename or dateOfbirth columns where selected then having the Text In Row would be a benefit.

So bottom line is to plan carefully whether to include LOB data in row, taking note of the other columns on the table.

You can also read about the new .write method available for the max data types that greatly improve update performance

Simon is a database architect for Totaljobs Group based in the UK. He specialises in performance SQL Server systems and recently on search technologies.

To keep up on Simons thoughts, read his blog

You can also read about Simon in a recent article by Steve Jones

Total article views: 5919 | Views in the last 30 days: 1
 
Related Articles
FORUM

Getting DateOfBirth of youngest person.

Hi, i have table named PersonalData.In this table i have a column DateOfBirth.I want to get the per...

FORUM

How To Lock in Sql Server 2005 The record When It access by Other Person?

How To Lock in Sql Server 2005 The record When It access by Other Person?

FORUM

How to prevent our database to access any person

How to prevent our database to access any person while other person is administrator

FORUM

Avatar / Photo Issues?

If I edit my contact details and click on either 'Edit Avatar' or 'Edit Personal Photo', I see a mes...

SCRIPT

Convert Row to Column without using any other column for Pivot

Aim is to convert Row output having 3 rows of column Text to column output having 3 columns named as...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones