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 «««23456

OUTPUT & NEWID() Expand / Collapse
Author
Message
Posted Monday, June 27, 2011 7:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 1,651, Visits: 5,202
Ninja's_RGR'us (6/27/2011)
mister.magoo (6/27/2011)And the same thing re-written using a CTE - just to show an alternate way to format the code:


;WITH ToDelete AS
(
SELECT TOP (9) *
FROM @t AS t
ORDER BY
NEWID()
)
DELETE ToDelete
OUTPUT deleted.*

Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.



I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart!


I always enjoy words like that - hope they still make me chuckle when I am 90!

I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.

I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1132130
    Posted Monday, June 27, 2011 7:59 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Thursday, January 16, 2014 12:03 PM
    Points: 21,376, Visits: 9,594
    mister.magoo (6/27/2011)
    Ninja's_RGR'us (6/27/2011)
    mister.magoo (6/27/2011)And the same thing re-written using a CTE - just to show an alternate way to format the code:


    ;WITH ToDelete AS
    (
    SELECT TOP (9) *
    FROM @t AS t
    ORDER BY
    NEWID()
    )
    DELETE ToDelete
    OUTPUT deleted.*

    Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.



    I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart!


    I always enjoy words like that - hope they still make me chuckle when I am 90!

    I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.

    I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).


    I personally have set implicit_transactions ON by default in ssms.

    The first work I type IS ROLLBACK and then I push it down at the end of the script. That way I have to comment out rollback and then type commit to push the changes into the db. Saved my but once or twice so far.
    Post #1132133
    Posted Monday, June 27, 2011 8:01 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 5:20 PM
    Points: 1,651, Visits: 5,202
    Ninja's_RGR'us (6/27/2011)
    mister.magoo (6/27/2011)
    Ninja's_RGR'us (6/27/2011)
    mister.magoo (6/27/2011)And the same thing re-written using a CTE - just to show an alternate way to format the code:


    ;WITH ToDelete AS
    (
    SELECT TOP (9) *
    FROM @t AS t
    ORDER BY
    NEWID()
    )
    DELETE ToDelete
    OUTPUT deleted.*

    Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.



    I think it's much clearer that way and it makes it a little easier and less risky to move between select and delete without having a brainfart!


    I always enjoy words like that - hope they still make me chuckle when I am 90!

    I tend to show people who are less experienced how to do it this way for precisely that reason. They can write the SELECT on it's own, confirm that it returns the correct set of data and then simply "wrap it all up" in a CTE and perform the update/delete.

    I also like to get them to pop an OUTPUT clause in there and wrap it all in a transaction with rollback to check the results are really correct before they commit - and to be honest, if it's production DB - that's the way I operate as well (mostly).


    I personally have set implicit_transactions ON by default in ssms.

    The first work I type IS ROLLBACK and then I push it down at the end of the script. That way I have to comment out rollback and then type commit to push the changes into the db. Saved my but once or twice so far.


    Yeah - SSMS Tools does a similar job, when you open a new query, it stuffs BEGIN TRAN....ROLLBACK TRAN in there for you...


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1132136
    Posted Monday, June 27, 2011 7:32 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 5:59 AM
    Points: 11,168, Visits: 10,931
    mister.magoo (6/27/2011)
    Why did I bother posting this?.... well, I like this construct for updates mostly as the visual seperation of the SELECT from the UPDATE or the DELETE makes it entirely clear what you are doing - Here is the set of data I am interested in....now here is what I am going to do to it.

    Interestingly, the reason I chose to use the syntax I did is that most people assume that this type of query _has_ to be written using a CTE. There are two schools of thought on CTEs versus nested subqueries. I used to always use CTEs, but when there are several nested CTEs it can be be more convenient to debug written as nested subqueries - all you have to do is highlight a section of the code and run it; with nested CTEs you usually have to edit the query to select directly from whichever intermediate CTE you are interested in. Overall, though, it's purely a question of style; both are correct.




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1132586
    Posted Wednesday, July 20, 2011 2:41 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, November 25, 2013 11:06 PM
    Points: 196, Visits: 62
    In SQL Server 2005, it won't give the answer other the syntax error since we have to use
    'insert into table ' with 'union all' statement for the insertion to be done.
    Post #1144784
    Posted Wednesday, February 29, 2012 7:58 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Friday, April 04, 2014 8:03 AM
    Points: 100, Visits: 134
    Brigadur (4/25/2011)
    I answered "None of the above". After getting the explanation I still claim I answered right. The question was "... how many rows are gauranteed ...", so answer "Any of the above", well, questionable.

    Interesting question though


    Not trying to beat the dead horse on this one but I have to agree. How can you say that a value is "Gauranteed" if another value of more or less (mostly the less is what bugs me) could also be true. Say you picked 9 (as in 9 rows are gauranteed) but the next exec you get 7, well then you could not have "Gauranteed" 9 since you didnt get 9 and therefore failed to meet your Gauranteed Value...

    I think thye should fix it to give credit for NONE as well.
    Post #1259891
    « Prev Topic | Next Topic »

    Add to briefcase «««23456

    Permissions Expand / Collapse