August 1, 2013 at 6:47 am
meh... 😛
August 1, 2013 at 6:47 am
PHYData DBA (8/1/2013)
Hugo Kornelis (8/1/2013)SELECT LEN(@var) implicitly converts @var to string, because that's the only data type supported for LEN. This conversion is independent of the clients' Windows locale setting, since it's done at the server. It might depend on SET LANGUAGE, though I'd expect that to only influence the choice of decimal separator. The length of the string is then computed and returned to the client as an integer. The client then decides on how to present this integer value. Most clients tend to do this in the same way, since there aren't that many options for formatting the string representation of integer values.
Hugo - I would wager you have no documentation or proof that this conversion occurs.
LEN returns the Bytes required to store the data for All NUMERIC data types. It does not convert the value to string.
The following is copied from the MSDN page for the LEN function:
Returns the number of characters of the specified string expression, excluding trailing blanks.
Note:
To return the number of bytes used to represent an expression, use the DATALENGTH function.
Personally I would be very reluctant to suggest that Hugo lacks any basis for his comments since he regularly shares his knowledge on these forums and I have found it generally worth listening to what he says.
Philip
August 1, 2013 at 6:52 am
philip.cullingworth (8/1/2013)
PHYData DBA (8/1/2013)
Hugo Kornelis (8/1/2013)SELECT LEN(@var) implicitly converts @var to string, because that's the only data type supported for LEN. This conversion is independent of the clients' Windows locale setting, since it's done at the server. It might depend on SET LANGUAGE, though I'd expect that to only influence the choice of decimal separator. The length of the string is then computed and returned to the client as an integer. The client then decides on how to present this integer value. Most clients tend to do this in the same way, since there aren't that many options for formatting the string representation of integer values.
Hugo - I would wager you have no documentation or proof that this conversion occurs.
LEN returns the Bytes required to store the data for All NUMERIC data types. It does not convert the value to string.
The following is copied from the MSDN page for the LEN function:
Returns the number of characters of the specified string expression, excluding trailing blanks.
Note:
To return the number of bytes used to represent an expression, use the DATALENGTH function.
Personally I would be very reluctant to suggest that Hugo lacks any basis for his comments since he regularly shares his knowledge on these forums and I have found it generally worth listening to what he says.
Philip
Also from MSDN article http://msdn.microsoft.com/en-us/library/ms190476(v=sql.110).aspx
Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
Phillip would you mind posting a link to the article your quote comes from? I not trying to say anyone is right or wrong. I am just pointing out where results and documentation are not matching up with posts.
August 1, 2013 at 6:58 am
PHYData DBA (8/1/2013)
Phillip would you mind posting a link to the article your quote comes from? I not trying to say anyone is right or wrong. I am just pointing out where results and documentation are not matching up with posts.
PhyData the link is the same as the one I linked to in one of my earlier posts, but here it is again.http://msdn.microsoft.com/en-us/library/ms190329%28v=sql.90%29.aspx. This is the 2005 version but all version have the same information.
Looking at your posts, I suspect you are confusing Length as used in the page you are looking at and the SQL function LEN(string_expression)
Also, probably not needed, but this page http://msdn.microsoft.com/en-us/library/ff848794.aspx lists the Numeric Data Types and includes Money.
Philip
August 1, 2013 at 7:02 am
PHYData DBA (8/1/2013)
Phillip would you mind posting a link to the article your quote comes from? I not trying to say anyone is right or wrong. I am just pointing out where results and documentation are not matching up with posts.
As Philip already said, it's the MSDN page for the LEN function. Google MSDN + LEN + SQL Server, and it's the first hit.
Direct link: http://msdn.microsoft.com/en-us/library/ms190329.aspx
The link you posted talks about the length of numeric data types, but doesn't reference the LEN function at all. The length discussed on that page is the number of bytes used to represent the value, which you can find by using the DATALENGTH function.
I always like to include code to prove my point - so here's some quick code:
DECLARE @val money;
SET @val = 0;
SELECT LEN(@val), DATALENGTH(@val);
SET @val = -123456789.01
SELECT LEN(@val), DATALENGTH(@val);
As you see, DATALENGTH remains the same regardless of actual value (numeric data types are fixed length), but LEN changes - because more characters are needed to represent the higher values in human-readable form.
EDIT: Spent too much time on my reply; Philip has beaten me to it. 😎
August 1, 2013 at 7:06 am
DECLARE @var money = 123456789101118.12345
DECLARE @nvarchar nvarchar(21)
DECLARE @char nchar(22)
--Select 1
SELECT LEN(N'123456789101118.12345'), DATALENGTH(N'123456789101118.12345') ,N'123456789101118.12345'
--Select 2
SELECT LEN(@var), DATALENGTH(@var), @var
--Select 3
SELECT @nvarchar = convert(nvarchar(21), @var)
SELECT LEN(@nvarchar), DATALENGTH(@nvarchar), @nvarchar
--Select 4
SELECT @nvarchar = convert(nvarchar(21), @var,2)
SELECT LEN(@nvarchar), DATALENGTH(@nvarchar), @nvarchar
I'll just let the code speak for itself.
Have Fun! 😎
EDIT - Watch how datalength changes on the string variable.
August 1, 2013 at 7:13 am
PHYData DBA (8/1/2013)
DECLARE @var money = 123456789101118.12345
DECLARE @varchar nvarchar(35)
DECLARE @char nchar(22)
--Select 1
SELECT LEN('123456789101118.12345'), DATALENGTH('123456789101118.12345') ,'123456789101118.12345'
--Select 2
SELECT LEN(@var), DATALENGTH(@var), @var
--Select 3
SELECT @varchar = convert(nvarchar(25), @var)
SELECT LEN(@varchar), DATALENGTH(@varchar), @varchar
--Select 4
SELECT @varchar = convert(nvarchar(25), @var,2)
SELECT LEN(@varchar), DATALENGTH(@varchar), @varchar
I'll just let the code speak for itself.
Have Fun! 😎
Now where is the surprise?
Best Regards,
Chris Büttner
August 1, 2013 at 7:13 am
Hugo Kornelis (8/1/2013)
PHYData DBA (8/1/2013)
Phillip would you mind posting a link to the article your quote comes from? I not trying to say anyone is right or wrong. I am just pointing out where results and documentation are not matching up with posts.As Philip already said, it's the MSDN page for the LEN function. Google MSDN + LEN + SQL Server, and it's the first hit.
Direct link: http://msdn.microsoft.com/en-us/library/ms190329.aspx
The link you posted talks about the length of numeric data types, but doesn't reference the LEN function at all. The length discussed on that page is the number of bytes used to represent the value, which you can find by using the DATALENGTH function.
I always like to include code to prove my point - so here's some quick code:
DECLARE @val money;SET @val = 0;
SELECT LEN(@val), DATALENGTH(@val);
SET @val = -123456789.01
SELECT LEN(@val), DATALENGTH(@val);
As you see, DATALENGTH remains the same regardless of actual value (numeric data types are fixed length), but LEN changes - because more characters are needed to represent the higher values in human-readable form.
EDIT: Spent too much time on my reply; Philip has beaten me to it. 😎
Thank you Hugo. Like you said Philip ant you beat us both to the code. Although I did find the results of my test code I posted this morning very interesting also. You would think the Data length of all the strings would have remained the same and it did not. Fun...
August 1, 2013 at 7:32 am
PHYData,
I'm not sure I understand what you are trying to say with this code, but let's go through it select by select
SELECT LEN('123456789101118.12345'), DATALENGTH('123456789101118.12345') ,'123456789101118.12345'This is showing that the LEN function (the number of characters of the specified string expression) and the DATALENGTH function (the number of bytes used to represent any expression) are equal when a literal string is entered.
SELECT LEN(@var), DATALENGTH(@var), @varThis is showing that the LEN function (once the numeric value is converted to a string using the default style of 0 [no commas and 2 numbers to the right of the decimal]) returns the value expected (15 characters for the numbers before the decimal + 1 for the decimal point + 2 for the numbers after the decimal) while the DATALENGTH (the number of bytes required to store the value) =8 (which is the storage requirements specified on MSDN http://msdn.microsoft.com/en-us/library/ms179882.aspx). This select also shows that the variable has only stored 4 digits after the decimal and has rounded .12345 to .1235
SELECT @varchar = convert(nvarchar(25), @var)
SELECT LEN(@varchar), DATALENGTH(@varchar), @varcharThe conversion from Money to nvarchar has used the same style code and so the LEN function returns the same value as the previous SELECT. DATALENGTH has returned 36 since we are converting to NVARCHAR which uses 2 bytes to store each character.
SELECT @varchar = convert(nvarchar(25), @var,2)
SELECT LEN(@varchar), DATALENGTH(@varchar), @varchar
Conversion from Money to NVarchar is now using style 2 [no commas and 4 numbers to the right of the decimal] and as is expected has increased the length of the string representation of the number by 2 characters. This is reflected in the increased value of the LEN function and also the DataLength function (remembering that we are using NVARCHAR so everything is doubled).
Regarding your comment about the DATALENGTH, Money has a DATALENGTH of 8, whatever value is stored. CHAR and NCHAR have fixed data length whatever is stored in them but VARCHAR AND NVARCHAR only use what is required.
DECLARE @var money
DECLARE @varchar nvarchar(35)
DECLARE @char nchar(40)
SELECT
DATALENGTH(@var),DATALENGTH(@varchar),DATALENGTH(@char)
SELECT
@var=0
,@varchar=''
,@char=''
SELECT
DATALENGTH(@var),DATALENGTH(@varchar),DATALENGTH(@char)
SELECT
@var=123456789101118.1234
,@varchar='123456789101118.1234'
,@char='123456789101118.1234'
SELECT
DATALENGTH(@var),DATALENGTH(@varchar),DATALENGTH(@char)
August 1, 2013 at 7:36 am
PHYData DBA (8/1/2013)
I'll just let the code speak for itself.Have Fun! 😎
EDIT - Watch how datalength changes on the string variable.
This time I remembered to refresh before posting. And a good thing too - Philip has already replied, and he wrote almost exactly the same I wanted to write.
Thanks, Philip! 😉
August 1, 2013 at 9:24 am
The answer really also depends on what version of SQL Server you are using. Some versions do not allow a default value of a declared variable to be set, it must be done in a separate statement.
Just my thoughts,
Leif
August 1, 2013 at 9:29 am
philip.cullingworth (8/1/2013)
PHYData,I'm not sure I understand what you are trying to say with this code, but let's go through it select by select
Seems like understood everything as well as I did once I ran the code and looked hard at the results.
😛
August 1, 2013 at 9:30 am
Hugo Kornelis (8/1/2013)
PHYData DBA (8/1/2013)
I'll just let the code speak for itself.Have Fun! 😎
EDIT - Watch how datalength changes on the string variable.
This time I remembered to refresh before posting. And a good thing too - Philip has already replied, and he wrote almost exactly the same I wanted to write.
Thanks, Philip! 😉
Yep
August 1, 2013 at 11:36 am
PHYData DBA (8/1/2013)
Hugo Kornelis (8/1/2013)SELECT LEN(@var) implicitly converts @var to string, because that's the only data type supported for LEN. This conversion is independent of the clients' Windows locale setting, since it's done at the server. It might depend on SET LANGUAGE, though I'd expect that to only influence the choice of decimal separator. The length of the string is then computed and returned to the client as an integer. The client then decides on how to present this integer value. Most clients tend to do this in the same way, since there aren't that many options for formatting the string representation of integer values.
Hugo - I would wager you have no documentation or proof that this conversion occurs.
LEN returns the Bytes required to store the data for All NUMERIC data types. It does not convert the value to string.
That is unadulterated tripe, utter nonsense, total rubbish.
If you look up the definition of the LEN function, you will see that it requires a string argument. As a result, any non-string argument is converted to string before the function is applied. The whole reason for having implicit conversion in SQL is to allow people to do things like using a function or an operator with an arguments of type combinations or single types that it can't accept, and have the system insert an implicit conversion to avoid the type error. This is something which has been present in type systems for all sorts of computer languages for more than 40 years (more that 50, I believe, but my first contact with programming was only 47 years ago, so in round numbers I can only vouch for more than 40), the implicit conversion that results from such a call or application was traditionally called "coercion", and I haven't a clue why SQL calls it "implicit conversion".
Tom
August 1, 2013 at 11:50 am
PHYData DBA (8/1/2013)
Also from MSDN article http://msdn.microsoft.com/en-us/library/ms190476(v=sql.110).aspx
Length for a numeric data type is the number of bytes that are used to store the number. Length for a character string or Unicode data type is the number of characters. The length for binary, varbinary, and image data types is the number of bytes. For example, an int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
And just where do you get the idea that "length" on that page means "what the Len function returns"? It is very well documented in BOL that it is not, as several people have pointed out in comments on this question.
And if you were not so convinced that you were right despite everyone disagreeing with you, you could run some experiments looking at how much disc storage is required for how many variables on money or decimal type and whether that is as large as what treating LEN as storage length would suggest, and then you would fin that Len and storage length are NOT the same thing at all. People who "know" they are right and refuse to experiment to see if they have got it wrong ,,,, no, I can't finish that sentence without being rude, so I'll leave you to finish it for yourself.
I think Hugo and Philip are being much too gentle.
Tom
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply