4

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):

Platforms

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] 

NonclusteredCache

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

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

ClusteredCache

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?

3
  • 1
    I guess it is using the unique index since it is covering both columns (the name and by construct the primary key). In your case it is most likely not important if it scans this index or the clustered table. But, why do you care about that query anyway, isn’t it more interesting to see what happens if a specific primary key is queries (and no order does not play a role here, however using ORDER BY Id will most likely not use the Name index anymore)
    – eckes
    Mar 19 '18 at 15:28
  • Is there any reason to care about which index the system uses? I trust that the run time is equal either way, and that you're not trusting that the data is sorted without explicitly asking for it. Mar 19 '18 at 15:35
  • @eckes I don't care about this specific query, but rather why it behaves like thi in generals. I'll utilize ORDER BY in queries as necessary, but when I was toying around with a few things and saw this, it intrigued me. A solution isn't needed, but more-so a deeper understanding on why SQL Server handles it as such. It's a curious topic that I believe knowing more about can assist more broadly in the future. Mar 19 '18 at 16:08
14

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.

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."

3
  • I saw the tooltip, but I want to understand why. Why does SQL Server sometimes leverage the clustered index on other tables with UQ, and sometimes not? I'll take a read further into your blog post, because this is indeed an assumption on my part that I'm wanting to correct. Thank you. Mar 19 '18 at 14:48
  • 1
    @PicoDeGallo A cheaper index will often be chosen (but unless you force it, that is not guaranteed either - it can change due to stats changes, index changes, etc). You get used to the way a clustered index scan returns data (again, without ORDER BY, this is just an observation), then when a different index is selected a different index is chosen. Basically, just think about it this way: if you don't specify order explicitly, SQL Server just throws its hands in the air and assumes you don't care about order, so it doesn't care either, and returns data the most efficient way possible. Mar 19 '18 at 14:54
  • That makes a lot of sense, thank you for the concise answer and comment. The behavior of SQL Server's query optimizer is an interesting beast, so insight such as this is invaluable for better understanding and assistance down the road. Mar 19 '18 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.