Hi all:

The following scenario happened (it will be a long one):

1.We have here an ASE 11.9.2 running on NT 4.0. Every day, a program loads
data from external sources into some ASE tables. One of these tables is
called ms_orig_20001031 and contained 1,621,641 records. I will refer this
table as (ASE1192)ms_orig_20001031;

2.Some modifications were made into the above program to make it run
faster. For test purposes, the modified program was run on (another) ASE
12.0 on NT 4.0 and loaded into the same table a number of 1,621,641
records. I will refer this table as (ASE120)ms_orig_20001031

3.So far, so good. The idea was to compare the two tables to see if they
contains really the same info, not only the same number of rows.

3.1 The first step was to bcp out (ASE1192)ms_orig_20001031;

3.2 The second step was to bcp in (ASE120)ms_orig_20001031 using the
file generated in step 3.1
The result was the table (ASE120)ms_orig_20001031 contains 3,243,282 rows,
i.e. twice the original size;

3.3 The third step was to remove duplicates from table
(ASE120)ms_orig_20001031 using a clustered index:
create clustered index x on (ASE120)ms_orig_20001031 (col1, col2) with
ignore_dup_row
The result after the indexing was that the table (ASE120)ms_orig_20001031
contained 1.806.415 rows. Well, maybe the modified program was wrong;

3.4 Now comes the surprise:
select * into test_tab from (ASE120)ms_orig_20001031.
Of course, test_tab contains 1.806.415 rows.
create clustered index x on test_tab (col1, col2) with ignore_dup_row
.. and warning messages about duplicated rows started to pour...
select count(*) from test_tab
.. reveals 1,621,641 records.. the program was good, after all.

3.5 One step further:
drop index (ASE120)ms_orig_20001031.x
go
create clustered index x on (ASE120)ms_orig_20001031 (col1, col2) with
ignore_dup_row
.. nothing removed.

So practically i whave a table, i copied it with select into into another
table and the same created clustered index on both tables produced
different results.

Any help will be greately appreciated

Stefan
stefan.giuros@connex.ro