InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:14:48 GMT20http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice and interesting question.Wed, 07 Mar 2012 15:15:12 GMTzymoshttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxBecause all colors are in proper case one can be led to believe that case sensitivity is a factor. To avoide this confusion it would be better to have all colors in lower or upper case.Tue, 17 Aug 2010 01:03:09 GMThayk-736382http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxGood question! I was shocked to see the four rows when I tried the query!Leaving aside the issue of the COLLATION, I opted for the one row answer, because the first that came to mind was the default value of varchar in the CONVERT function, which is 30.[code="sql"]select convert (varchar, '123456789012345678901234567890123456789012345678901234567890 ')[/code]Wed, 28 Jul 2010 05:47:23 GMTdbuendiabhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxGood question. I got it wrong, because I didn't notice that the length wasn't specified. That will teach me not to skim read code when working out what it does (something I've often told others they must not do, but am still stupid enough to do it myself).Mon, 26 Jul 2010 17:43:23 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxGood question, got it wrong. Didn't know about default length being set to 1 if not declared. Nice job!Tue, 20 Jul 2010 10:34:40 GMTSqlchickenhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxExcellent question. Defaults are always something to keep in mind and to avoid relying on, as they may change.Tue, 20 Jul 2010 09:07:59 GMTKevinC.http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxExcellent question! I don't think it's so much about relying on a default (bad practice) so much as forgetting to specify the length, which may not result in a syntax error, and may return results which are incorrect.Thanks for the reality check.Tue, 20 Jul 2010 08:31:26 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxthanks, this explains everything...:-)[quote][code="sql"]DECLARE @Testvar VARCHAR;SET @Testvar = 'Red';print @Testvar;[/code][/quote]Mon, 19 Jul 2010 23:36:06 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxI liked this question as well, it demonstrated what many assumed to be one thing but was another, in this case the default length of a varchar when none is specified.Mon, 19 Jul 2010 16:49:11 GMTJan Sorensonhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxGood question. Although the first thing I wanted to know was the collation.Mon, 19 Jul 2010 13:10:44 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspx[quote][b]Trey Staker (7/19/2010)[/b][hr]Good Question. I've been stung by this in production early on in my career. Since then I always have specified the length of my varchar's.I've read the complaints about coallation. I always assume default out of the box coallation for QOD but you all have a point since many of you are outside the US and have different coallations. I think if we took in consideration every tiny thing that we normally assume the question may take pages and pages to set up.[/quote]Thanks - your comment made me wonder why SQL doesn't just throw an error when the varchar definition doesn't include a size. What is the purpose of having two default values based on the kind of declaration vs. CAST and CONVERT, given the possible bad side effects?Just wondering.- webrunnerMon, 19 Jul 2010 12:24:56 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxExcellent question. Thanks for posting it.Mon, 19 Jul 2010 12:19:20 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspx[quote][b]Trey Staker (7/19/2010)[/b][hr]I've read the complaints about coallation. I always assume default out of the box coallation for QOD but you all have a point since many of you are outside the US and have different coallations. I think if we took in consideration every tiny thing that we normally assume the question may take pages and pages to set up.[/quote]Or like Greg stated, he could have put it all in lower, or upper, case and then the collation wouldn't have mattered. But yes, there are lots of things to think about when writing a QotD to make it successful.Mon, 19 Jul 2010 11:17:43 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxGood Question. I've been stung by this in production early on in my career. Since then I always have specified the length of my varchar's.I've read the complaints about coallation. I always assume default out of the box coallation for QOD but you all have a point since many of you are outside the US and have different coallations. I think if we took in consideration every tiny thing that we normally assume the question may take pages and pages to set up.Mon, 19 Jul 2010 11:14:53 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxThis is an excellent question, thank you Greg. It serves as a reminder that "[b]thou shalt not omit the size when declaring a string based variable[/b]" as it can serve as a ground of subtle bugs. For example, int and varchar are implicitly convertible and funny things can happen because of it:[code="sql"]create proc dbo.dump_me( @input varchar)asbegin select @input result;end;go[/code]The proc above has a nasty side effect:[code="sql"]exec dbo.dump_me '123';[/code]produces[code="sql"]result------1[/code]However, removing the single quotes:[code="sql"]exec dbo.dump_me 123;[/code]produces[code="sql"]result------*[/code]While this behaviour is by design, it might catch someone by surprise. The size is omitted, so '123' becomes [b]1[/b] when cast to varchar(1), but implementation of int data type allows returning [b]*[/b] when the number of characters in the int does not fit.OlegMon, 19 Jul 2010 09:56:49 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspx[quote][b]Kanaka (7/19/2010)[/b][hr]I am not sure why sql server 2008 returned 1, inspite of not giving the variable length.I knew this would give a syntax error, however, I tried this and it returned 1 row.:w00t:[/quote]The version of SQL Server doesn't matter. The only way that you will get one record is if you are using a case-sensitive collation.Thanks,GregMon, 19 Jul 2010 09:22:40 GMTJestersGrindhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxI am not sure why sql server 2008 returned 1, inspite of not giving the variable length.I knew this would give a syntax error, however, I tried this and it returned 1 row.:w00t:Mon, 19 Jul 2010 09:04:38 GMTKanakahttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxA lesson that's common to many QotDs is that you should never rely on default values - always be specific, so you know exactly what you're getting, and so does naybody else who reads the code later!A lesson which I break whenever I create a table without specifying 'primary' all over the place ;-)Mon, 19 Jul 2010 08:52:43 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxGood question. In this case I don't consider it a trick question because the lack of a length specification in the statement "DECLARE @TestVar VARCHAR" pointed to behavior based on omitting the length. At that point there is not so much trickery as a real test of knowledge of how SQL behaves in the absence of a length specification. That's a valid test of knowledge, not a trick.I got the question wrong, but that is as it should be because I didn't know the information the question was testing for. I learned something from the question and the documentation, namely: [quote]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.[/quote]http://msdn.microsoft.com/en-us/library/ms176089.aspxI assumed the length is always 30 when a length is not specified, but as the documentation states, it is sometimes 1. That's enough to make the question a valuable one, as far as I am concerned.Thanks,webrunnerMon, 19 Jul 2010 08:20:57 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxI too thought the default length was 30.Not stating the collation setting makes it obvious no matter what actual db settings you work with regularly.Thanks for the question and education.Mon, 19 Jul 2010 07:50:04 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxThanks for all of the feedback. I definitely should have considered case-sensitivity, especially since we have several case-sensitive database here. In hind sight, I should have made everything lower case to avoid confusion. A little back story... I ran into this situation while reviewing a developer's stored procedure that was returning more results than it should have. Up until that point, I had never needed to know the default length of a varchar variable, because I always followed the best practice of explicitly stating the length of a varchar variable. I guess this is why it's a best practice.Thanks,GregMon, 19 Jul 2010 07:23:35 GMTJestersGrindhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice question - I guessed that the collation was case insensitive (the default on any installation I've done), but got it wrong anyway, because I didn't spot that "Green" had an "r" in it. I guess that's Mondays for you.DuncanMon, 19 Jul 2010 07:17:46 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice question.I had to browse my memory from long ago after I saw that VARCHAR declaration. I had forgotten that.Thanks a lot. :cool:Mon, 19 Jul 2010 07:04:45 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspx[quote][b]SanjayAttray (7/18/2010)[/b][hr]Nice question.When the length is not specified in a data definition or variable declaration statement, the default length is 1 ----- Didn't knew this. used to give length to all variables.[/quote]And I really hope that you continue to give length to all variables. Relying on this default length is bad practice. And very confusing, since the default length depends on where the data type is used - in a CONVERT or CAST function, the default is 30; elsewhere it is 1.Mon, 19 Jul 2010 04:41:52 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxA good question about a common misunderstanding. The number of times when a problem posted to usenet or forums could be solved by simply adding the length to a varchar parameter is staggering.My only minor gripe is, as already noted by others, the lack of collate indication. On my default test server, this query would have returned 1 row, as I use a case sensitive collation there. Fortunately I assumed out of the box default settings for the instance, so I replied 4 and got it right.Thanks for the great question; I'm looking forward to seeing more of these, Greg!Mon, 19 Jul 2010 04:40:31 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxHmmmm, it appears I don't have much of a point, but, if I did, it would be that it is not good practice to cast a variable unless it is strongly typed. select * from( select 'red' textfieldunion select 'blue' textfieldunion select 'green' textfieldunion select 'yellow' textfieldunion select 'orange' textfieldunion select 'purple' textfield)xwhere convert(varchar(1),textfield) like '%red%' ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/282cd982-f4fb-4b22-b2df-9e8478f13f6a.htmWhen 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.[quote]Not 100% sure but I think the rule of default length applies only to variable declaration; functions have other rules: CONVERT(VARCHAR, expression) defaults to VARCHAR(30).[/quote]But, I was unaware that the varchar declaration has a default. Normally, I wouldn't think of trying this so now the question has forced me to think outside the box. To wit, perhaps it is a good question after all.Mon, 19 Jul 2010 01:06:52 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice question.I did not notice the variable length.that's why i got wrong.I had answered 1.Mon, 19 Jul 2010 00:31:23 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice question!!Mon, 19 Jul 2010 00:08:02 GMTudayroy15http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxI agree, the collation setting (whether it's case sensitive or not) would bring different results (1 for case-sensitive and 4 for case-insensitive).Sun, 18 Jul 2010 16:18:39 GMTIggy-SQLhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxThis could make the Q "less confusing" but less real-life and interesting:[code="sql"]DECLARE @Testvar VARCHAR;SET @Testvar = 'Red';print @Testvar;[/code]If only there was a remark of the collation set (as UMG developer pointed out) to avoid ambiguity.Sun, 18 Jul 2010 15:11:41 GMThrvoje.piasevolihttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspx[quote][b]Jamie Longstreet-481950 (7/18/2010)[/b][hr]Textfield is the field name... are we not saying the same thing? The textfield is not the same for both the variable and the temp test table. If you remove the temp table and use a sub-query, it makes the issue less confusing. [/quote]I don't understand what you are trying to say. Yes Textfield is the name, but it has nothing to do with the variable, and your example isn't at all what the question is about.The issue is the "DECLARE @Testvar VARCHAR" and knowing that that means the same as "DECLARE @Testvar VARCHAR(1)" and that the SET truncates the data and does not report an error. I'm not sure why you think that is confusing.Sun, 18 Jul 2010 14:45:48 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxBoth 1 and 4 could be the correct answer as Green, Orange and Purple do not actually contain a "R", so if you have a case sensitive collation you would only have one row returned. But I figured that wasn't you were looking for, and "It depends" wasn't a choice. ;-)In any case, nice question, thanks!Sun, 18 Jul 2010 14:40:46 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxTextfield is the field name... are we not saying the same thing? The textfield is not the same for both the variable and the temp test table. If you remove the temp table and use a sub-query, it makes the issue less confusing. You'll excuse me... I'm griping. I don't like the way the question was worded. Should have said that in the first place.Sun, 18 Jul 2010 13:22:05 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspx[quote][b]Jamie Longstreet-481950 (7/17/2010)[/b][hr]Could call it a trick... in another form:select * from(select 'red' union select 'red'union select 'blue'union select 'green'union select 'yellow'union select 'orange'union select 'purple')xwhere textfield like 'red' -- or like '%red%' same thingIn the above form, it's obvious we have no field named "textfield".[/quote]Don't see how this relates to the posted question. No trick in the question just default behavior for unspecified optional parameter.Sun, 18 Jul 2010 12:56:36 GMThrvoje.piasevolihttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice question. I got it right cause I made an effort to examine all kinds of default parameters after encountering something similar. Not 100% sure but I think the rule of default length applies only to variable declaration; functions have other rules: CONVERT(VARCHAR, expression) defaults to VARCHAR(30).My 2cRegards,HrvojeSun, 18 Jul 2010 12:51:34 GMThrvoje.piasevolihttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxNice question.When the length is not specified in a data definition or variable declaration statement, the default length is 1 ----- Didn't knew this. used to give length to all variables.Sun, 18 Jul 2010 07:25:54 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxCould call it a trick... in another form:select * from(select 'red' union select 'red'union select 'blue'union select 'green'union select 'yellow'union select 'orange'union select 'purple')xwhere textfield like 'red' -- or like '%red%' same thingIn the above form, it's obvious we have no field named "textfield".Sat, 17 Jul 2010 16:38:50 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic954419-2742-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/70321/">VARCHAR datatype</A>[/B]Sat, 17 Jul 2010 12:17:27 GMTJestersGrind