Determining the length of a data type

Question

Determining the length of a data type

michael.leach2015

SSCommitted

Points: 1597
More actions
May 24, 2019 at 2:40 am

#3646750
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?

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

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 2

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: 27038 More actions · Answer 3

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/