THE SQL Server Blog Spot on the Web

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

Denis Gobo

SQL Teaser: LEN vs DATALENGTH

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 '

SELECT @i,LEN(@i),DATALENGTH(@i)

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

Comments

 

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:

Tom,

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 http://weblogs.sqlteam.com/mladenp/archive/2006/11/10/19546.aspx

and tweaking it a bit to produce a repeatable result.

Here is what I got:

set nocount on

go

create database varDecimalON

go

-- enable vardecimal storage format for varDecimalON Dababase

exec sp_db_vardecimal_storage_format 'varDecimalON', 'on'

go

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

begin

   insert into t1(col1)

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

   set @i = @i + 1

end

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

go

use master

drop database varDecimalON

set nocount off

/*

LEN         DATALENGTH

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

13          5

13          9

*/

Thanks again.

December 13, 2007 9:59 AM
New Comments to this post are disabled

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 http://sqlservercode.blogspot.com/ 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

Syndication

Privacy Statement