Order of Data and Cursor Fetch Next Order

Question

Order of Data and Cursor Fetch Next Order

tinausa

SSCrazy

Points: 2697
More actions
June 28, 2017 at 2:07 pm

#338412

I have inherited some SQL code. The stored proc creates a temp table of some of the views in sys.views.
Then it defines a cursor and loops thru the temp table to call each of the views and do some processing.
CREATE TABLE #temp ( name NVARCHAR(10) ) INSERT INTO #temp SELECT name FROM sys.views WHERE name LIKE 'vwx%' SELECT * FROM #temp vwx1r vwx1d vwx2a vwx2i vwx4 vwx2b vwxg
My question is two fold.
1. would the above insert always return in the same order?
2. If the 1 above returns the same order, would the cursor always pick the next row when doing FETCH NEXT?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

In general never assume that SQL server will return a consistent order without an explicit order by. Even if the system view is consistently returning in a specific order that is no guarantee the temp table would also return in the same order.

pietlinden SSC Guru Points: 63535 More actions · Answer 2

tinausa - Wednesday, June 28, 2017 2:07 PM
I have inherited some SQL code. The stored proc creates a temp table of some of the views in sys.views.
Then it defines a cursor and loops thru the temp table to call each of the views and do some processing.
CREATE TABLE #temp ( name NVARCHAR(10) ) INSERT INTO #temp SELECT name FROM sys.views WHERE name LIKE 'vwx%' SELECT * FROM #temp vwx1r vwx1d vwx2a vwx2i vwx4 vwx2b vwxg
My question is two fold.
1. would the above insert always return in the same order?
2. If the 1 above returns the same order, would the cursor always pick the next row when doing FETCH NEXT?
Thanks.

Order is not guaranteed unless you specify a sort order in the ORDER BY clause. Simple as that.

tinausa SSCrazy Points: 2697 More actions · Answer 3

Thanks for confirming what I was thinking. Then is there a way that I can force the order?
ORDER BY would not work given the names but is there a way I can create something like an array and use that in the cursor?
Tahbnks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

tinausa - Wednesday, June 28, 2017 2:47 PM
Thanks for confirming what I was thinking. Then is there a way that I can force the order?

With an ORDER BY clause. Doesn't have to be a straight order by name. You can have an expression in the ORDER BY, or add another column to the temp table with the required order, but that's how you force an order.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason A. Long SSC-Insane Points: 23788 More actions · Answer 5

tinausa - Wednesday, June 28, 2017 2:47 PM
Thanks for confirming what I was thinking. Then is there a way that I can force the order?
ORDER BY would not work given the names but is there a way I can create something like an array and use that in the cursor?
Tahbnks.

If the views are in order by their object_id in sys.views, you can at it as an additional column in the temp table and sort by that... Just an idea.