CDS order of records slightly different than query

[Preston]

I have a CDS that is ordering the records from the query slightly different than the query order. There is no index on the cds.

Using the profiler I can grab the query that's being executed against the database and run it from the database management tool. The order of that result set is different than what the cds shows. It's not by much though, the records are within a few rows of where they should be.

Is this a known issue?


D2006 Update 2
SQL Server Express 2005

[Craig Stuntz [TeamB]]

Post by Preston
I have a CDS that is ordering the records from the query slightly
different than the query order. There is no index on the cds.

Unless you set TDataSetProvider.Options.poRetainServerOrder true (not
the default), you can't count on these being the same.

[Preston]

Post by Craig Stuntz [TeamB]
Unless you set TDataSetProvider.Options.poRetainServerOrder true (not
the default), you can't count on these being the same.

Any idea why the default is off or why this problem even exists. Is it a performance issue? If so, will turning it on slow things down?

[Craig Stuntz [TeamB]]

Post by Preston
Any idea why the default is off or why this problem even exists.

I don't know why the default is what it is. My guess is that people
expect the CDS index feature to work. I don't think that turning it on
will hurt performance, but as with all performance issues, I wouldn't
suggest attempting to optimize things which have not been shown to be
problems yet.

[Roy]

Post by Craig Stuntz [TeamB]

Post by Preston
Any idea why the default is off or why this problem even exists.

I don't know why the default is what it is. My guess is that people
expect the CDS index feature to work. I don't think that turning it on
will hurt performance, but as with all performance issues, I wouldn't
suggest attempting to optimize things which have not been shown to be
problems yet.

I too have noticed the sorting differences. Dashes are ignored in the CDS index sort. The CDS sort appears to be based upon Windows dictionary sorting (as is MS Access / Jet). MS SQL Server does binary sort by default, but can be configured to do Windows dictionary sort. However, it would be nice if the CDS would provide an option for a binary sort for compatibility with SQL Server (I prefer the binary sort for my application). Any chance of that happening?

[delfo]

Post by Preston
Is this a known issue?

Sure it's known, but it is worse than you believe, because it doesn't
affect only the order but the number of rows returned -or at least shown-:

http://qc.borland.com/wc/qcmain.aspx?d=55581