Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

The OUPUT Command Expand / Collapse
Author
Message
Posted Friday, June 1, 2007 10:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/3041.asp






My Blog: http://dineshasanka.spaces.live.com/
Post #370745
Posted Sunday, July 8, 2007 9:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 8, 2013 5:59 PM
Points: 83, Visits: 57

Very useful hint

Regards




Post #379839
Posted Sunday, July 8, 2007 11:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 6, 2011 7:41 AM
Points: 20, 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

Post #379847
Posted Sunday, July 8, 2007 11:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:49 AM
Points: 22, Visits: 119

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

Post #379852
Posted Monday, July 9, 2007 12:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 4, 2013 12:39 AM
Points: 95, 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 =

 

Post #379857
Posted Monday, July 9, 2007 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:18 AM
Points: 2, Visits: 31
Useful tip, but I think the editor should have done more editing (and proofreading.)
Post #379925
Posted Monday, July 9, 2007 5:36 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 3:55 PM
Points: 1,473, Visits: 575
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.
Post #379928
Posted Monday, July 9, 2007 5:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210
sorry for the query errors






My Blog: http://dineshasanka.spaces.live.com/
Post #379929
Posted Monday, July 9, 2007 6:26 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906

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
Post #379950
Posted Monday, July 9, 2007 7:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210

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/
Post #379967
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse