Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003

    Unanswered: SELECT INTO ORDER not working for big table. Anyone experiece it before?

    I wrote a program that sort a table with more than 40,000 records into a new one, it failed. So I tried to simulate this behaviour with Northwind database. I tried on few machines with configuration range from 500MHz (Win98) to 1.8GHz (XP) and 128-256MB RAM.

    Executing 1st time gave table that seems sorted halfway. Execute another round may give correct result. But later when the size of Remarks column (see below) changed to larger size, it failed again.

    Here is the coding:

    -- remove the following two lines if you run it for 1st time
    drop table tmp_customer
    drop table tmp_order

    create table tmp_customer (CustomerID nchar(5), RowNo int, Remarks nchar(250))

    declare @i int
    set @i = 1
    while @i <= 500
    insert into tmp_customer (CustomerID, RowNo) select CustomerID, @i from Customers
    set @i = @i + 1

    select * into tmp_order from tmp_customer order by CustomerID, RowNo

    select * from tmp_order

    Please help, Thanks

  2. #2
    Join Date
    Feb 2002
    Houston, TX
    If you want the contence of the tmp_Orders table sorted by CustomerID and RowNo the add "orderby CustomerID, RowNo" to the end of your select statment, otherwise your results will be sorted bassed on the primary index or if no index exists, then by physical order of the data. BTW the physical order is NOT guaranteed to be the same everyt time due to page splits!
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jun 2003
    You are right. Actually I'm also puzzled on how SQL decides on the physical order.

    Btw, if the table has little records (ex. 100 records). The ORDER statement seems guaranteed to work fine. Strange isn't it?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts