OUTPUT - 1

Question

Post reply

OUTPUT - 1

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

ldorian81 SSCommitted Points: 1694 More actions · Answer 1

[Quote]

--Using SQL Server 2008R2 or 2012

[/Quote]

#Sarcasm 😛

Regards
ld

Stoke-on-Trent
United Kingdom

If at first you don't succeed, go to the pub and drink away your current thought plan.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 2

Thanks 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.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

very nice question as doesn't know much about OUTPUT clause 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Raghavendra Mudugal (2/27/2013)
(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)

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_AS

edit: add missing bit, so it makes sense

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 5

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?

Tom

Mike Hays SSCommitted Points: 1871 More actions · Answer 6

Ouch, my collation was SQL_Latin1_General_CP1_CS_AS. This became a lesson on collation instead of the OUTPUT clause. Opps.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

L' Eomot Inversé (2/27/2013)
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?

Reading the supporting link (further down the page)

output_table
Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 8

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 9

Mike Hays (2/27/2013)
Ouch, my collation was SQL_Latin1_General_CP1_CS_AS. This became a lesson on collation instead of the OUTPUT clause. Opps.

No No... you mean WhOoPs! 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Dana Medley SSCertifiable Points: 6764 More actions · Answer 10

mtassin (2/27/2013)
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.

+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. 😀

Everything is awesome!

theboyholty Ten Centuries Points: 1050 More actions · Answer 11

Another 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.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 12

L' Eomot Inversé (2/27/2013)

However, the default isn't Latin1_General_CI_AI, it's case insensitive. But it's accent sensitive - CI_AS

actually... mine does. I am using the below version

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)

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

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.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Venkat Jonnalagadda Mr or Mrs. 500 Points: 510 More actions · Answer 13

I 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..!

___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:

Primo Dang SSCrazy Points: 2643 More actions · Answer 14

Got 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.

theboyholty Ten Centuries Points: 1050 More actions · Answer 15

There 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.