Hi
From past one week I have been facing with a strange issue.I did lot more analysis and I come up with few findings.But those were went on top of my head.So I am posting here for suggestions / help.
We are migrating from 2000 to 2012 sql server database.In one of the procedure ,we have seen there was a logic which was purely written on order based.
Logic is : 1. Storing the data into temp table using order by id clause.
SELECT id,col2,col3
INTO #tmp_equations
FROM table1 EQ WITH (NOLOCK)
INNER JOIN table2 ELU WITH (NOLOCK) ON EQ.EQ_Rate = ELU.[ID]
ORDER BY col3,ID
2.A cursor created on this table but unfortunately there is no order by in cursor declaration.
DECLARE frame CURSOR FOR
SELECT [ID]
FROM #tmp_equations
3.based on the cursor data,logic is performing to insert the data into master table with some business logic. The logic is handled in such way that previous id will be source for current row..
Now we have deployed the same SP in 2012.But we see different result set in step 3.
While analysing we observe that whatever the data is stored in step 1 temp table ,the same order is cursor taking for step 3.
But in step1, the stored result is different order in 2000 and 2012 like
in 2000, id's will be stored like 1,2,3,144,145,230....1000(random order)
in 2012 ,id's will be stored like 1000,999,850,849,....10,99(random order)
We have highly restriction that we cannot change the SP in 2000 by adding order by id in cursor which impacts business.So could any one share some suggestions/ideas to handle this in sql 2012.
Thanks in Advance