Publishing Tables Issue

Today I encountered a very straight forward SQL Query  a simple Select with columns list, and order by an integer column. I wondered because there was no such big columns in the selection list, and even there were only a few thousands of records.

 When I analyzed the underlying table I cam to know that it was a publishing table (as it belonged to pub schema), which does not has any separate primary key nor it inherit it from some other tables, so in short there were no Clustered Index neither Non Clustered Index. Here is the execution plan details.

I experiment on that same table I created another table with an Auto ID columns to be used as primary key. So I just got approximately 4 to 7 percent performance as follow.  

So...I was not happy with this performance, I dig a bit on the other side, instead of creating another table, move data in it, was too much for that little performance boost. So I tried another thing why not I should create a clustered index on the table for the column it is sorting the data, and I did it and I got only 2 % percent performance gain.

And finally I got some thing that why I should not try Non clustered index on the same column, so I created a Non Clustered index as follow.

CREATE NONCLUSTERED INDEX [NCI_SequencNo_MyTable] ON [pub].[MyTable] 

(

[SequenceNo] ASC

)

INCLUDE ( [ID],

[Name],

[IsInActive],

[IsSuspended]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

And I got a lot of performance....look at yourself...

I tried another thing, why not to remove the clustered index I created before which did give me only 2 % performance, and see if I can get more. Here is the final execution plan details I got.

And the most beutiful thing was in the execution plan I was not seeing the sorting details, seems it was removeing the Order By clause from the query, as it understands the result will be sorted..