Sql-server – How to make this query sargable

sql serversql server 2014t-sqltype conversion

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?

Best Answer

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.

Related Question