SQL Server Performance Forum – Threads Archive
Varbinary columnsRosID1 RosID2
The integer values in the RosID1 and RosID2 columns are actually the hex values.
The First Byte of the RosID1 Column represents a threadID and the other three bytes
represents a Seq Number. When I copy these values to the destination tables
I have to change the first byte to a new ThreadID which I will geting as an Input
parameter to the Stored Proc as integer. Also I have to get the max seqNumber from the
destination table’s ROSID1 Column for the ThreadID. So I need to split the destination tables ROSID1 Column to get the max sequence number. what is a best way to do this. Is there a set bases solution to it or we have to use
What is RosID2 for ? Can you also post how should the data in destination tables like ?
RosID2 has some information in that the first byte has represent the order type and the other 3 bytes will have the date. both the sorce and destination tables structure is the same. But when I move the records from source to destination I have to chnage the RosID1’s first byte to have a new threadID and the other three bytes of the RosID1 is the
sequence number. I have to get the max seqnumber from the destination table ( i.e the SeqNumber from the RosID1 of the destination table for the same threadID which is the first byte ) Hope its not too confusing Thx
If you are storing the values as integers and you want to get the maximum sequence number for a particular thread ID, I would create a scalar function like this:<br /><br /><b>CREATE FUNCTION MaxSequenceNumber (@ThreadID int)<br />RETURNS int<br />AS<br />BEGIN<br />DECLARE @SequenceNumber int<br />SET @SequenceNumber=(SELECT REPLACE(convert(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,MAX(ROSID1)),convert(varchar(2),@ThreadID),”) FROM <i>YOURTABLE</i><br />WHERE convert(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,ROSID1) LIKE convert(varchar(2),@ThreadID) + ‘%’)<br />RETURN convert(int,@SequenceNumber)<br />END<br />GO</b><br /><br />and use it wherever necessary.<br /><br /><br /><br />Nathan H. Omukwenyi<br />MVP [SQL Server]<br />