Determining the length of a data type

• michael.leach2015

Default port

Points: 1484

On this website in at the beginning of the 3rd paragraph, we have:

"Length for a numeric data type is the number of bytes that are used to store the number."

When I run this code, the answer is 2.

DECLARE @n int
SELECT @n = 25
SELECT LEN(@n)

The int data type has 4 bytes, so why do I get 2 instead of 4?  Instead of returning the number of bytes, this seems to be returning the number of digits.

If I have an  int variable, SQL will set aside 4 bytes in memory to accommodate the value assigned to that variable right? Suppose I assign a value that requires only 1 byte of storage.  If this were the case, how many bytes are used to store the number?  Is it 1 byte or 4 bytes?

• Jeff Moden

SSC Guru

Points: 994661

michael.leach2015 wrote:

On this website in at the beginning of the 3rd paragraph, we have: "Length for a numeric data type is the number of bytes that are used to store the number." When I run this code, the answer is 2.

DECLARE @n int
SELECT @n = 25
SELECT LEN(@n)

The int data type has 4 bytes, so why do I get 2 instead of 4?  Instead of returning the number of bytes, this seems to be returning the number of digits. If I have an  int variable, SQL will set aside 4 bytes in memory to accommodate the value assigned to that variable right? Suppose I assign a value that requires only 1 byte of storage.  If this were the case, how many bytes are used to store the number?  Is it 1 byte or 4 bytes?

If you're going to use a function, don't just assume what it does.  Go look it up.  In this case, you find the syntax for the LEN function is...

LEN ( string_expression )

With that in mind, you tell me why you're getting a 2 instead of a 4 for the example you gave.

Then, change LEN to DATALENGTH and see the difference.  You should also lookup DATALENGTH to see what it actually does, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

How to post code problems
Create a Tally Function (fnTally)

• michael.leach2015

Default port

Points: 1484

Ok.  Good point.  And good advice.  I tried DATALENGTH on a:

tinyint variable assigned the value of 1

smallint variable assigned the value of 1

int variable assigned the value of 1

bigint variable assigned the value of 1

To represent the number 1, it only takes 1 byte.  Instead of getting 1 as the answer in all 4 cases above, I got the number of bytes set aside by the variable which is determined by the variables data type.  So I got:

1

2

4

8

But what about this.  Instead of returning the number of bytes set aside by a variable (dependent on the data type), what if I wanted to return the number of bytes that is occupied by the value stored in the variable?  If that were possible, in all four cases above the value returned would be 1 since 1 byte is all it takes to represent the value of 1 and I am assigning the value of 1 to a variable in all four cases above.  Is there a way to return the number of bytes occupied by the value assigned to a variable?

• Michael L John

One Orange Chip

Points: 25759

No, your assumption is not correct.

This link  explains how much is allocated for the the different data types.

So, when you declare a variable, or a column in the table as a bigint, it will be allocated 8 bytes.

It seems as if you are confusing character types and numeric types.  This snippet of code illustrates that.

DECLARE @1 varchar(1) = '1'
DECLARE @2 varchar(2) = '11'
DECLARE @3 varchar(3) = '111'
DECLARE @4 varchar(4) = '1111'

SELECT
DATALENGTH(@1),
DATALENGTH(@2),
DATALENGTH(@3),
DATALENGTH(@4)

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply