SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Length


Data Length

Author
Message
Peter Petrov
Peter Petrov
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1315 Visits: 432
Comments posted to this topic are about the item Data Length



karthik.nallajalla
karthik.nallajalla
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 32
REPLICATE( N'A', 4000000));

what is the meaning of N in N'A'? is it the maximum length.

I executed the below statements,

INSERT #t VALUES (1, REPLICATE( N'A', 4000000));
INSERT #t VALUES (2, REPLICATE( 'A', 4000000));

SELECT DATALENGTH(LongName) FROM #t

The result of the SELECT is
8000
16000

I don't know why i got 16000. Can anyone explain me?
Robbert Hof
Robbert Hof
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 834
The N'A' means that 'A' is an Unicode string constant.
Unicode is using 2 bytes to encode each character.
The DATALENGTH function returns the number of bytes used to represent the expression.
So in this case, you have to multiply the number in the REPLICATE function by 2.

HTH,

Robbert



Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18425 Visits: 12426
karthik.nallajalla (2/1/2009)
REPLICATE( N'A', 4000000));

what is the meaning of N in N'A'? is it the maximum length.

I executed the below statements,

INSERT #t VALUES (1, REPLICATE( N'A', 4000000));
INSERT #t VALUES (2, REPLICATE( 'A', 4000000));

SELECT DATALENGTH(LongName) FROM #t

The result of the SELECT is
8000
16000

I don't know why i got 16000. Can anyone explain me?


Hi Karthik,

Yes, I can.

The result of REPLICATE depends on the first argument. If it's varchar(MAX) or nvarchar(MAX), the result will also be [n]varchar(MAX) and the string won't be truncated. However, a string constant will never be considered ..(MAX) - well, maybe if the string constant is more than 4000 (nvarchar) or 8000 (varchar) characters long, I never tried that. Smile

For REPLICATE(N'A', 4000000), the input string is nvarchar(something other than max), so the result can not be longer than the longest nvarchar, which is nvarchar(4000). That's 8000 bytes, since two bytes are used for each Unicode character. The implicit cast to nvarchar(MAX) when storing the result in the table does not affect the data length.

For REPLICATE('A', 4000000), the input string is varchar(something other than max) -- note no N in front of varchar!!--. The result will be the longest varchar, which is varchar(8000). That's 8000 bytes as well, of course. But the implicit cast to nvarchar(MAX) will translate each of the 1-byte non-unicode characters to its two-byte unicode equivalent, bumping the data length to 16,000 bytes. And since nvarchar(MAX) has no problem storing an 8,000 byte unicode string, it will.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Shaun McGuile
Shaun McGuile
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 1060
REPLICATE function is obviously broken then.

it only gives 8,000 repititions and thus must be geared to old SQL 2000 varchar max size of 8000.

Naughty MS have not updated the function to deal with the new data types.

--Shaun

Hiding under a desk from SSIS Implemenation Work Crazy
Jeff Kunkel-812485
Jeff Kunkel-812485
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 112
Don't forget that the DATALENGTH function is different from the LEN function.
DATALENGTH returns the number of bytes
versus
LEN which returns the number of characters.

That is one of the tricky parts of this particular question.Wink
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18425 Visits: 12426
Shaun McGuile (2/2/2009)
REPLICATE function is obviously broken then.


Hi Shaun,

No, it's not. It just adheres to the standard operating procedure of datatype in = datatype out.

CREATE TABLE #t
(ID int,
LongName nvarchar(MAX));

INSERT #t
VALUES (1, REPLICATE(CAST(N'A' AS nvarchar(MAX)), 4000000));

SELECT DATALENGTH(LongName) FROM #t WHERE ID = 1;



Returns 8000000.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Shaun McGuile
Shaun McGuile
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 1060
Hugo: so what you are saying is that REPLICATE uses varchar sizing i.e. 8000 and not the destination size unless you explicitly instruction the function to use something else as shown in your code. Wink

Dumb function ! Tongue

--Shaun

Hiding under a desk from SSIS Implemenation Work Crazy
karthik.nallajalla
karthik.nallajalla
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 32
Thanks Robbert and Hugo for helping me out.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18425 Visits: 12426
Shaun McGuile (2/2/2009)
Hugo: so what you are saying is that REPLICATE uses varchar sizing i.e. 8000 and not the destination size unless you explicitly instruction the function to use something else as shown in your code. Wink

Dumb function ! Tongue


Hi Shaun,

I'd use different words, but basically you're right.

And by the way, this is not limited to REPLICATE but applies to all expressions in SQL Server. The division below discards the fraction because the dividend of two integers is defined to be an integer, regardless of whether the result will later be assigned to a float variable.

DECLARE @i1 int, @i2 int, @f1 float;
SET @i1 = 10;
SET @i2 = 3;

SET @f1 = @i1 / @i2;
SELECT @f1;



And as far as I can remember, all other programming languages I have been involved with behave similarly.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search