SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The OUPUT Command


The OUPUT Command

Author
Message
Dinesh Asanka
Dinesh Asanka
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 223
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/3041.asp






My Blog: http://dineshasanka.spaces.live.com/
josephgabrie
josephgabrie
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 57

Very useful hint

Regards





DoktorBlue
DoktorBlue
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 17

Beside some missing spaces in the code, which is confusing, I miss information how the Output option returns its values to the devolper.




Kindest Regards,

DoktorBlue

Hans van Dam
Hans van Dam
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 185

Very valuable information to me. Too bad that te conclusion is wrong! There really is a need to perform modifications wherever @@identity or scope_identity() is used because they are not reliable:

  • after a rollback, the assigned identity is returned while it does not exist
  • when some other identity is assigned (in any table) between your insert and reading out @@identity you get that other identity

So, my conclusion would be that it really is nescessary to rewrite code whenever you need the newly assigned identity.

Regards,

Hans van Dam


Johan van Tonder
Johan van Tonder
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 347

Yes indeed. Very valuable feature. Performance can be greatly enhanced by this, where triggers may be replaced. Those wondering about the missing spaces...

In the first example block, SELECTSCOPE_IDENTITY() should be SELECT SCOPE_IDENTITY(). In the last example block where the delete statements are WHEREID = should be WHERE ID =


TJ-205129
TJ-205129
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 31
Useful tip, but I think the editor should have done more editing (and proofreading.)
Jimi Meyer
Jimi Meyer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 787
Missing some key information such as when you cannot use it. Will not work if there is a trigger on the table, or if it is involved in foreign key, or has check constraints. All of those are pretty common.
Dinesh Asanka
Dinesh Asanka
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 223
sorry for the query errors






My Blog: http://dineshasanka.spaces.live.com/
Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: Moderators
Points: 30169 Visits: 2759

Jimi, that's not quite correct. Here's what it says in BOL:

output_table cannot:

  • Have enabled triggers defined on it.
  • Participate on either side of a foreign key constraint.
  • Have CHECK constraints or enabled rules.

You can use it on any table regardless of configuration, it's only the output table where you're landing the results that has restrictions.

The output clause is one of the really nice improvements in 2005. I've seen all too many instances where developers were essentially inserting an alternate primary key so that they could insert multiple rows at once and still get the keys back. If they had used scope_identity() they would have been forced to insert the records one at a time which is not great for performance.

Essentially the output clause exposes the logical inserted and deleted tables that were previously only visible within the trigger execution.



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Dinesh Asanka
Dinesh Asanka
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 223

Andy,

is it possible you to do following changes in the article

SELECTSCOPE_IDENTITY() should be SELECT SCOPE_IDENTITY() in first example block.

In the last example block where the delete statements are WHEREID = should be WHERE ID








My Blog: http://dineshasanka.spaces.live.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search