CTE and ROWCOUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE and ROWCOUNT

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-02 : 10:49:13

Hi,

If I use the SET ROWCOUNT before my CTE, will this work just like when using a temp table? i.e. the temp table/CTE will not be loaded with more than the value of ROWCOUNT ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-02 : 11:37:38

from Books Online:

quote:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

Be One with the Optimizer
TG

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-02 : 16:15:58

I tried using the TOP statement in my SELECT statement that loads the CTE, but for some reason I couldn't get it to work.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-02 : 18:01:27

Do you want help with the CTE statement? If so you'll have to post the code and why it's not working (error? wrong results?)

But sounds like unless you are performing Insert,Update,orDelete rowcount should continue to work currently and for future releases.

Be One with the Optimizer
TG

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-03 : 10:26:01

Well I am using ROWCOUNT with a CTE, which is INSERTing into a CTE no?

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-03 : 10:27:00

My CTE is like this:

SET @topCount = (pageNumber + 1) * @pageSize;

WITH MyCTE (articleID, rowNumber) AS
(
SELECT TOP @topCount articleID, ROW_NUMBER() OVER (ORDER BY dtCreated DESC) AS rowNumber
FROM Articles a (NOLOCK)
WHERE userID = @userID
ORDER BY dtCreated DESC
)

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-03 : 10:27:35

Error msg: Msg 102, Level 15, State 1, Procedure Articles_Get, Line 27
Incorrect syntax near '@topCount'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 10:35:22

quote:
Originally posted by sql777

Error msg: Msg 102, Level 15, State 1, Procedure Articles_Get, Line 27
Incorrect syntax near '@topCount'.

can you try like this:-

SET @topCount = (pageNumber + 1) * @pageSize;

WITH MyCTE (articleID, rowNumber) AS
(
SELECT TOP (@topCount) articleID, ROW_NUMBER() OVER (ORDER BY dtCreated DESC) AS rowNumber
FROM Articles a (NOLOCK)
WHERE userID = @userID
ORDER BY dtCreated DESC
)

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-03 : 14:29:25

Thanks, the (@topCount) made it work just fine.

Now is there a different between using TOP and SET ROWCOUNT?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 23:11:58

Read this. This expalins the difference between both and when each can be used:-
http://www.4guysfromrolla.com/webtech/070605-1.shtml

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources