>In .NET 1.1, if I insert a bunch of rows into a DataTable and then use the DataTable.Select(string) method to pull out a subset, their order is not preserved.
>
>I can code around this problem, but my impression of ADO.NET was that the DataSet is intended to behave like an in-memory database. MS SQL Server does not change the order when you SELECT rows.
>
>Even if Oracle, MySQL, MS Access, and other databases don't preserve the order of rows from the database, if you sort them, ADO.NET should preserve the order of selected subsets. Preserving order supports more efficient code because unnecessary sorts are avoided.
>
>My DataTables have under 50 rows. For people working with much larger DataTables, if order matters, ADO.NET is really wasting their processing time, especially when they have ordered a result set on the database. They could do multiple data pulls to avoid using DataTable.Select(string), but that is less efficient for the database server and the network and slows things down.
>
>Has this behavior changed in VS.NET 2.0?
>
>Does anyone else consider this a bug?

David,

I do not think that this is a bug. SQL Server does not preserve the sort order even when SELECTING without ORDER BY and/or WHERE clause. This is because SQL Server can dynamically choose to provide the data directly from an index intead of the table if it is more efficient to do so at the time.

Similarly, ADO.NET will utilize its own indexes to more efficiently return the filtered rows. Of course, ADO.NET is not as smart as SQL Server when it comes to analyzing filter criteria and sort criteria in the same .Select(), so it may be less efficient to filter the rows and sort the rows on different columns. However, this is a minor performance hit when dealing with small rowcounts.

Even with slighlty larger rowcounts ( < 1000), sorting and filtering is quite snappy compared to requerying SQL Server.