Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Length Expand / Collapse
Author
Message
Posted Saturday, January 31, 2009 8:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:16 PM
Points: 1,199, Visits: 336
Comments posted to this topic are about the item Data Length


Post #647627
Posted Sunday, February 1, 2009 4:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 6, 2009 1:58 PM
Points: 84, 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?
Post #647761
Posted Monday, February 2, 2009 12:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:41 AM
Points: 1,287, Visits: 784
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



Post #647834
Posted Monday, February 2, 2009 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 5,965, Visits: 8,218
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. :)

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
Post #647849
Posted Monday, February 2, 2009 7:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 4, 2012 9:20 AM
Points: 583, Visits: 1,060
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
Post #648026
Posted Monday, February 2, 2009 8:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:33 PM
Points: 327, Visits: 101
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.;)
Post #648057
Posted Monday, February 2, 2009 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 5,965, Visits: 8,218
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
Post #648086
Posted Monday, February 2, 2009 9:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 4, 2012 9:20 AM
Points: 583, Visits: 1,060
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. ;)

Dumb function ! :P

--Shaun


Hiding under a desk from SSIS Implemenation Work
Post #648113
Posted Monday, February 2, 2009 10:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 6, 2009 1:58 PM
Points: 84, Visits: 32
Thanks Robbert and Hugo for helping me out.
Post #648217
Posted Monday, February 2, 2009 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:33 PM
Points: 5,965, Visits: 8,218
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. ;)

Dumb function ! :P


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
Post #648240
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse