Table with clustered index implicitly sorting by unique nonclustered index

Question

I have a table that captures the host platform that a user is running on. The table's definition is straightforward:

IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL
BEGIN
    CREATE TABLE [Auth].[ActivityPlatform] (
        [ActivityPlatformId] [tinyint] IDENTITY(1,1) NOT NULL
        ,[ActivityPlatformName] [varchar](32) NOT NULL
        ,CONSTRAINT [PK_ActivityPlatform] PRIMARY KEY CLUSTERED ([ActivityPlatformId] ASC)
        ,CONSTRAINT [UQ_ActivityPlatform_ActivityPlatformName] UNIQUE NONCLUSTERED ([ActivityPlatformName] ASC)
    ) ON [Auth];
END;
GO

The data it stores is enumerated based on a JavaScript method that uses information from their browser (I don't know much more than that, but could find out if needed):

When I perform a basic SELECT without an explicit ORDER BY, however, the Execution Plan shows that it is using the UNIQUE NONCLUSTERED index in order to sort instead of the CLUSTERED index.

SELECT * FROM [Auth].[ActivityPlatform] 

When explicitly specifying the ORDER BY, it correctly sorts by ActivityPlatformId.

SELECT * FROM [Auth].[ActivityPlatform] ORDER BY [ActivityPlatformId]

DBCC SHOWCONTIG('[Auth].[ActivityPlatform]') WITH ALL_LEVELS, TABLERESULTS shows no table fragmentation.

What am I missing that could cause for this? I thought so long that the table was created on a clustered index, it should automatically sort by it implicitly without need to specify ORDER BY. What is SQL Server's preference in choosing the UQ? Is there something I need to specify in the table's creation?

Answer 1

No, sorting is not implicit and should not be relied upon. In fact, in the first tooltip, you can see that it is explicitly stated that Ordered = False. This means SQL Server didn't do anything at all to implement any sorting. What you observe is just what it happened to do, not what it tried to do.

If you want to be able to predict a reliable sort order, type out the ORDER BY. Period. What you might observe when you don't add ORDER BY might be interesting, but it cannot be relied upon to behave consistently. In fact in this post, see #3, I show how a query's output can change simply by someone else adding an index.

• T-SQL Tuesday #56 : Assumptions

What is SQL Server's preference in choosing the UQ?

The UNIQUE NONCLUSTERED index contains the clustering key, so it is covering for the query. In this case, your table only has two columns, so the clustered and nonclustered indexes contain the same data (just sorted differently). They're both the same size, so the optimizer could choose either. That it chooses the nonclustered index is an implementation detail.

I call this a "coin flip."