October 3, 2012 at 10:57 am
L' Eomot Inversé (10/3/2012)
rmechaber (10/3/2012)
Per BOL:length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. sic
Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.
The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.
The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.
Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.
October 3, 2012 at 11:53 am
rmechaber (10/3/2012)
L' Eomot Inversé (10/3/2012)
rmechaber (10/3/2012)
Per BOL:length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. sic
Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.
The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.
The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.
Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.
Very strange when I click on the link I included in support of the corrrect answers I get the following:
char and varchar (Transact-SQL)
SQL Server 2012 Other Versions
... ... SQL Server 2008 R2
.. ... SQL Server 2008
... ... SQL Server 2005
October 3, 2012 at 12:06 pm
bitbucket-25253 (10/3/2012)
rmechaber (10/3/2012)
L' Eomot Inversé (10/3/2012)
rmechaber (10/3/2012)
Per BOL:length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. sic
Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.
The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.
The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.
Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.
Very strange when I click on the link I included in support of the corrrect answers I get the following:
char and varchar (Transact-SQL)
SQL Server 2012 Other Versions
... ... SQL Server 2008 R2
.. ... SQL Server 2008
... ... SQL Server 2005
I think L'Eomot was referring to the hyperlink I provided to BOL in my reply (timestamp 2:27:11 pm), not in your original explanation.
October 3, 2012 at 12:13 pm
Thanks Ron.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2012 at 11:31 pm
Here we can see the default size is 30 for the varchar and the char datatype.
DECLARE @myVariable AS varchar(40);
SET @myVariable = 'This string is longer than thirty characters';
SELECT CAST(@myVariable AS varchar);
SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';
SELECT CONVERT(char, @myVariable);
SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';
But still I have question that why following expression returning 1 even we already assign value into the variable.
DECLARE @myVariable AS varchar
set @myVariable = 'abc';
DECLARE @myNextVariable AS char
set @myNextVariable= 'abc';
--The following returns 1
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);
GO
--Output
/*
(No column name) (No column name)
11
*/
October 3, 2012 at 11:35 pm
Following example will give idea that default size of the varchar and char in convert/cast has 30
DECLARE @myVariable AS varchar(40);
SET @myVariable = 'This string is longer than thirty characters';
SELECT CAST(@myVariable AS varchar);
SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';
SELECT CONVERT(char, @myVariable);
SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';
But still I have one doubt that why following expression giving 1 even I already assign values to variable.
DECLARE @myVariable AS varchar
set @myVariable = 'abc';
DECLARE @myNextVariable AS char
set @myNextVariable= 'abc';
--The following returns 1
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);
GO
/*
(No column name)(No column name)
11
*/
October 4, 2012 at 12:24 am
You haven't specify the length of datatype in "DECLARE @myVariable AS varchar" that's why it is giving 1 as output.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 4, 2012 at 1:32 am
neat and clear ..
Thanks for the question .
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
October 4, 2012 at 1:58 am
Thanks for reply.
I know this its taking size 1 default.. But my doubt is .. I assign the values to that variable so it contains memory as per values. I want to know how much space my variable occupied after assigning the values to that variable?
October 4, 2012 at 3:57 am
Vikas Pathak (10/4/2012)
I want to know how much space my variable occupied after assigning the values to that variable?
That's not how it works. When you assign the value 'xyz' to the variable, the variable itself is already a single character varchar, so only the first character of 'xyz' is actually assigned. The literal is 3 bytes long but the variable is just a single byte. It's exactly the same as if you did this:DECLARE @myVariable VARCHAR(1)
SET @myVariable = 'xyz'
-------------------------------Oh no!
October 4, 2012 at 3:59 am
rmechaber (10/3/2012)
Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.
I didn't see a link in your message - it was pretty well hidden. However, I did look up the 2005 BoL page that specifies the default length used for char and varchar with cast (the link is msdn.microsoft.com/en-us/library/ms176089(v=sql.90).aspx) and it states that the default with CAST is 30. The SQL 2000 version of that page only gives the default for convert, and I guessed that that was what you were quoting.
Now that I've looked at your message again I see there is a link in there , and that page does specify only the default for convert. So we have one BoL page for 2005 which says nothing about the default length with cast, and one which says it's 30. Should I take it that you believe that because one page dosn't specify the default you assume that the other page which does specify it is wrong, or have you actually run a test to check what the default value actually is?
edit fix typos
Tom
October 4, 2012 at 4:32 am
Hi All,
I am showing you this ambiguous behavior of length function in T-SQL.
Concept:
1)when data itself passes to 'LEN' function , then it returns no of characters. refer case-1.
2)If u are passing variable inside a LEN function, then we will have three more cases.
First of all, by default 'varchar' have default length is '1' in variable declaration time and '30' is in use inside 'CAST' and 'CONVERT' functions.
refer case2,3,4.
Case-1:
DECLARE @a VARCHAR;
SET @a='AAA' ;
SELECT
LEN('AAA') AS 'Declared'
, LEN(CONVERT(VARCHAR,'AAA')) AS 'Converted'
, LEN(CAST('AAA' AS VARCHAR)) AS 'Cast'
RESULT:
333
CASE 2:
DECLARE @a VARCHAR;
SET @a='AAA' ;
SELECT
LEN(@a) AS 'Declared'
, LEN(CONVERT(VARCHAR,@a)) AS 'Converted'
, LEN(CAST(@a AS VARCHAR)) AS 'Cast'
RESULT:
111
Exp: VARCHAR have '1' size and @a will contain only 'A'(one character).
so, we look size according to size of datatype like in Declared section : @a have 1 character and VARCHAR also can hold only 1 character. So result is 1.
Converted Section: @a have 1 character and VARCHAR can hold 30 character. So result is 1.(DATA<SIZE)
Cast Section: same as Converted section.
CASE 3:
DECLARE @a VARCHAR(5);
SET @a='AAABBBCCC' ;
SELECT
LEN(@a) AS 'Declared'
, LEN(CONVERT(VARCHAR,@a)) AS 'Converted'
, LEN(CAST(@a AS VARCHAR)) AS 'Cast'
result:
555
Exp: same as case2 , now we have a VARCHAR(5).
CASE 4:
DECLARE @a VARCHAR(50);
SET @a='Indias services sector makes up for over 60 percent of the countrys gross domestic product and a strong reading in HSBCs survey augurs well for the economy, where growth has faltered in recent months against the backdrop of political upheaval.
Service sector activity grew at a faster clip in September led by firm demand, underscoring (its) resilience, said Leif Eskesen, an economist at HSBC.';
SELECT
LEN(@a) AS 'Declared'
, LEN(CONVERT(VARCHAR,@a)) AS 'Converted'
, LEN(CAST(@a AS VARCHAR)) AS 'Cast'
RESULT:
503030
Exp: Here,
Declared:@a can contain 50 characters.so,it shows 50 as o/p.
Converted: VARCHAR have default size 30, but data(@a) have 50 characters so it truncated to 30.
Cast: same as converted.
Thanks:
Ajay Gaur
9767860936
October 4, 2012 at 8:14 am
L' Eomot Inversé
Note in all of the following the bolding of parts of the explanation has been done by myself.
When using BOL, at least the copy available to myself
SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms176089(v=sql.90).aspx
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms176089(v=sql.100).aspx
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
SQL Server 2012
http://msdn.microsoft.com/en-us/library/ms176089(v=sql.110).aspx
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
As for running my own tests, if I did do so, it was when I was using SQL 2000 and that would have been some 10 or 12 years ago, and to be truthful, I doubt if I did run tests back then. Naive enough back then to believe all I read in BOL as the truth and nothing but the truth.
October 4, 2012 at 2:19 pm
bitbucket-25253 (10/4/2012)
L' Eomot InverséNote in all of the following the bolding of parts of the explanation has been done by myself.
...
...
...
As for running my own tests, if I did do so, it was when I was using SQL 2000 and that would have been some 10 or 12 years ago, and to be truthful, I doubt if I did run tests back then. Naive enough back then to believe all I read in BOL as the truth and nothing but the truth.
Ron,
The comments you are responding to you were in response to rmechaber's coment, and I certainly didn't intend to suggest that the correct answer and explanation was anything other that what you provided; the reference in your explanation has the same text about the length defaults in all 4 versions, and is the same page that I would consult to get this information. The SQL 2000 equivalent of that page doesn't specify a default for cast. Nor does the SQL 2005 page referenced by rmechaber, and I simply wanted to clarify whether his statement that the default length with CAST was different in SQL 2005 had any rational basis or was just an irrational (if without evidence, since it would require the SQL 2005 BoL page for CHAR and VARCHAR to be wrong) leap to a wrong conclusion from the absence of information at his reference. The char and varchar page in BoL for SQL 2000 also failed to specify a default for case (interestingly enough the nchar and nvarchar page specified 30 for cast but didn't specify anything for connect), but I'm fairly sure that it the defaults for length both with cast and with convert were both 30 (as stated on the SQL 2000 cast and convert page) although not 100% certain because can't test it.
Tom
October 4, 2012 at 2:28 pm
L' Eomot Inversé (10/4/2012)
bitbucket-25253 (10/4/2012)
L' Eomot InverséNote in all of the following the bolding of parts of the explanation has been done by myself.
...
...
...
As for running my own tests, if I did do so, it was when I was using SQL 2000 and that would have been some 10 or 12 years ago, and to be truthful, I doubt if I did run tests back then. Naive enough back then to believe all I read in BOL as the truth and nothing but the truth.
Ron,
The comments you are responding to you were in response to rmechaber's coment, and I certainly didn't intend to suggest that the correct answer and explanation was anything other that what you provided; the reference in your explanation has the same text about the length defaults in all 4 versions, and is the same page that I would consult to get this information. The SQL 2000 equivalent of that page doesn't specify a default for cast. Nor does the SQL 2005 page referenced by rmechaber, and I simply wanted to clarify whether his statement that the default length with CAST was different in SQL 2005 had any rational basis or was just an irrational (if without evidence, since it would require the SQL 2005 BoL page for CHAR and VARCHAR to be wrong) leap to a wrong conclusion from the absence of information at his reference. The char and varchar page in BoL for SQL 2000 also failed to specify a default for case (interestingly enough the nchar and nvarchar page specified 30 for cast but didn't specify anything for connect), but I'm fairly sure that it the defaults for length both with cast and with convert were both 30 (as stated on the SQL 2000 cast and convert page) although not 100% certain because can't test it.
Sorry, please accept my apologies for the mistake I made.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply