Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/#!/StackDBAs/status/553367349039202305
added version tag
Link
Mansfield
Mansfield
  • 1.1k
  • 8
  • 19
  • 33
Formatting, tags
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

I have a query which joins two tables based on a Timestamptimestamp 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 varcharvarchar.

Previously, the query converted the timestamptimestamp column to a varcharvarchar for the join. As I understand it this is not sargable, so I'd like to do the reverse. (The "Timestamp"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 nvarcharnvarchar to bigintbigint.

How can I convert the varcharvarchar value into a timestamptimestamp rather than the other way around?

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?

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?

Source Link
Mansfield
Mansfield
  • 1.1k
  • 8
  • 19
  • 33

How can I make this query sargable?

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?