How to specify custom sorting order for primary key in Sql Server

Question

I have the table with column type varchar as primary key; the primary is also clustered index. The value for primary key is prefix with either I or X, and follow with increasing number (I1, I2, I3, ..., X1, X2, X3, ...). The default sorting currently is like I1, I11, I111, I2, I21, I22, ....

Are there any way I can specify the sorting order for my primary key column by numerical order, when key is created and be inserted to tree; not in select query time? So the sorting order is I1, I2, I3, ..., I11, I12, I13

Thanks

Possible duplicate of [Primary key Ascending vs Descending](https://stackoverflow.com/questions/649307/primary-key-ascending-vs-descending) — Tab Alleman, May 02 '18 at 13:27
Well this is kind of what happens when you put two pieces of data into a single tuple. You probably should have two columns and a composite key. And remember that by definition a table is an unordered set. The concept of order happens when you select data from the table and apply an order by clause. — Sean Lange, May 02 '18 at 13:32

Tim Biegeleisen · Answer 1 · 2018-05-02T13:36:33.837

5

You can try ordering using only the numerical portion of the column, cast to an integer:

SELECT *
FROM yourTable
ORDER BY CAST(SUBSTRING(col, 2, LEN(col)-1) AS INT);

This would sort as follows:

I1
X1
I11
X11

If you wanted to also separate the I from X fields, you could add another level to the ORDER BY clause:

ORDER BY
    LEFT(col, 1),
    CAST(SUBSTRING(col, 2, LEN(col)-1) AS INT);

The real problem here is that you are storing text and numbers (as text) in a single column. You would do better to have a numerical ID column, along with a separate text column.

edited May 02 '18 at 13:36

answered May 02 '18 at 13:25

Tim Biegeleisen

502,043
27
286
360

I could be wrong, but I think OP is asking about changing the way the PK is stored, meaning at Key-Creation time. Not about the way the results are returned at query time. If I am wrong, then the question could use some editing. – Tab Alleman May 02 '18 at 13:29
@TabAlleman Interesting take, and you might be right. But here's the thing, there is no internal order in the general sense, and as text, that primary key column can't be sorted numerically, leaving `ORDER BY` as the alternative. – Tim Biegeleisen May 02 '18 at 13:35
Well exactly, if OP is conflating PK with Clustered Index, as I was suspecting, then they could want to order the "PK" this way to reduce fragmentation when trying to SELECT the data in this order. But the more I think about it, the more I think your interpretation is probably the correct one, and the question is just in need of clarification. – Tab Alleman May 02 '18 at 13:38

DxTx · Answer 2 · 2018-05-02T14:00:56.670

If you want to sort prefix with 'I' first and then prefix with 'X', then try this.
Example: I1, I2, I3, ..., X1, X2, X3, ...

Select * from Table
order by Cast(REPLACE(REPLACE(PKColumn, 'I', '1'), 'X', '2') as int)

Sample data

I1, I11, I2, I3, I111, I2, I21, I22, ..., X1, X2, X3

Result Order

I1, I2, I2, I3, I11, I21, I22, I111,  ..., X1, X2, X3

But if you want to ignore prefix and just sort..., then try this.
Example: I1, X1, I2, X2, I3, X3 ...

Select * from ORDERS
order by Cast(RIGHT(VALUE, LEN(VALUE) - 1) as int);

Sample data

I1, I11, I2, I3, I111, I2, I21, I22, ..., X1, X2, X3

Result Order

I1, X1, I2, I2, X2, I3, X3, I11, I21, I22, I111,  ...

If I didn't understand correctly or if the queries are not working, please let me know.

How to specify custom sorting order for primary key in Sql Server

2 Answers2