InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 18:50:17 GMT20http://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxPretty good question (though you can get some issues based on collations etc as I work on environment based on different collation then default). Interesting. :-)Mon, 08 Apr 2013 05:37:36 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]Raghavendra Mudugal (2/27/2013)[/b][hr]Very interesting question, did not know until now... and I answered all correct with just mental work :-) and I can feel my confidence is somewhat kicking in back..quote]+1Mon, 11 Mar 2013 10:19:59 GMTSathishKhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxThanks! Good question.Tue, 05 Mar 2013 11:13:54 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxInteresting question.I wonder why people thinking too complex[i][b](colation)[/b] [/i] for simple task.Mon, 04 Mar 2013 04:43:03 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxAwesome Question...Thank You.Thu, 28 Feb 2013 15:25:09 GMTpchiragshttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxYes indeed nice question.Ofcourse the answer depends on the collation of COL1 but if collation is not explicitly defined for COL1 during create statement then we need to assume that column is using the default collation of sql server database which is [b]SQL_Latin1_General_CP1_CI_AS (Case Insensitive, Accent Sensitive)[/b]Thu, 28 Feb 2013 03:36:58 GMTMomhammad Atifhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]batgirl (2/27/2013)[/b][hr]Many years ago I was working to support a SQL Server based accounting system and had a very troubling issue that was ultimately found to have been caused by case sensitivity. I learned the hard way that it can matter :-)[/quote]I learnt the hard way too: back in SQL 2000 days the default collation was one of the SQL collations (presumably at latin one, but I don't recall which) not a windows collation; but our code was all designed to work with Latin_General_CI_AS (except for bits designed to work with Arabic or other languages using non-Latin alphabets). The installation team managed, despite very clear instructions, to install servers at some customer sites with the default collation (and the wrong default language too, in many cases - they must have actively overridden the SQL server defaults there) and the resulting problems in getting the systems up and running correctly were pretty horrendous. Eventually the installation team was successfully educated, and it stopped happening, but while it was happening it made life very hard.Wed, 27 Feb 2013 12:47:41 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxThanks for the question.Wed, 27 Feb 2013 12:46:23 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]Raghavendra Mudugal (2/27/2013)[/b][hr]actually... mine does. I am using the below version[code="sql"]Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)[/code]and the model database's default collation is "Latin1_General_CI_AI" and when installing the sql express and under server configuration it was default selectedso i guess it mainly depends on what you select in the core setup, and Microsoft has made this as the default choice in the setup.[/quote]Interesting. I wonder if this is a new thing in SQL 2012, or maybe a difference between 32-bit and 64-bit - I'm still on 2008R2 32 bit and the default for both developer edition and express edition is CI_AS (although when I picked up the 2008R2 version of AdventureWorks it had a database collation Latin1_General_CI_AI). Or it may even be a difference between different countries.Wed, 27 Feb 2013 12:33:09 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxThanks, nice questionWed, 27 Feb 2013 11:15:57 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxWow - what a nice question! Thanks, Ron!Wed, 27 Feb 2013 10:52:18 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxMany years ago I was working to support a SQL Server based accounting system and had a very troubling issue that was ultimately found to have been caused by case sensitivity. I learned the hard way that it can matter :-)Wed, 27 Feb 2013 09:52:02 GMTbatgirlhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxThere have been a number of complaints about the case sensitivity issue (and one of them from myself - I admit), but the fact is that these are things that we need to consider when developing and managing SQL Server databases. A database will often behave in a surprising way, or in a way that totally baffles us and it can be something as fundamental as the collation set on a database.So on reflection, I say well done to the poster of the question, although I still don't think the upper/lower case issue it was intentional. If this question helps just one person fix an issue that has been bothering them, then it has been a useful exercise, and its certainly got people talking.Wed, 27 Feb 2013 08:50:55 GMTtheboyholtyhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxGot it wrong because I thought Output.ID was an identity column and couldn't receive insert values. D'oh!Noticed the difference in capitalisation, but assumed the base to be case-insensitive, as it usually is in these questions.Wed, 27 Feb 2013 08:45:45 GMTPrimo Danghttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxI learnt something new today.. I got the answer right but after reading about output and taking for granted the default case insensitive collation. But when I realized that output is well before 2012, I was suprised how much I dont know about sql server.. I guess still a beginner..!Wed, 27 Feb 2013 08:27:18 GMTvenkat9.sqlhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]L' Eomot Inversé (2/27/2013)[/b][hr][quote] However, the default isn't Latin1_General_CI_AI, it's case insensitive. But it's accent sensitive - CI_AS [/quote]actually... mine does. I am using the below version[code="sql"]Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)[/code]and the model database's default collation is "Latin1_General_CI_AI" and when installing the sql express and under server configuration it was default selected[img]http://2.bp.blogspot.com/-5C5PYdr7qnA/UI43gky4lcI/AAAAAAAAALs/axIfeWxeZSs/s400/9.png[/img]so i guess it mainly depends on what you select in the core setup, and Microsoft has made this as the default choice in the setup.Wed, 27 Feb 2013 07:46:46 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxAnother ambiguous question.If you have case sensitive collation, then the 'Whoops' row won't be deleted.Was the lowercase 'w' in the DELETE statement intentional (not to mention the two 'Dbo' schema references rather than 'dbo')? They must have been intentional, surely no-one would make such a basic typo when posting a question to this kind of forum. The lack of care given to this detail display a complete lack of working knowledge or experience of SQL Server from the contributor.And I ticked the 'ONE row was deleted' option PLUS the 'TWO rows were deleted'. Both were true yet I get the question wrong.AMATEURS.Wed, 27 Feb 2013 07:41:23 GMTtheboyholtyhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]mtassin (2/27/2013)[/b][hr]Good question... +1 for me.Shame on the typo... I didn't even notice the whoops vs Whoops issue until I read the thread in here.[/quote]+1 . It wasn't even a blip on my radar until I got here, so lucky for me that wasn't a stipulation of the question. :-DWed, 27 Feb 2013 07:37:18 GMTDana Medleyhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]Mike Hays (2/27/2013)[/b][hr]Ouch, my collation was SQL_Latin1_General_CP1_CS_AS. This became a lesson on collation instead of the OUTPUT clause. Opps.[/quote]No No... you mean WhOoPs! :)Wed, 27 Feb 2013 06:53:48 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxGood question... +1 for me.Shame on the typo... I didn't even notice the whoops vs Whoops issue until I read the thread in here.Wed, 27 Feb 2013 06:52:53 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]L' Eomot Inversé (2/27/2013)[/b][hr]Nice question, but two (very minor) niggles: (i) what was the last statement for? and (ii) why refer in the explanation to the output clause returning rows to the client when the question is actually about the output clause inserting rows into a table?[/quote]Reading the supporting link (further down the page)[quote]output_tableSpecifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.[/quote]Wed, 27 Feb 2013 06:37:26 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxOuch, my collation was SQL_Latin1_General_CP1_CS_AS. This became a lesson on collation instead of the OUTPUT clause. Opps.Wed, 27 Feb 2013 06:35:51 GMTMike Hayshttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxNice question, but two (very minor) niggles: (i) what was the last statement for? and (ii) why refer in the explanation to the output clause returning rows to the client when the question is actually about the output clause inserting rows into a table?Wed, 27 Feb 2013 06:25:52 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]Raghavendra Mudugal (2/27/2013)[/b][hr](note : I know (and most of all know) collation is very important and each server/database/table or any object which is qualified for using collation has one by default whether or not we set it explicitly and result of the underlying task depends on it... I am not arguing that it must not be used or mentioned... but when the collation is not mentioned by the author in any qtod lets take it as the default say "Latin1_General_CI_AI"; mentioning the collation and its varied results in every qtod's reply is really... (dont know why it is bothering me...)... and i know now you all have a lot to say on this and like i said "i am not arguing here" its just the same replies to qtod.. kind of old gig now.. and I apologize if I offended anyone with this comment, it is not intentional; it is in general)[/quote]I agree, no need to mention the collation when it's the default collation (although some will always complain). However, the default isn't Latin1_General_CI_AI, it's case insensitive. But it's accent sensitive - CI_ASedit: add missing bit, so it makes senseWed, 27 Feb 2013 06:23:20 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxvery nice question as doesn't know much about OUTPUT clause :-)Wed, 27 Feb 2013 06:17:54 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxThanks for the question Ron. Clever of you to have used the lower case 'w' to make the conspiracy minded believe this was yet another question on collation.Wed, 27 Feb 2013 05:04:59 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[Quote]--Using SQL Server 2008R2 or 2012 [/Quote]#Sarcasm :-PWed, 27 Feb 2013 04:49:23 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]ldorian81 (2/27/2013)[/b][hr]took a stab in the dark... missed totallygood question, look forward to playing with it when i finally get 2012 :-P;-)[/quote]Why would you require 2012 to play with OUTPUT clause? This got introduced in 2005 :-)Wed, 27 Feb 2013 04:47:59 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxtook a stab in the dark... missed totallygood question, look forward to playing with it when i finally get 2012 :-P;-)Wed, 27 Feb 2013 04:26:15 GMTldorian81http://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxI honestly had not used the OUTPUT clause before, I can see how it would be helpful, so for me it's a good question, regardless of the case sensitivity blip.Wed, 27 Feb 2013 03:26:29 GMTcall.copsehttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]handkot (2/26/2013)[/b][hr]the answer is ambiguous and depends from collationsget my point back :-)[/quote]Does that mean you assumed the collation was Case Sensitive? Normally, when no specific information is given, it's safe to assume the default, especially considering the context of the question.Wed, 27 Feb 2013 02:23:51 GMTRichard Warrhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxGood question, Ron, thanksWed, 27 Feb 2013 01:51:28 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]demonfox (2/27/2013)[/b][hr][quote][b]Kingston Dhasian (2/26/2013)[/b][hr][quote][b]handkot (2/26/2013)[/b][hr]the answer is ambiguous and depends from collations[/quote]I agree somewhatThe answers will change if the database collation is case sensitiveBut, overall a very good question.[/quote]Well, I did see the whoops with a small w , but carried on with default collation of case insensitive .. a typo I guess :-Pthanks for the question .[/quote]+1Wed, 27 Feb 2013 01:13:20 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]Kingston Dhasian (2/26/2013)[/b][hr][quote][b]handkot (2/26/2013)[/b][hr]the answer is ambiguous and depends from collations[/quote]I agree somewhatThe answers will change if the database collation is case sensitiveBut, overall a very good question.[/quote]Well, I did see the whoops with a small w , but carried on with default collation of case insensitive .. a typo I guess :-Pthanks for the question .Wed, 27 Feb 2013 01:10:18 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxVery interesting question, did not know until now... and I answered all correct with just mental work :-) and I can feel my confidence is somewhat kicking in back..(note : I know (and most of all know) collation is very important and each server/database/table or any object which is qualified for using collation has one by default whether or not we set it explicitly and result of the underlying task depends on it... I am not arguing that it must not be used or mentioned... but when the collation is not mentioned by the author in any qtod lets take it as the default say "Latin1_General_CI_AI"; mentioning the collation and its varied results in every qtod's reply is really... (dont know why it is bothering me...)... and i know now you all have a lot to say on this and like i said "i am not arguing here" its just the same replies to qtod.. kind of old gig now.. and I apologize if I offended anyone with this comment, it is not intentional; it is in general)Wed, 27 Feb 2013 01:03:28 GMTRaghavendra Mudugalhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]handkot (2/26/2013)[/b][hr]the answer is ambiguous and depends from collations[/quote]+1;-)Wed, 27 Feb 2013 00:51:06 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxNice Question Ron. Thanks :-)Wed, 27 Feb 2013 00:09:49 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxVery nice question, thanks Ron.Tue, 26 Feb 2013 23:27:53 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspx[quote][b]handkot (2/26/2013)[/b][hr]the answer is ambiguous and depends from collations[/quote]I agree somewhatThe answers will change if the database collation is case sensitiveBut, overall a very good question.Tue, 26 Feb 2013 23:26:44 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1424320-1222-1.aspxReally very good Question.Tue, 26 Feb 2013 22:49:30 GMTDanny Ocean