THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Variable Length Storage for Exact Numerics

I finally have recovered from the PASS conference last week, and connected to the blog site to see nothing but PASS posts on the front page! I guess I better get cracking if I want to maintain my visibility. J

 

 

One of the sessions at PASS was called “Hidden Gems in SQL Server 2005”, but a friend of mine suggested that it should really be called “Why SP2 Ain't Coming Out Any Time Soon”. Many of the gems dealt with new features in Service Pack 2, which you can download from http://www.microsoft.com/sql/ctp.mspx

 

As much as the folks at Microsoft keep saying that Service Packs are only for bug fixes and no new features will be introduced, they continue to add new features.

 

One new feature discussed in that PASS session is also described in a series of posts on the SQL Server Storage Engine blog. This feature allows you to change the storage of decimal/numeric values so that they can be stored as variable length values. These datatypes can use between 5 and 17 bytes per column, depending on the precision you declare for them. If you have a fact table with billions of rows and multiple columns of decimal/numeric data, it’s possible to reduce your storage requirements greatly by switching to a new variable length decimal/numeric datatype called VARDECIMAL.

 

To date, three posts on the VARDECIMAL datatype have been made by Sunil Agarwal, and he promises a couple more. The first one is here:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/11/10/reducing-the-size-of-your-database-in-sql-server-2005-sp2.aspx

 

The posts from Sunil provide details as to how to determine the savings you can realize by switching to this new datatype.

 

In older versions of SQL Server, prior to SQL Server 7.0 (in which the page and row structures changed completely) SQL Server would store any column defined as allowing NULLs as a variable length column. A column that actually had NULL would then be stored as a zero-length column. Starting in SQL Server 7.0, only varchar, varbinary, LOB and sqlvariant columns are stored as variable length, and all other columns are stored as fixed length. Every column uses the full defined width. Each row contains a ‘NULL bitmap’ with a bit to indicate whether a column actually is null is that row.

 

I use an example in the classes I teach of a SQL Server 6.5 database with dozens of char(255) columns in a table, with 255 being the maximum length for char or varchar in that version, and most of those columns in most of the rows contain NULL values, so they don’t take any space. When the database containing that table is upgraded (to 7.0, 2000 or 2005) every one of those columns will then take the full defined width of 255 bytes, and the database could balloon in size.

 

In fact, the same friend who dubbed the above-mentioned PASS session “Why SP2 Ain't Coming Out Any Time Soon” had a similar problem, and it is his experiences on which I based my class example. When he discovered this new vardecimal he reminded me of his earlier situation:

 

My first important SQL Server database was a horribly de-normalized datamart with 48+ columns for monthly figures, sparsely populated with NULLs for the rest.  In 1.1 through 6.5 that worked great as the NULLs were stored zero length.”

 

I had to remind him that I remembered his situation well, as I discussed the repercussions of the change in NULL storage every time I teach my SQL Server Architecture, Internals and Tuning class.

 

Note that the new vardecimal datatype doesn’t just help when you have NULLs, but can adjust the storage needed for smaller, less precise non-null values as well. Please read Sunil’s blog posts for the full story.

 

Have fun!

 

-- Kalen

 

Published Monday, November 20, 2006 9:20 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Farhan Soomro said:

FYI

Sunil Agarwal (Expert):
Q: [14] Does vardecimal help if the values is NULL
A: Yes, savings with NULL is similar to 0.0 for decimal/numeric
November 21, 2006 9:13 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement