Sql-server – What are valid usage scenarios for HEAP tables

You've got two questions in here:

1. Does the nonclustered index have a sorted B-tree? Yes, because you have to get to the right page of the index to find the data you're looking for.

2. Why do you want a nonclustered index when you also have a clustered index? Think of the white pages of the phone book as your clustered index. If you wanted to find all of the people in your city with a first name of "Brent", then the clustered index isn't going to do you much good - the clustered index is based on last name, first name, middle initial. Wouldn't it be helpful to have a separate phone book sorted by first name?

Just like in real life, the answer to that might be yes or no - but it depends on how often you search for people by first name, and how often people move in/out of your city, or how often people change names.

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most.

But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge problem. There are typically other matters that are more important to tune than trying desperately to avoid fragmentation.