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
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
SELECT * FROM [Auth].[ActivityPlatform]
When explicitly specifying the
ORDER BY, it correctly sorts by
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?