Upgrade issue from 6.5 to 2000

[singh]

Hi,
We have a VB application, which extracts data from SQL Server 6.5 database. Recently we upgraded our database from 6.5 to 2000.
One of the report which used to give results in Sorted order in 6.5 database, is not producing the same report in sorted order. We didn't do any change at database/application level.

As we have not made any changes at application level, I am presuming that the problem is at database level, due to this upgradation.

Can any one guide me how to rectify this problem.

As I don't have source code for this application; Is it possible for me to extract the query running for this report from database?

Thanks.

[MAK]

Yes. Use SQL Profiler to capture the code and add proper index to the table.

[skhanal]

One reason could be the query uses GROUP BY clause.

In SQL 6.5 GROUP BY used ORDER BY for grouping, in SQL 2K it can be done the old way or using a hashing function. If optimizer uses hashing then the result will not be sorted.

[singh]

Hi,
Thanks for your help. How should I check this? I never used profiler and I never did any thing related with tuning.

[rmiao]

Check sql books online for details about profiler.

[singh]

Hi,
Where should I get information about optimizer used by server for my query?

[MAK]

copy and paste the query in query optimizer and press CTRL + 'L'
it will show you the execution plan

[singh]

Hi,

My query is

SELECT DISTINCT CT.CTs, CT.CID
FROM CT INNER JOIN CTI
ON CT.CID = CTI.CID
WHERE CTI.CID = 1

I have non clustered indexes on both the tables on the all the columns in the WHERE clause.

I understand that becuase of Cost based Optimation, default behavior of the SQL Server 2000 is to optimize the query for least cost. So I will not get the sorted result for the above query.

Is there a way to configure SQL Server 2000, so that I can get the sorted output, like in SQL server 6.5 or any configuration option in 2000, which results in default behaviour of 6.5

I tried changing Index from non-clustered to clustered in 2000, but in both cases output is same.

Is there any index, which can help me in getting sorted output without using Order by clause in the query

Thanks.

[skhanal]

Did you try creating a clustered index on CID column.

[singh]

Hi,
I tried to create a clustered index on CID columne, no good.

When I remove DISTINCT from my query, I get same output on 6.5 and 2000.
This is also mentioned in the Inside SQL Server 2000 (Chaptor 15, page 845. Book from MS Press) that sorted output in ORODER BY clause or DISTINCT clause prior to version 7 is a by product. Now implementation in 2000 is different becuase of cost based optimization. It says that if the query optimizer chooses to use hashin to process the GROUP BY clause, the data does not come back in any predictable order. ( I guess the same clause apply to DISTINCT ALSO).

The problem is we cannot modify the application queries (add ORDER BY clause... and the problem is solved). I have to find a way which can be done at database level.

Any clue for this?

[singh]

Hi,

There is an error in the above posting.

in place of ORDER BY, it should be GROUP BY...

[skhanal]

Try creating a clustered index on CTs and CID and see what happens.