SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server equivalent to DB2 HEX


SQL Server equivalent to DB2 HEX

Author
Message
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 Visits: 2486
Ok, so I am going to go ahead with the straw grabbing. I was messing around with Drew's suggestion to ouput to text, combined with casting various substrings as various data types, and I ended up with this...and I don't even know it it will display here...

------------------------------
ø M
ø M
d<C

ßæC
… M




Indeed, they are not displaying as they do in the output. However, I took one of them at random, this guy, '', and converted to ASCII...



SELECT ASCII('')

-----------
8

(1 row(s) affected)




So, if ASCII 8 = backspace, does anyone think this has any meaning, or is SQL Server just totally confused by the nature of this data? In my results pane, the character actually looks like a vertically aligned rectangle with a slightly ovoid white dot in the center. Could this be the seemingly confounding EBCDIC? At this point, I think I'm going to stick with OPENQUERY, so any further discussion would be academic. That is, of course, unless anyone thinks my errand is anything other than a fool's. Thanks.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 7365
What's the data type of Column1 one in DB2? It looks like a structure containing 6 columns:

Pos 1: CHAR(1) - 'B' = 0xC2 (EBCDIC) = 0x42 (ASCII)
Pos 2-7: NUMERIC(11) Packed decimal - 0x00000012700C (0xC = plus sign)
Pos 8: CHAR(1) - 'M' = 0xD4 (EBCDIC) = 0x4D (ASCII)
Pos 9: NUMERIC(11) Packed decimal - 0x00000003673C (0xC = plus sign)
Pos 15: CHAR(1) - 'N' = 0xD5 (EBCDIC) = 0x4E (ASCII)
Pos 16-21: NUMERIC(11) Packed decimal - 0x00000000850C (0xC = plus sign)

When using the four part naming convention the entire structure is translated from EBCDIC to ASCII byte by byte. For a translation table take a look at http://en.wikipedia.org/wiki/EBCDIC_37. Note that the least significant nible of the packed decimal values contains 0xC meaning positive. Other values may be 0xD (negative) or 0xF (unsigned) (see http://www.simotime.com/datapk01.htm).

One way to get the packed decimal values out this structure is to translate it back to EBCDIC, convert it to a hex-string and then extracting the values the way you already did using OPENQUERY. Here's some demo code. Note that the Ascii-Ebcdic table is not complete in function AsciiToEbcdic. I just put in enough codes to translate your sample data:

DECLARE @V VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650C
SELECT
*
FROM
dbo.AsciiToEbcdic(@V) A2E
CROSS APPLY
dbo.GetPackedDecimal(A2E.Value, 2, 6) PD1
CROSS APPLY
dbo.GetPackedDecimal(A2E.Value, 9, 6) PD2
CROSS APPLY
dbo.GetPackedDecimal(A2E.Value, 16, 6) PD3
OPTION (MAXRECURSION 0)


Output:

0xC200000012700CD400000003673CD500000000850D 12700 3673 850

Functions:

IF OBJECT_ID('dbo.AsciiToEbcdic', 'IF') IS NOT NULL
DROP FUNCTION dbo.AsciiToEbcdic
GO

CREATE FUNCTION dbo.AsciiToEbcdic(@V VARBINARY(8000))
RETURNS TABLE
AS RETURN
WITH rCTE(I, Value) AS
(
SELECT 0 AS I, CAST('' AS VARBINARY(8000)) AS Value
UNION ALL
SELECT
I + 1, Value + A2E.EbcdicCode
FROM
rCTE
JOIN
( --Ascii - Ebcdic table: for all values look at http://en.wikipedia.org/wiki/EBCDIC_37
VALUES
(0x00, 0x00),
(0x03, 0x03),
(0x0C, 0x0C),
(0x0D, 0x0D),
(0x0F, 0x0F),
(0x12, 0x12),
(0x42, 0xC2),
(0x4D, 0xD4),
(0xC5, 0x67),
(0xF8, 0x70),
(0x14, 0x3C),
(0x4E, 0xD5),
(0x65, 0x85)
) A2E (AsciiCode, EbcdicCode) ON SUBSTRING(@V, I + 1, 1) = AsciiCode AND I < DATALENGTH(@V)
)
SELECT
Value
FROM
rCTE
WHERE
I = DATALENGTH(@V)
;
GO

IF OBJECT_ID('dbo.GetPackedDecimal', 'IF') IS NOT NULL
DROP FUNCTION dbo.GetPackedDecimal
GO

CREATE FUNCTION dbo.GetPackedDecimal(@V VARBINARY(100), @Pos INT, @Len INT)
RETURNS TABLE
AS RETURN
WITH Cte AS
(
SELECT
SUBSTRING(CONVERT(VARCHAR(100), @V, 2), 2 * @Pos - 1, 2 * @Len) R
)
SELECT
CASE RIGHT(R, 1)
WHEN 'D' THEN -- '0xC' = Positive, '0xF' = Unsigned
-1
ELSE
1
END * CAST(LEFT(R, DATALENGTH(R) - 1) AS DECIMAL(38)) Value
FROM
Cte
;
GO


Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 Visits: 2486
Peter, thank you so much for taking your good time to work on my problem, I am truly humbled by your knowledge.

Peter Brinkhaus (1/13/2012)
What's the data type of Column1 one in DB2? It looks like a structure containing 6 columns:

Pos 1: CHAR(1) - 'B' = 0xC2 (EBCDIC) = 0x42 (ASCII)
Pos 2-7: NUMERIC(11) Packed decimal - 0x00000012700C (0xC = plus sign)
Pos 8: CHAR(1) - 'M' = 0xD4 (EBCDIC) = 0x4D (ASCII)
Pos 9: NUMERIC(11) Packed decimal - 0x00000003673C (0xC = plus sign)
Pos 15: CHAR(1) - 'N' = 0xD5 (EBCDIC) = 0x4E (ASCII)
Pos 16-21: NUMERIC(11) Packed decimal - 0x00000000850C (0xC = plus sign)


Your break down makes perfect sense now, as I could not get how a single field could actually be an array.

When using the four part naming convention the entire structure is translated from EBCDIC to ASCII byte by byte.


The four part naming convention is causing me some trouble, as I can not get it to work with the IBM provider. If I use the MSDASQL provider, I can use it if I leave level zero only unchecked, and omit the catalog. The problem here is that I cannot see the objects. If I use the IBM provider, I can not get 4 part name to work regardless of the level zero value. There is somewhat of a language barrier between the dba's and me, because they do not know SQL Server, and I don't know DB2, so I don't even get what the catalog is. I scripted out my linked server, and tweeked it a bit for others to easily use, maybe you could Identify what I am doing wrong?


--==== Declare the local variables needed to make the connection. You will need to run this script
-- every time your mainframe password changes.
DECLARE @user VARCHAR(100),
@pwd VARCHAR(100),
@connection VARCHAR(100),
@local VARCHAR(100),
@LinkedServer VARCHAR(20),
@dsn VARCHAR(20)

--==== Set the values of the local variables
SELECT @user = '#######' --<<<< change this to your EID
SELECT @pwd = '#######' --<<<< change this to your mainframe password
SELECT @connection = 'Password=' + @pwd + ';Persist Security Info=True;User ID=' + @user
SELECT @local = '#######\' + @user --<<<< change to your domain
SELECT @LinkedServer = '#######' --<<<< change to your preference
SELECT @dsn = '#######' --<<<< change to your dsn

--==== Drop the linked server if it already exists.
IF EXISTS (SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = @LinkedServer)
EXEC master.dbo.sp_dropserver @server= @LinkedServer, @droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver
@server = @LinkedServer,
@srvproduct = 'DB2',
@provider = 'MSDASQL', -- can use 4 part name is catalog omitted, level zero only not checked
--@provider = 'IBMDADB2', -- 4 part name does not work, don't know what is catalog
@datasrc = @dsn,
@provstr = @connection;

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname ='MIRROR',
@useself ='False',
@locallogin = @local,
@rmtuser = @user,
@rmtpassword = @pwd;



Again, thank you for your help.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 7365
I'm sorry I have to disappoint you, but I don't know DB2 either, so I can't help you on this.
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 Visits: 2486
Not even the least bit disappointed, indeed just the opposite is true, as not being able to see the tables is a minor issue. I built a full table of values, and it worked like a charm. My task now is to figure out how to make it so that I can use it in a select list on multiple records. Right now I am unable to do this. Thanks again for your help Peter.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Greg Snidow
Greg Snidow
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2109 Visits: 2486
Peter, I looked at the link you provided for the lookup table, and decided to create a permanent table to use in the function. I also tweaked it to return only the final value, and to use a tally table instead of a recursive CTE. I'm not sure which is better, but I have never used a recursive CTE, so I wanted to stick with a solution I fully understood. A funny thing I noticed is that my function does not work without an index on the ascii values of the lookup table. Anyhow, I'll go ahead and post the code to create the look up table and the function (which assumes the existence of a tally table starting at 1) in case anyone is interested. Peter, once again, thank you for your so valuable post and code. I learned a great deal in tearing it apart until I fully understood what it is doing. Take care.

First the function...


USE master
GO

IF OBJECT_ID('dbo.fnAsciiToEbcdic','fn') IS NOT NULL
DROP FUNCTION dbo.fnAsciiToEbcdic
GO

CREATE FUNCTION [dbo].[fnAsciiToEbcdic](@v VARBINARY(8000))
RETURNS VARBINARY(8000)
AS BEGIN
DECLARE @return VARBINARY(8000)
SELECT
@return = ISNULL(@return,CAST('' AS VARBINARY(8000))) + a2e.EBCDIC_VAL
FROM master..Tally t INNER JOIN master..ASCII_EBCDIC a2e
ON SUBSTRING(@v,t.N,1) = a2e.ASCII_VAL
WHERE N <= DATALENGTH(@v)

RETURN @return
END




And now the lookup table...


USE master
GO

IF OBJECT_ID('master..ASCII_EBCDIC','u') IS NOT NULL
DROP TABLE master..ASCII_EBCDIC
GO
CREATE TABLE master..ASCII_EBCDIC
(
ID INT IDENTITY(1,1),
ASCII_VAL BINARY,
EBCDIC_VAL BINARY
)

INSERT INTO master..ASCII_EBCDIC
VALUES
(0x00, 0x00),(0x01, 0x01),(0x02, 0x02),(0x03, 0x03),(0x04, 0x37),
(0x05, 0x2D),(0x06, 0x2E),(0x07, 0x2F),(0x08, 0x16),(0x09, 0x05),
(0x0A, 0x25),(0x0B, 0x0B),(0x0C, 0x0C),(0x0D, 0x0D),(0x0E, 0x0E),
(0x0F, 0x0F),(0x10, 0x10),(0x11, 0x11),(0x12, 0x12),(0x13, 0x13),
(0x14, 0x3C),(0x15, 0x3D),(0x16, 0x32),(0x17, 0x26),(0x18, 0x18),
(0x19, 0x19),(0x1A, 0x3F),(0x1B, 0x27),(0x1C, 0x1C),(0x1D, 0x1D),
(0x1E, 0x1E),(0x1F, 0x1F),(0x20, 0x40),(0x21, 0x5A),(0x22, 0x7F),
(0x23, 0x7B),(0x24, 0x5B),(0x25, 0x6C),(0x26, 0x50),(0x27, 0x7D),
(0x28, 0x4D),(0x29, 0x5D),(0x2A, 0x5C),(0x2B, 0x4E),(0x2C, 0x6B),
(0x2D, 0x60),(0x2E, 0x4B),(0x2F, 0x61),(0x30, 0xF0),(0x31, 0xF1),
(0x32, 0xF2),(0x33, 0xF3),(0x34, 0xF4),(0x35, 0xF5),(0x36, 0xF6),
(0x37, 0xF7),(0x38, 0xF8),(0x39, 0xF9),(0x3A, 0x7A),(0x3B, 0x5E),
(0x3C, 0x4C),(0x3D, 0x7E),(0x3E, 0x6E),(0x3F, 0x6F),(0x40, 0x7C),
(0x41, 0xC1),(0x42, 0xC2),(0x43, 0xC3),(0x44, 0xC4),(0x45, 0xC5),
(0x46, 0xC6),(0x47, 0xC7),(0x48, 0xC8),(0x49, 0xC9),(0x4A, 0xD1),
(0x4B, 0xD2),(0x4C, 0xD3),(0x4D, 0xD4),(0x4E, 0xD5),(0x4F, 0xD6),
(0x50, 0xD7),(0x51, 0xD8),(0x52, 0xD9),(0x53, 0xE2),(0x54, 0xE3),
(0x55, 0xE4),(0x56, 0xE5),(0x57, 0xE6),(0x58, 0xE7),(0x59, 0xE8),
(0x5A, 0xE9),(0x5B, 0xBA),(0x5C, 0xE0),(0x5D, 0xBB),(0x5E, 0xB0),
(0x5F, 0x6D),(0x60, 0x79),(0x61, 0x81),(0x62, 0x82),(0x63, 0x83),
(0x64, 0x84),(0x65, 0x85),(0x66, 0x86),(0x67, 0x87),(0x68, 0x88),
(0x69, 0x89),(0x6A, 0x91),(0x6B, 0x92),(0x6C, 0x93),(0x6D, 0x94),
(0x6E, 0x95),(0x6F, 0x96),(0x70, 0x97),(0x71, 0x98),(0x72, 0x99),
(0x73, 0xA2),(0x74, 0xA3),(0x75, 0xA4),(0x76, 0xA5),(0x77, 0xA6),
(0x78, 0xA7),(0x79, 0xA8),(0x7A, 0xA9),(0x7B, 0xC0),(0x7C, 0x4F),
(0x7D, 0xD0),(0x7E, 0xA1),(0x7F, 0x07),(0x80, 0x20),(0x81, 0x21),
(0x82, 0x22),(0x83, 0x23),(0x84, 0x24),(0x85, 0x15),(0x86, 0x06),
(0x87, 0x17),(0x88, 0x28),(0x89, 0x29),(0x8A, 0x2A),(0x8B, 0x2B),
(0x8C, 0x2C),(0x8D, 0x09),(0x8E, 0x0A),(0x8F, 0x1B),(0x90, 0x30),
(0x91, 0x31),(0x92, 0x1A),(0x93, 0x33),(0x94, 0x34),(0x95, 0x35),
(0x96, 0x36),(0x97, 0x08),(0x98, 0x38),(0x99, 0x39),(0x9A, 0x3A),
(0x9B, 0x3B),(0x9C, 0x04),(0x9D, 0x14),(0x9E, 0x3E),(0x9F, 0xFF),
(0xA0, 0x41),(0xA1, 0xAA),(0xA2, 0x4A),(0xA3, 0xB1),(0xA4, 0x9F),
(0xA5, 0xB2),(0xA6, 0x6A),(0xA7, 0xB5),(0xA8, 0xBD),(0xA9, 0xB4),
(0xAA, 0x9A),(0xAB, 0x8A),(0xAC, 0x5F),(0xAD, 0xCA),(0xAE, 0xAF),
(0xAF, 0xBC),(0xB0, 0x90),(0xB1, 0x8F),(0xB2, 0xEA),(0xB3, 0xFA),
(0xB4, 0xBE),(0xB5, 0xA0),(0xB6, 0xB6),(0xB7, 0xB3),(0xB8, 0x9D),
(0xB9, 0xDA),(0xBA, 0x9B),(0xBB, 0x8B),(0xBC, 0xB7),(0xBD, 0xB8),
(0xBE, 0xB9),(0xBF, 0xAB),(0xC0, 0x64),(0xC1, 0x65),(0xC2, 0x62),
(0xC3, 0x66),(0xC4, 0x63),(0xC5, 0x67),(0xC6, 0x9E),(0xC7, 0x68),
(0xC8, 0x74),(0xC9, 0x71),(0xCA, 0x72),(0xCB, 0x73),(0xCC, 0x78),
(0xCD, 0x75),(0xCE, 0x76),(0xCF, 0x77),(0xD0, 0xAC),(0xD1, 0x69),
(0xD2, 0xED),(0xD3, 0xEE),(0xD4, 0xEB),(0xD5, 0xEF),(0xD6, 0xEC),
(0xD7, 0xBF),(0xD8, 0x80),(0xD9, 0xFD),(0xDA, 0xFE),(0xDB, 0xFB),
(0xDC, 0xFC),(0xDD, 0xAD),(0xDE, 0xAE),(0xDF, 0x59),(0xE0, 0x44),
(0xE1, 0x45),(0xE2, 0x42),(0xE3, 0x46),(0xE4, 0x43),(0xE5, 0x47),
(0xE6, 0x9C),(0xE7, 0x48),(0xE8, 0x54),(0xE9, 0x51),(0xEA, 0x52),
(0xEB, 0x53),(0xEC, 0x58),(0xED, 0x55),(0xEE, 0x56),(0xEF, 0x57),
(0xF0, 0x8C),(0xF1, 0x49),(0xF2, 0xCD),(0xF3, 0xCE),(0xF4, 0xCB),
(0xF5, 0xCF),(0xF6, 0xCC),(0xF7, 0xE1),(0xF8, 0x70),(0xF9, 0xDD),
(0xFA, 0xDE),(0xFB, 0xDB),(0xFC, 0xDC),(0xFD, 0x8D),(0xFE, 0x8E),
(0xFF, 0xDF)
GO

IF EXISTS(SELECT * FROM SYSINDEXES WHERE name = 'IX_MASTER_ASCII_EBCDIC_ascii_val')
DROP INDEX ASCII_EBCDIC.IX_MASTER_ASCII_EBCDIC_ascii_val
GO

CREATE UNIQUE CLUSTERED INDEX IX_MASTER_ASCII_EBCDIC_ascii_val
ON ASCII_EBCDIC(ascii_val)




Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15542 Visits: 11354
Peter Brinkhaus (1/13/2012)
What's the data type of Column1 one in DB2? It looks like a structure containing 6 columns...

I have to say Peter, this is one of the best posts I have ever seen on SSC. Above and beyond the call of duty! :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 7365
Paul, thank you very much. I'm really honoured by such a compliment from the master of T-SQL & query optimization. However, I still feel a little bit uncomfortable about the solution I suggested. It would have been nice if someone could have pointed Greg in the right direction to properly configure the linked server, if at all possible. I found out that DB2 knows something like a structered type (comparable to a struct in any C-like language) and I'm afraid the provider does not support that feature very well. Any way, I see the solution I provided as some kind of a last resort. It's slow and it might turn into a real maintenance nightmare.

Thanks again,

Peter
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 7365
Greg Snidow (1/13/2012)
Peter, I looked at the link you provided for the lookup table, and decided to create a permanent table to use in the function.
That's a good thing to do.

I also tweaked it to return only the final value, and to use a tally table instead of a recursive CTE. I'm not sure which is better, but I have never used a recursive CTE, so I wanted to stick with a solution I fully understood.
It's a perfectly valid argument to stick with a solution you fully understand, especially if you pick something up from the internet. However, in this case I would stay with the recursive CTE just because it performs better. Scalar functions do not perform very well. Besides, the SELECT @x = ...@x ... method seems to be unreliable (sorry, don't have details at hand, but maybe Paul is willing to jump in). Here's a quick test:


DECLARE @V VARBINARY(100) = 0x4200000012F80C4D00000003C5144E00000000650C

SET STATISTICS TIME ON

PRINT '-- Scalar function'

select top 10000
dbo.fnAsciiToEbcdic(@V) V
from
sys.columns C1
cross join
sys.columns C2

PRINT '-- Recursive CTE'

SELECT TOP 10000
A2E.*
FROM
sys.columns C1
CROSS JOIN
sys.columns C2
CROSS APPLY
dbo.AsciiToEbcdic(@V) A2E

SET STATISTICS TIME OFF



Result:

-- Scalar function

(20000 row(s) affected)

SQL Server Execution Times:
CPU time = 1466 ms, elapsed time = 1545 ms.

-- Recursive CTE

(20000 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 252 ms.

Anyway, thanks for the very positive feedback.

Peter
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15542 Visits: 11354
Peter Brinkhaus (1/14/2012)
Besides, the SELECT @x = ...@x ... method seems to be unreliable (sorry, don't have details at hand, but maybe Paul is willing to jump in).

It was only supported for backward compatibility, and only where an explicit ORDER BY clause is provided. That said, the compatibility is very limited and many of us have seen trivial examples where this best-effort promise for backward compatibility is broken. I definitely do not recommend it. More information on the ordering guarantee situation as it was back in 2005...http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx

Here's a quick test

The optimizer is smart enough to realise you are translating the same value over and over again. It will save the result from the first execution and just replay that for every subsequent row that has the same input value. This does depend on the function being deterministic, so I can't say whether this actually happened in your case, but it's something to be aware of.

I had a quick look at the scalar function. This in-line version produces correct results for me, and might be even faster than the rCTE version:


ALTER FUNCTION dbo.AsciiToEbcdic
(
@v VARBINARY(8000)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
SELECT
CONVERT(varbinary(8000), q.result, 2) AS result
FROM
(
SELECT
CONVERT(char(2), ae.EBCDIC_VAL, 2)
FROM dbo.Tally AS t
JOIN dbo.ASCII_EBCDIC AS ae ON
SUBSTRING(@v, t.N, 1) = ae.ASCII_VAL
WHERE
t.N <= DATALENGTH(@v)
ORDER BY
t.N
FOR XML
PATH ('')
) AS q (result)




SELECT
ate.result
FROM dbo.AsciiToEbcdic
(0x4200000012F80C4D00000003C5144E00000000650C) AS ate





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search