Sort order of aspnet_Users table

Question

I am wondering if the aspnet_Users table is always sorted (by UserName) even if I add new users which are alphabetically between two already existing users.

I've noticed that the method Membership.GetAllUsers() always seems to return a sorted collection of MembershipUsers.

Also when I look into SQL Server Management Studio and run a simple SQL query without ORDERBY clause...

SELECT [UserName]
  FROM [MyDb].[dbo].[aspnet_Users]

...I get always a sorted result list.

I'm still very unfamiliar with SQL Server but I expected that when I add a new row to a table it is (physically) appended to the end of the table. And when I run a select statement without ORDERBY the rows will be selected in the order they were initially inserted into the database (so without any specific sort order).

I am wrong I guess. But how does it really work? Is it perhaps something special with the aspnet_Users table?

I am happy that the MemberShipUserCollection returned by GetAllUsers() is sorted but is it always guaranteed?

Thanks for info!

Update

I've just noticed that the database contains a stored procedure called aspnet_Membership_GetAllUsers. This procedure actually contains an ORDER BY clause by UserName. So if this stored procedure is indeed always called when I use Membership.GetAllUsers() in my code (at least if I am using the SqlMembershipProvider) it explains why the collection returned by Membership.GetAllUsers() is sorted by UserName.

Remains the question: Is Membership.GetAllUsers() actually based on this stored procedure?

Answer 1

There might be a clustered index on UserName. That means the rows in the table are stored in UserName order. SQL Server will usually (but not always) return rows in the order of the index it uses.

Without order by, there are no guarantees. So if you rely on the results being ordered, don't forget the order by :)

Answer 2

Without explicitly specifying an ORDER BY clause in your SQL, any order is accidental and definitely not guaranteed in any way, shape or form. Don't rely on it.

If you need a specific order, use ORDER BY to define that order.

Answer 3

There is a clustered Index on UserName and ApplicationID. Since you usually only use 1 applicationID then it will appear to be sorted by UserName. Data in tables are ordered by their clustered index. Which means you will usually see unsorted date return in the order of the clustered columns. However, it is NEVER guaranteed. So you must use an ORDER BY if you want the results to come back in a specific order. Any time you insert data into a table with a Clustered Index it will insert it into the table wherever if physically belongs. Look into paging and clustered vs non clustered. It will help you understand how SQL Server stores data. Look at this http://en.wikipedia.org/wiki/Clustered_index#Architecture for an overview.