Order of your data returned is not guaranteed when you do not specify an order by.
Consider this example:
CREATE TABLE dbo.SmallTable([name] varchar(25) PRIMARY KEY NOT NULL,
INSERT INTO dbo.SmallTable ([name],[Number]) VALUES ('compi',15 ), ('jack',5 ), ('malik',20 ), ('nana',10 );
If you were to just select without an order by:
The order this time is the same as your first example:
But this is not guaranteed.
If you want the ordering to be guaranteed then you need to add an order by:
ORDER BY [name] ASC;
What you are seeing when creating the nonclustered index on number is the query using this new NC index.
CREATE INDEX IX_Number
Running the query again:
I get the same result as you:
Because now sql server decides to use the
IX_Number index to return the results:
Which is ordered by
To guarantee the ordering the query needs to be changed to:
ORDER BY [name] asc;
The ordering could even be different than the index order from the index that was used. This means that even if now the ordering is on the
name column, it could be on any one of these columns or even none.
Always use an
order by if the ordering is important.
More info on the subject can be found here, here, here and here