Microsoft KB Archive/45661

From BetaArchive Wiki
Knowledge Base

INF: Sorting Data Out of SQL Server

Article ID: 45661

Article Last Modified on 3/2/2005


  • Microsoft SQL Server 4.21a Standard Edition

This article was previously published under Q45661

The following steps describe a process for sorting records outside of SQL Server:

  1. Use BCP to unload the table. The rows will appear in the order in which they were originally inserted or in the order of the clustered index (if there is one).
  2. Use TRUNCATE TABLE to purge all of the data from the table.
  3. Sort the rows with the utility of your choice and reload them. If no clustered index exists when the table is loaded, the rows will be stored in the order loaded. If a clustered index exists, the rows will be stored in clustered index order. The loading process will be slower with indexes because the index(es) must be updated every time a row is added.

If a clustered index is created after the table is loaded, the entire table will be copied and sorted using the free pages within the database. This will be done regardless of whether or not the rows were sorted externally before they were loaded.

Additional query words: BCP

Keywords: KB45661