Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OUTPUT & NEWID()


OUTPUT & NEWID()

Author
Message
anu1krishna
anu1krishna
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 109
i also chosen none of the above which actully should be the answer Smile also i am not at all satisfied with the explanation saying that it is a bug and can't fixed Smile

what is the use of rasing such questions which itself a question?
and prompting others that u are wrong!!!!!!!!!!!!!!!!!!!! :-)
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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.
Mr. Phantomblot
Mr. Phantomblot
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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.
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2702 Visits: 601
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
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Jagadish Kumar Punnapu
Jagadish Kumar Punnapu
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 111
Good Question...
Thank you...
Abi Chapagai
Abi Chapagai
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 Visits: 1127
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
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 237
Surprised...
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 7827
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Ninja's_RGR'us
    Ninja's_RGR'us
    SSC-Insane
    SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

    Group: General Forum Members
    Points: 20931 Visits: 9671
    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!
    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