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:

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

 

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

Leave a Comment

(required) 
(required) 
Submit

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

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