HowTo Compare Binary and int colns

  • Thread starter Thread starter sjoshi
  • Start date Start date
S

sjoshi

I need to compare and update a Binary(8) coln with values from an
Int(4) coln. The table definitions are :

CREATE TABLE [dbo].[COREBaseClass] (
[oid] [uniqueidentifier] NOT NULL ,
[ConditionID] [int] NOT NULL ,
[persistentFlag] [int] NOT NULL ,
[UIDCreator] [uniqueidentifier] NOT NULL ,
[UIDLastModifier] [uniqueidentifier] NOT NULL ,
[DateCreated] [smalldatetime] NOT NULL ,
[DateLastModified] [smalldatetime] NOT NULL ,
[ApprovalStatus] [int] NOT NULL ,
[ApprovalReason] [int] NOT NULL ,
[tsRelations] [int] NOT NULL ,
[ts] [int] NOT NULL
)
CREATE TABLE [dbo].[COREProxy] (
[oid] [uniqueidentifier] NOT NULL ,
[dwProperties] [int] NOT NULL ,
[sourceTimeStamp] [binary] (8) NOT NULL ,
[COREPersistentFlags] [int] NOT NULL ,
[ts] [int] NOT NULL ,
[tsRelations] [int] NOT NULL
)

I need to update COREProxy.sourceTimeStamp with the value of
COREBaseClass.ts wherever they are not the same.
thanks
Sunit
 
What does "not the same" mean to you in this context where even the
data types can never be equal? Have you tried converting them both to
string values to see how that flies?

--Mary
 
Ok I checked with the DBA and I need to compare just the first 4 bytes.
So if I have 0x0300000000000000, then just use the first 4.

Similarly, I would need to create same hexstring from an int to update
the db.

Sunit
 
Is this requirement being driven by the dba? If so, then ask the dba
to provide you with a sample demonstrating what they want in T-SQL
using the CAST/CONVERT function.

This requirement indicates poor table/database design. Whenever you
need to denormalize data for comparisons or joins you are bogging down
the server with unnecessary operations. If the first 4 bytes of the
binary data need to be compared with an int column in another table,
then when the data is entered a stored procedure or trigger should run
that performs the operation of stripping off these bytes and storing
them as an int in a separate column. That way you can perform joins
and look for equality without bogging down the server with extra
processing for every row. You can also index the int columns to speed
up these operations. The current plan is going to doom your
application to sub-par performance and lack of scalability.

--Mary
 
Back
Top