How does index affect the result of two tables - one with index and another without index?

Question

I have two tables in two schemas - schema1 and schema2. Both the tables have the same design, except that Schema2 has a clustered index on the primary key of the tables.

Schema1 tables don't have primary key (That's how the old design was and I've to revamp it with a new schema design which is schema2)

In schema 2, COL_1 is the primary key for table1 and (COL_4, COL_12 ) are keys for table2 which are indexed.

Table1 (col_1, col_2, col_3, col_4 ... col_10) Table2(col_1,col_4,col_12, .... col_20)

I have a query which retrieves data from table1, and is as follows

SELECT t1.COL_1,t1.COL_2, t1.COL_3, t1.COL_4,t1.COL_5 
FROM table1 t1
LEFT JOIN table2 t2 ON
     t2.COL_1 = t1.COL_1,
     AND t2.COL_4 = t1.COL_4
WHERE 
     t1.col_10 = '/some string/'

When I run this query in both the schemas, I get the number of rows retrieved same. But the order of the rows are not the same and I don't know how to compare the data in both.

My questions.

1. Can I assume that both the results in the two schemas match, just 'coz the rowcount match ?
2. Do the results differ if since there is index in the tables in schema2 ?

I would like to have an understanding of the above behaviour.

Thanks in advance.

Answer 1

Table 1/Schema 1 is a heap table, you insert a record it's added to the end of that table. When you query that table the records are (but don't count it) returned in the same order they are inserted.

Table 2/Schema 2 is a clustered index table, i.e. when you insert a record into that table it's inserted in between records if needed (or appended if the new record primary key is greater than all other existing ones). When you query that table the records are returned (but don't count in it) in a sorted order of the primary key.

If you wish to compare these two tables and be certain they are exactly the same you can do this (be prepared it will take awhile if it's a huge table).

-- show all records in table1 that do not exist in table2
select * from table1
except
select * from table2

and the other way around

-- show all records in table2 that do not exist in table1
select * from table2
except
select * from table1

if no records are returned from these two queries, the tables are the same. if you have "updated/created" columns or identity column that is allowed to differ, then you list the columns you wish to compare in all queries.