Seemingly Odd Ordering Of Results With/without TOP And/or Index

I've been trying to find out why a simple query containing a couple of top-level order bys produces different results when I introduce TOP into the query. I've found nothing so far, other than the results of both queries (TOP and non-TOP) are different again if I add an index.

All the DDL and DML is below, along with the results. The database uses the BIN2 collator.

I guess there's a simple explanation for this...




DROP table employeeCREATE TABLE employee( id INTEGER IDENTITY (1, 1) NOT NULL, givenname NVARCHAR (20), familyname NVARCHAR (20), CONSTRAINT pk_employee_id PRIMARY KEY (id));insert into employee values('John', 'Smith');insert into employee values('John', 'SMITH');insert into employee values('John', 'Smyth');insert into employee values('John', 'SMYTH');goselect top 10 givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo-- Dropping top produces results in different order!select givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo-- Creating an index results in different order on both queries!CREATE INDEX idx_employee_names ON employee (familyname, givenname);goselect top 10 givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgoselect givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo

This produces the following results:

givenname familyname
-------------------- --------------------
John SMITH
John Smith
John SMYTH
John Smyth

(4 rows affected)
givenname familyname
-------------------- --------------------
John Smith
John SMITH
John Smyth
John SMYTH

(4 rows affected)
givenname familyname
-------------------- --------------------
John Smith
John SMITH
John Smyth
John SMYTH

(4 rows affected)
givenname familyname
-------------------- --------------------
John SMITH
John Smith
John Smyth
John SMYTH