April 29, 2011 at 12:34 pm
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
June 23, 2011 at 8:12 pm
Surprised...
June 26, 2011 at 4:18 am
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
SQLkiwi blog
@SQL_Kiwi
June 27, 2011 at 7:44 am
SQLkiwi (6/26/2011)
One can delete 9 rows non-deterministically with this code:
DELETE TOP (9) @tThough 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);June 27, 2011 at 7:48 am
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!
June 27, 2011 at 7:55 am
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 27, 2011 at 7:59 am
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.
June 27, 2011 at 8:01 am
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 27, 2011 at 7:32 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 20, 2011 at 2:41 am
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.
February 29, 2012 at 7:58 pm
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.
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply