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 Tuesday, April 26, 2011 5:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 3, 2012 5:59 AM
Points: 226, Visits: 109
i also chosen none of the above which actully should be the answer :) also i am not at all satisfied with the explanation saying that it is a bug and can't fixed :)

what is the use of rasing such questions which itself a question?
and prompting others that u are wrong!!!!!!!!!!!!!!!!!!!!
Post #1098528
Posted Tuesday, April 26, 2011 7:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:55 AM
Points: 1,639, Visits: 1,985
bitbucket-25253 (4/25/2011)
One of the main objectives of the QOD is to teach/educate and that your QOD has certainly done.


I agree with this. My comment about how the query could be modified so it'll always remove 9 rows was more from the attitude of, "If someone is dealing with this issue here's one way to handle it," and less from, "the question was poor." I certainly didn't know about this behavior before and it's good to know it's out there.
Post #1098608
Posted Tuesday, April 26, 2011 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 12, 2012 6:59 AM
Points: 46, Visits: 160
What about the interesting issues raised by the question?

To me, it is strange why the sqlserver team is avoiding adding an option to materialize intermediate tables from CTE's. Oracle has this with the /*+ materialize */ hint.

If we could have the option to get rid of the automatic expansion of inline views/CTEs/TVF, we could control the query execution by query semantics instead of fighting stale statistics with query hints.

A person writing query, will often know instinctively the best sequence of steps to execute a query would be.

"I'll get my few candidates from this CTE1, and then get some more data with CTE2 that consumes CTE1 and then I'll join a little more. "

To get this to perform consistently, you'll often see developers make up all kinds of #temp tables etc, because the got burned by the optimizer one time to many.

Its no secret that the optimizer will generate strange plans when there are a lot of joins and the statistics a not as fresh as they could be.

On Oracle, /*+ materialize */ did the trick. On sqlserver you'll have to do the materializing yourself, making the code less readable and maintainable.
Post #1098614
Posted Wednesday, April 27, 2011 2:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:27 AM
Points: 2,624, Visits: 583
I think its a a nice question, but wrong answer and explanation. I would say that NONE of the above should be the correct answer, because none are guaranteed.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Post #1099245
Posted Thursday, April 28, 2011 6:13 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, February 17, 2014 9:44 AM
Points: 127, Visits: 105
Good Question...
Thank you...
Post #1100159
Posted Friday, April 29, 2011 12:34 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:36 PM
Points: 1,786, Visits: 1,116
Jason,
Great question with the exposure of NEWID(). I got it wrong but i learnt something today. I am not worried about the points we get here but am more interested towards learning every day from SQL Experts around the globe.

Thanks again and look forward more questions.

Thanks,
Abi
Post #1100997
Posted Thursday, June 23, 2011 8:12 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Surprised...
Post #1130908
Posted Sunday, June 26, 2011 4:18 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 9,928, Visits: 11,203
One can delete 9 rows non-deterministically with this code:
DELETE TOP (9) @t

Though in practice you're likely to see the same 9 rows deleted, it's just not guaranteed. To go further and delete 9 rows at random reliably:

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

Now I know that wasn't really the point of the question, but I wanted to get it out of the way.

The broader question is whether the optimizer should be free to reorder expressions and, in particular, to evaluate those expressions more than once, even when that expression is non-deterministic. On balance, I think I prefer better query plans for a broad class of queries over the alternative.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1131753
Posted Monday, June 27, 2011 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 1,816, Visits: 5,911
SQLkiwi (6/26/2011)
One can delete 9 rows non-deterministically with this code:
DELETE TOP (9) @t

Though in practice you're likely to see the same 9 rows deleted, it's just not guaranteed. To go further and delete 9 rows at random reliably:

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

Now I know that wasn't really the point of the question, but I wanted to get it out of the way.

The broader question is whether the optimizer should be free to reorder expressions and, in particular, to evaluate those expressions more than once, even when that expression is non-deterministic. On balance, I think I prefer better query plans for a broad class of queries over the alternative.


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.


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 #1132125
    Posted Monday, June 27, 2011 7:48 AM


    SSC-Insane

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

    Group: General Forum Members
    Last Login: Thursday, November 27, 2014 4:23 AM
    Points: 20,584, Visits: 9,624
    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!
    Post #1132127
    « Prev Topic | Next Topic »

    Add to briefcase «««23456»»

    Permissions Expand / Collapse