April 21, 2011 at 9:42 am
In the BOL for SET ROWCOUNT, it says
SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.
SET ROWCOUNT is ignored in INSERT, UPDATE, and DELETE statements when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause.
So I setup a test to verify:
CREATE TABLE #Test_Rowcount (Value VARCHAR(10));
SET ROWCOUNT 2;
INSERT INTO #Test_Rowcount (Value)
SELECT TOP 4 * FROM (
SELECT 'One' V UNION ALL
SELECT 'Two' UNION ALL
SELECT 'Three' UNION ALL
SELECT 'Four' UNION ALL
SELECT 'Five'
) X;
SET ROWCOUNT 0;
SELECT * FROM #Test_Rowcount;
DROP TABLE #Test_Rowcount;
Which only returns 2 rows! Why isn't SET ROWCOUNT ignored in the INSERT as described in the BOL? Or am I reading that incorrectly?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 21, 2011 at 11:04 am
toddasd (4/21/2011)
In the BOL for SET ROWCOUNT, it saysSET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.
SET ROWCOUNT is ignored in INSERT, UPDATE, and DELETE statements when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause.
TOP also works with INSERT directly. So this is the case BOL was talking about:
CREATE TABLE #Test_Rowcount (Value VARCHAR(10));
SET ROWCOUNT 2;
-- This TOP takes precendence over ROWCOUNT
INSERT TOP (4) INTO #Test_Rowcount (Value)
SELECT TOP 4 * FROM (
SELECT 'One' V UNION ALL
SELECT 'Two' UNION ALL
SELECT 'Three' UNION ALL
SELECT 'Four' UNION ALL
SELECT 'Five'
) X;
SET ROWCOUNT 0;
SELECT * FROM #Test_Rowcount;
DROP TABLE #Test_Rowcount;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 21, 2011 at 11:09 am
This...
SET ROWCOUNT 2;
INSERT INTO #Test_Rowcount (Value)
SELECT TOP 4 * FROM (
SELECT 'One' V UNION ALL
SELECT 'Two' UNION ALL
SELECT 'Three' UNION ALL
SELECT 'Four' UNION ALL
SELECT 'Five' ) X;
...puts two rows into the table - which is exactly what BOL says it should do.
Edit: nope, I'm talking rubbish.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 21, 2011 at 1:36 pm
opc.three (4/21/2011)
TOP also works with INSERT directly. So this is the case BOL was talking about:
I didn't know INSERT could have a TOP clause. Now it makes sense. Thank you.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 24, 2011 at 6:09 am
toddasd (4/21/2011)
I didn't know INSERT could have a TOP clause.
It's a good thing to know, because SET ROWCOUNT will not work with INSERT, DELETE, or UPDATE in the next version of SQL Server (SQL11 aka Denali).
See http://msdn.microsoft.com/en-us/library/ms187043.aspx for details, e.g.:
Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that DELETE, INSERT, and UPDATE statements that currently are using SET ROWCOUNT be rewritten to use TOP.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 24, 2011 at 6:42 am
SQLkiwi (4/24/2011)
toddasd (4/21/2011)
I didn't know INSERT could have a TOP clause.It's a good thing to know, because SET ROWCOUNT will not work with INSERT, DELETE, or UPDATE in the next version of SQL Server (SQL11 aka Denali).
See http://msdn.microsoft.com/en-us/library/ms187043.aspx for details, e.g.:
Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that DELETE, INSERT, and UPDATE statements that currently are using SET ROWCOUNT be rewritten to use TOP.
A very good thing to know indeed 🙂
Glad I do not have any systems relying on that feature 😛
Mind you at the rate we move by the time we get to 'Denali' it will be past its sell by date 🙁
Far away is close at hand in the images of elsewhere.
Anon.
April 25, 2011 at 6:43 am
BOL is right, Ser rowcount overwrites the TOP function used in select statement. The statements will get executed from right to left so first Select statement will get executed and will return only 2 rows and that would get in serted into temp table.
April 25, 2011 at 7:42 am
SQLkiwi (4/24/2011)
toddasd (4/21/2011)
I didn't know INSERT could have a TOP clause.It's a good thing to know, because SET ROWCOUNT will not work with INSERT, DELETE, or UPDATE in the next version of SQL Server (SQL11 aka Denali).
See http://msdn.microsoft.com/en-us/library/ms187043.aspx for details, e.g.:
Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that DELETE, INSERT, and UPDATE statements that currently are using SET ROWCOUNT be rewritten to use TOP.
SET ROWCOUNT does not seem like a "best practices" way of doing things so it makes sense to deprecate the feature. As soon as I read this in the BOL, I searched the code base I support and found only a few instances where SET ROWCOUNT is used (and only in SELECTS).
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 25, 2011 at 7:50 am
Ajay.Kedar (4/25/2011)
BOL is right, Ser rowcount overwrites the TOP function used in select statement. The statements will get executed from right to left so first Select statement will get executed and will return only 2 rows and that would get in serted into temp table.
That's not the part that is in question. Look at this:
CREATE TABLE #Test_Rowcount (Value VARCHAR(10));
SET ROWCOUNT 2;
-- This TOP takes precendence over ROWCOUNT
INSERT TOP (4) INTO #Test_Rowcount (Value)
SELECT * FROM (
SELECT 'One' V UNION ALL
SELECT 'Two' UNION ALL
SELECT 'Three' UNION ALL
SELECT 'Four' UNION ALL
SELECT 'Five'
) X;
SET ROWCOUNT 0;
SELECT * FROM #Test_Rowcount;
DROP TABLE #Test_Rowcount;
The INSERT TOP (4) ignores the SET ROWCOUNT 2 and inserts 4 rows anyway. That's the part opc.three pointed out to me.
Now, "executed from right to left"? WTF?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 25, 2011 at 10:45 am
toddasd (4/25/2011)
The INSERT TOP (4) ignores the SET ROWCOUNT 2 and inserts 4 rows anyway. That's the part opc.three pointed out to me.
That's true - but only if the TOP is specified on the INSERT, DELETE, or UPDATE: that TOP 'overrides' the SET ROWCOUNT setting. As another example (of how confusing this is) consider:
CREATE TABLE #Test_Rowcount (Value VARCHAR(10));
SET ROWCOUNT 4;
INSERT INTO #Test_Rowcount (Value)
SELECT TOP (2) * FROM (
SELECT 'One' V UNION ALL
SELECT 'Two' UNION ALL
SELECT 'Three' UNION ALL
SELECT 'Four' UNION ALL
SELECT 'Five'
) X;
SET ROWCOUNT 0;
SELECT * FROM #Test_Rowcount;
DROP TABLE #Test_Rowcount;
That code results in two rows being inserted. There are two Top iterators in the query plan. One is for the TOP specified in the query, and the other is a 'rowcount' Top, used to enforce the effective SET ROWCOUNT setting.
toddasd (4/25/2011)
Now, "executed from right to left"? WTF?
He was referring to the fact that query plans are often read right to left. They are executed left to right, to be technically correct about it, but the data flow is indeed usually top-right-toward-the-root, so it is often practical to look at plans that way.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 25, 2011 at 11:50 am
Not only confusing, but dangerous! I'm reading Defensive Database Programming by Kuznetsov and he describes where any triggers on the table in the code above will be affected by the SET ROWCOUNT statement. Sounds better to skip the SET ROWCOUNT and just use TOP.
Thanks for the great explanations on both parts, Paul.
-todd
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 25, 2011 at 12:01 pm
SET ROWCOUNT is supposed to be deprecated in the future. I'd try to work around it wherever you can
April 25, 2011 at 12:21 pm
toddasd (4/25/2011)
Not only confusing, but dangerous! I'm reading Defensive Database Programming by Kuznetsov and he describes where any triggers on the table in the code above will be affected by the SET ROWCOUNT statement. Sounds better to skip the SET ROWCOUNT and just use TOP.
Absolutely right. It is quite common to see SET ROWCOUNT 0; at the top of trigger code for exactly that reason. And yes, Alex's book is excellent.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 25, 2011 at 10:59 pm
Now, "executed from right to left"? WTF?
In the new example the top has been mentioned in INSERT statement so as per BOL, it will not get overwrite by SET ROWCOUNT because it is in insert statement. And query gets executed from right to left, it will execute select statement and will return 5 rows but due to TOP function in the iNSERT will only allowed to insert TOP (4) rows into the temp table.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply