How to effectively sort nvarchar containing numbers and letters?

Question

I'm having some troubles sorting a table effectively (the table contains a big amount of rows so any optimization would make a big difference).

What I currently have that gives the correct result (11) is the code below.

BEGIN TRANSACTION

    CREATE TABLE #tPallets
    (
      PalletNumber bigint,
      Placement nvarchar(4)
    )

    INSERT INTO #tPallets  VALUES(100000, 'B')   
    INSERT INTO #tPallets  VALUES(100001, 'M1')  
    INSERT INTO #tPallets  VALUES(100002, 'M2')  
    INSERT INTO #tPallets  VALUES(100003, 'M3')  
    INSERT INTO #tPallets  VALUES(100004, 'M4')  
    INSERT INTO #tPallets  VALUES(100005, 'M5')  
    INSERT INTO #tPallets  VALUES(100006, 'M6')  
    INSERT INTO #tPallets  VALUES(100007, 'M7')  
    INSERT INTO #tPallets  VALUES(100008, 'M8')  
    INSERT INTO #tPallets  VALUES(100009, 'M9')  
    INSERT INTO #tPallets  VALUES(100010, 'M10')  
    INSERT INTO #tPallets  VALUES(100011, 'M11')  

    SELECT 
        TOP 1 CASE 
            WHEN Placement LIKE 'M%' THEN CONVERT(int, SUBSTRING(Placement,2, len(Placement)-1)) 
            ELSE 0 
        END AS PALLET_PLACEMENT 
    FROM 
        #tPallets 
    ORDER BY 
        1 DESC

ROLLBACK TRANSACTION

So I am looking for a way to make the select faster if that's possible in this specific scenario.

I wouldn't consider this a duplicate in the way of answers. Since no answer in the thread would make the execution time faster without making the sort in C# (as opposed to doing it in TSql). And I have a hard time believing that there is no faster way to do it in TSql itself.

Possible duplicate of [Natural (human alpha-numeric) sort in Microsoft SQL 2005](http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005) — Dai, Apr 25 '17 at 09:34
Is the structure of the values consistent? Always 1 letter and then 0 - 2 digits? Are the letters always upper-case? And does the `Placement` value ever change? — Solomon Rutzky, Apr 25 '17 at 15:24
@SolomonRutzky It's always B or M1 - M150 (at worst). Sorry for the very late answer, didn't see the comment. — Danieboy, Dec 19 '17 at 11:27

KumarHarsh · Answer 1 · 2017-04-25T11:37:16.010

0

with what detail you hv provided,try this way.

CREATE TABLE #tPallets
    (
      PalletNumber bigint,
      Placement nvarchar(4),
      OrderBy as CASE 
            WHEN Placement LIKE 'M%' THEN CONVERT(int, SUBSTRING(Placement,2, len(Placement)-1)) 
            ELSE 0 END
    )

    INSERT INTO #tPallets  VALUES(100000, 'B')   
    INSERT INTO #tPallets  VALUES(100001, 'M1')  
    INSERT INTO #tPallets  VALUES(100002, 'M2')  
    INSERT INTO #tPallets  VALUES(100003, 'M3')  
    INSERT INTO #tPallets  VALUES(100004, 'M4')  
    INSERT INTO #tPallets  VALUES(100005, 'M5')  
    INSERT INTO #tPallets  VALUES(100006, 'M6')  
    INSERT INTO #tPallets  VALUES(100007, 'M7')  
    INSERT INTO #tPallets  VALUES(100008, 'M8')  
    INSERT INTO #tPallets  VALUES(100009, 'M9')  
    INSERT INTO #tPallets  VALUES(100010, 'M10')  
    INSERT INTO #tPallets  VALUES(100011, 'M11')  

    select * from #tPallets
    order by OrderBy

Alternatively try this but not sure.

select * from #tPallets
order by BINARY_CHECKSUM(Placement) desc

edited Apr 25 '17 at 11:37

answered Apr 25 '17 at 09:48

KumarHarsh

5,046
1
18
22

This is exactly as fast and wouldn't apply to the real query. I'm looking for a replacement to the Convert / substring / len that is faster. – Danieboy Apr 25 '17 at 10:37
Even if you make computed column `persisted`? – Arvo Apr 25 '17 at 11:50
It doesn't give the answer that I am looking for. When I add the case in the select instead of * they are exactly as fast. The answer I want in the end is 11 in this case. – Danieboy Apr 25 '17 at 13:43
@Danieboy , i think create clustered index on computed column is the only way .Why you will want to order so much data at one time ? – KumarHarsh Apr 26 '17 at 11:01

score 0 · Answer 2 · answered Apr 25 '17 at 11:33

0

An index on a computed column https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns

or, perhaps better in your situation a combination of that and filtered index

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

to get rid of those pesky 0s. (What are your expectations of order when your placement is not like %M by the way?)

answered Apr 25 '17 at 11:33

LoztInSpace

5,584
1
15
27

At the start there is only a 'B' which should be seen as number 0. So that the query returns 0 in that case. – Danieboy Apr 25 '17 at 11:52
Sure but it's about selectivity. If you have effectively `ORDER BY 0` then you will not be able to determine the `TOP 1` unless you specify some other criteria. You will get *a* row but you won't know which one. – LoztInSpace Apr 25 '17 at 11:56
You pointed me in the right direction with that last comment. I realized that in this scenarion MAX() was way faster than TOP 1 / ORDER BY 1 DESC. – Danieboy Apr 25 '17 at 13:41
And while you may be correct in that this index could possibly make this query faster...I don't think it's desirable in this specific scenarion (outside of a temporary table). Because there are tons of different queries on the same table which need other indexes. And as I understand it you don't want too many indexes on a table. Is this correct? – Danieboy Apr 25 '17 at 13:46
More indexes slow down inserts & updates not selects. The more you have the slower your inserts & updates but if you need them then you need them. Observe, measure, decide. It's always a trade off. – LoztInSpace Apr 25 '17 at 14:50

score 0 · Answer 3 · answered May 25 '21 at 01:25

Use a Computed Column The way to speed it up is to have the column be persisted than you can create an index on your column. Keep in mind this of course will have a performance penalty on DML (insert/updates/deletes)

DROP TABLE IF EXISTS #tPallets
CREATE TABLE #tPallets
(
    PalletNumber bigint,
    Placement nvarchar(4),
    PALLET_PLACEMENT AS (CASE 
        WHEN Placement LIKE 'M%' THEN CONVERT(int, SUBSTRING(Placement,2, len(Placement)-1)) 
        ELSE 0 
    END) PERSISTED /*Will save the calculation in the table*/
)

INSERT INTO #tPallets
VALUES(100000, 'B')   
,(100001, 'M1')  
,(100002, 'M2')  
,(100003, 'M3')  
,(100004, 'M4')  
,(100005, 'M5')  
,(100006, 'M6')  
,(100007, 'M7')  
,(100008, 'M8')  
,(100009, 'M9')  
,(100010, 'M10')  
,(100011, 'M11')  

/*Can create this to speed up the query*/
CREATE INDEX ix_test on #tPallets (PALLET_PLACEMENT) INCLUDE (PalletNumber,Placement)

SELECT *
FROM  #tPallets 
ORDER BY PALLET_PLACEMENT DESC

How to effectively sort nvarchar containing numbers and letters?

3 Answers3