October 30, 2012 at 7:18 pm
webtekkie (10/30/2012)
Eugene - I didn't even notice your post earlier as I was posting at the same time. It's an excellent solution but sadly fails for the same reason as Dwain's - it returns a decimal.GSquared - full marks for thinking "outside the box" - unfortunately it does have to be an integer datatype as the third party solution requires it to be so.
So, it does look like I'm asking the impossible - it isn't mathematically possible to turn that many characters into an integer datatype which will fit into a bigint.
Rats! And I thought my use of the DECIMAL(34,20) type was pretty inspired too!
Regardless of all that, I felt that it was important to post a true radix (base 37/11) solution that can fit into DECIMAL(20,0), which incidentally happens to display as an integer (i.e., no decimal point). You never said how this 3rd party solution is going to get the data from you. Possibly if it's in a CSV or flat file, you may be able to slide by.
Anyway, here's the setup data where I scramble both encoding keys (shouldn't really have called them encryption keys). Note that the last value in the setup data is the highest value that could be stored if the encoding keys weren't scrambled (that was my test basis).
DECLARE @Values TABLE
(ID INT IDENTITY
,Value1 VARCHAR(7)
,Value2 VARCHAR(10)
,v DECIMAL(22,0))
DECLARE @Alphanumerics CHAR(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
,@Numerics CHAR(10) = '0123456789'
DECLARE @Radix1 BIGINT = LEN(@Alphanumerics) + 1
,@Radix2 BIGINT = LEN(@Numerics) + 1
-- Scramble both of the encoding keys
;WITH Tally (n) AS (
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number < @Radix1)
SELECT @Alphanumerics = (
SELECT SUBSTRING(@Alphanumerics, n, 1)
FROM Tally a
ORDER BY NEWID()
FOR XML PATH(''))
,@Numerics = (
SELECT SUBSTRING(@Numerics, n, 1)
FROM Tally a
WHERE n < @Radix2
ORDER BY NEWID()
FOR XML PATH(''))
SELECT EncodingKeyAlpha=@Alphanumerics, EncodingKeyNum=@Numerics
-- Sample data
INSERT INTO @Values (Value1, Value2)
SELECT 'AXH32CT','22124587'
UNION ALL SELECT '544DX88','21457751'
UNION ALL SELECT 'ZZZZZZZ', '9999999999'
First the encoding (note the streamlined approach):
-- Encode
;WITH Tally (n) AS (
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 10),
Encode AS (
SELECT ID, Value1, Value2, n, v1, v2
FROM @Values
CROSS APPLY Tally a
CROSS APPLY (
SELECT CHARINDEX(SUBSTRING(Value1, n, 1), @Alphanumerics) * POWER(@Radix1, n-1)
,CHARINDEX(SUBSTRING(Value2, n, 1), @Numerics) * POWER(@Radix2, n-1)
)b (v1, v2))
UPDATE a
SET v = b.v
OUTPUT INSERTED.ID, INSERTED.Value1, INSERTED.Value2, INSERTED.v
FROM @Values a
INNER JOIN (
SELECT ID, Value1, Value2
,v=100000000000*CAST(SUM(v1) AS BIGINT) + CAST(SUM(v2) AS BIGINT)
FROM Encode
GROUP BY ID, Value1, Value2) b ON a.ID = b.ID
Now the decoding (also much streamlined):
-- Decode
;WITH Tally (n) AS (
SELECT n=number
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 10),
Decode AS (
SELECT ID, Value1, Value2, v, n
,v1=SUBSTRING(@Alphanumerics, (v1%POWER(@Radix1, n))/POWER(@Radix1, n-1), 1)
,v2=SUBSTRING(@Numerics, (v2%POWER(@Radix2, n))/POWER(@Radix2, n-1), 1)
FROM @Values a
CROSS APPLY (
SELECT v1=CAST(v/100000000000 AS BIGINT), v2=CAST(v%100000000000 AS BIGINT)) b
CROSS APPLY Tally c)
SELECT ID, Value1, Value2, v
,v1=(
SELECT v1 + ''
FROM Decode b
WHERE n < 8 AND a.ID = b.ID
ORDER BY n
FOR XML PATH(''))
,v2=(
SELECT v2 + ''
FROM Decode b
WHERE a.ID = b.ID
ORDER BY n
FOR XML PATH(''))
FROM Decode a
GROUP BY ID, Value1, Value2, v
Probably still not faster than the mapping table, but hey it is a solution for the true math geeks out there.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 30, 2012 at 7:27 pm
Jeff Moden (10/30/2012)
dwain.c (10/30/2012)
Definitely can't wait to see what Jeff comes up with. The man is in a class all by himself.Thanks for the compliment :blush: but seriously not true. I haven't checked your code for performance but it looks like something that I might write. Well done!
That is a major compliment coming from you that I feel totally unworthy of.:blush: However being the gracious sort, I will humbly accept it.
I think you'll agree that my latest effort (which kept me totally distracted all last night as I thought it through), while still unable to overcome the BIGINT hurdle, is much cleaner.
Edit: It could have been even cleaner of course if I'd have realized sooner that Value2 does not need to be encoded to base 11 at all. That would have dropped the number of output digits by 1 (still not enough to fit into a BIGINT of course, even considering negative values).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 31, 2012 at 4:23 am
Looks like it'll be a mapping table after all then.
Dwain, Eugene, Jeff - Thanks so much for your efforts. I'm in awe of your brainy-ness and committment to the SQL community! Even if we did prove that this wasn't possible, I still learnt some pretty cool T-SQL stuff along the way.
Martin
😀
October 31, 2012 at 4:39 am
My solution was returning a float and involved converting to BINARY(8).
I never got time to fix a bug in it (I'm busy writing an upgrade for migrating our old schema to the new schema design which is taking all of my time), where the last digit from the source data was lost during the conversion. At some point, just for the laughs, I'll get around to fixing it and will post.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply