THE SQL Server Blog Spot on the Web

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

Denis Gobo


This should trip up some people.....

Without running this code what do you think will LEN and DATALENGTH return?

DECLARE @i int

SELECT @i =' 123456789 '


Published Wednesday, December 12, 2007 9:56 AM by Denis Gobo
Filed under:

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



Paul Nielsen said:

a clue..

Select Len('abcdefg'), datalength('abcdefg')

Select Len(N'abcdefg'), datalength(N'abcdefg')

December 12, 2007 12:13 PM

Jared Ko said:

I didn't notice the data type. I spent a couple of minutes trying to figure out why DATALENGTH was doing an implicit conversion before I realized. I was too busy focused on the beginning and trailing spaces.

Good teaser, though. I didn't know that INTs were treated this way.

December 12, 2007 2:06 PM

Denis Gobo said:

I figured most people wouldn't look at the dataype. DATALENGTH returns the needed storage for int = 4, for bigint =8. Just change the int to bigint and run it again

December 12, 2007 2:10 PM

Adam Machanic said:

Nice one!

December 12, 2007 2:32 PM

Tom Trombly said:

I haven't pull down 2008 yet, and I'm curious what the results would be with @i declared as a vardecimal.  

December 12, 2007 2:47 PM

Denis Gobo said:


I don't think you can use vardecimal for a variable, this is used for tables only (IIRC)

December 12, 2007 2:59 PM

Adam Machanic said:

I think Denis is correct.  Isn't vardecimal in SQL Server 2005 SP2?

December 12, 2007 3:08 PM

Vern Rabe said:

If I understand it correctly, vardecimal isn't a data type at all, but a storage format that can make storing of decimal data types more compact.

December 12, 2007 3:25 PM

Tom Trombly said:

Yes, I am (was) ignorant and you all (Denis, Adam, Vern) are all correct.  Thanks.  After a little research based on the clues provided I've worked out a test for my hypothosis, that DATALENGTH will return varying values for a vardecimal.  I ended up borrowing code from

and tweaking it a bit to produce a repeatable result.

Here is what I got:

set nocount on


create database varDecimalON


-- enable vardecimal storage format for varDecimalON Dababase

exec sp_db_vardecimal_storage_format 'varDecimalON', 'on'


use varDecimalON

if object_id('t1') is not null

   drop table t1

create table t1


   col1 decimal(12, 11)


-- enable vardecimal storage format on t1 in varDecimalON database

exec sys.sp_tableoption 't1', 'vardecimal storage format', 'on'

declare @i int

set @i = 1

while @i <= 100000


   insert into t1(col1)

   select    convert(decimal(12, 11), rand()*10)

   set @i = @i + 1


select distinct len(col1)[LEN],datalength(col1)[DATALENGTH] from t1


use master

drop database varDecimalON

set nocount off



----------- -----------

13          5

13          9


Thanks again.

December 13, 2007 9:59 AM

Leave a Comment


About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


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