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


OUTPUT & NEWID()


OUTPUT & NEWID()

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

Group: General Forum Members
Points: 282 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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2994 Visits: 2076
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
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3323 Visits: 612
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 Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 117
Good Question...
Thank you...
Abi Chapagai
Abi Chapagai
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1926 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
SSC Eights!
SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)SSC Eights! (805 reputation)

Group: General Forum Members
Points: 805 Visits: 237
Surprised...
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35904 Visits: 11361
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
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: 10693 Visits: 7891
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 Guru
    SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

    Group: General Forum Members
    Points: 68077 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