6

I have a query which joins two tables based on a timestamp value (don't ask).

One of these tables stores the data in a column with a Timestamp data type. The other stores it as a varchar.

Previously, the query converted the timestamp column to a varchar for the join. As I understand it this is not sargable, so I'd like to do the reverse. (The timestamp table is much larger and efficiency is more of an issue).

SELECT * 
FROM TABLE1 
INNER JOIN TABLE2 
    ON UPPER(master.dbo.fn_sqlvarbasetostr(cast(TimestampColumn as binary(8))))
       = VARCHARTIMESTAMPCOLUMN

I've tried:

CONVERT(ROWVERSION, N'0x0000000003306BDD')

No error, but no match on the table (when a row definitely exists).

CONVERT(ROWVERSION, CAST(N'0x0000000003306BDD' as bigint))

This gives an error converting nvarchar to bigint.

How can I convert the varchar value into a timestamp rather than the other way around?

  • 1
    Why do your two tables have the same value stored as two different data types? Can you fix that? – Aaron Bertrand Jan 8 '15 at 19:54
  • If you have to run a function on a column on one side of the join, you won't have a sargable query. Period. – JNK Jan 8 '15 at 19:58
  • @AaronBertrand Unfortunately, the actual situation is more complicated (as it often is). The second table is actually on a DB2 linked server that can't store a rowversion data type (and even if it could I don't think I could get it changed). – Mansfield Jan 8 '15 at 19:59
  • @JNK Indeed, but I'm talking about running a function on a table with 60k rows (right now) vs one with 1000 (the second table) so I think there's still a performance improvement to be had. – Mansfield Jan 8 '15 at 20:00
  • 1
    How many rows stored in DB2 (roughly)? Might make sense to pull them into a local table first, apply a function there, then join on like data types. You could also create a computed column locally so that both forms are represented, then join on the computed column. – Aaron Bertrand Jan 8 '15 at 20:02
12

In SQL Server 2008, converting binary to a character representation became a lot faster and easier:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1)
);

Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to binary(8) (when not nullable, varbinary(8) otherwise).

We can now create an index on the computed column:

-- Create index on the computed column
-- Note PERSISTED is *not* required
CREATE UNIQUE INDEX i 
ON dbo.X (rvc);

Add some sample data:

-- Some rows
INSERT dbo.X
    (pk, c1)
VALUES
    (1, 100),
    (2, 200),
    (3, 300);

And display the results:

-- Show the data
SELECT
    X.pk,
    X.c1,
    X.rv,
    X.rvc
FROM dbo.X AS X;

Output example:

Output rows

The task of joining to the table with rowversions in character format is now trivial.

SQL Server 2005 version

This requires a helper function:

CREATE FUNCTION dbo.Bin8ToHexStr
    (@Hex binary(8))
RETURNS char(18)
WITH SCHEMABINDING
AS
BEGIN
    RETURN 
        '0x' + 
        CONVERT(xml, N'').value('xs:hexBinary(sql:variable("@Hex"))', 'char(16)');
END;

The table definition becomes:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS dbo.Bin8ToHexStr(CONVERT(binary(8), rv))
);

Everything else proceeds as before, including the index.

| improve this answer | |
  • Awesome, thanks. I'll give that a shot tomorrow and report back. I'm on sql server 2014, by the way - I've tagged the question as such. – Mansfield Jan 9 '15 at 0:59
  • Regarding the "when not nullable, varbinary(8) otherwise" is it possible to get this to happen? If I try and create a nullable timestamp column and insert an explicit NULL it still doesn't end up as NULL. The column also ends up in the fixed length data part of the row. CREATE TABLE #T (A TIMESTAMP NULL,B INT,C INT);INSERT INTO #T VALUES (NULL,1,1);SELECT * FROM #T;DROP TABLE #T; – Martin Smith Jan 10 '15 at 12:50
  • Ah Ok. I'm sure you're right but I don't see the translation to 0x either though. It just gets translated to the next rowversion column as far as I see. 0x00000000000007D9, 0x00000000000007DA, 0x00000000000007DB – Martin Smith Jan 10 '15 at 13:37
  • 1
    @MartinSmith Try e.g. DECLARE @rv rowversion = NULL; SELECT @rv; or DECLARE @T AS table (rv rowversion); INSERT @T DEFAULT VALUES; SELECT * FROM @T AS T LEFT JOIN @T AS T2 ON T2.rv != T.rv; – Paul White Jan 10 '15 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.