How to efficiently sort nvarchar containing numbers and letters?
I am having problems sorting the table (the table contains a large number of rows, so any optimization would make a big difference).
What I have now is giving the correct result (11) - this 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'm looking for a way to make the selection faster, if possible in this particular scenario.
I would not consider this duplicate in the answers. As no answer in the thread would force the runtime faster without doing the sort in C # (unlike in TSql). And I can hardly believe that there is no faster way in TSql.
source to share
with the details you provided, try this method.
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
source to share
Index on a computed column https://docs.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns
or maybe better in your situation a combination of this and the filtered index
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes
to get rid of those pesky 0s. (What are your expectations of the order when yours is placement
out of place like %M
?)
source to share