binary_checksum craziness

Folks, hi.
Any idea why this patently different items return the same binary_checksum value:

select
binary_checksum('X', convert(datetime,'2006-13-12'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','S','X') [1],

binary_checksum('X', convert(datetime, '2006-28-11'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','B','X') [2]

The core of a very major system is based on these computing unique values for a unique set of expressions!Because BINARY_CHECKSUM is not guaranteed to be unique. From BOL:

BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.|||The core of a very major system is based on these computing unique values for a unique set of expressions!
Oops. Somebody should have done some research first, eh?|||Oops. Somebody should have done some research first, eh?

At the risk of overstepping my bounds: don't be an anacedent.

Regards,

hmscott|||Point taken...|||A most helpful forum indeed it has to be said.|||Here is how CHECKSUM is calculated. You can see for yourself how Microsoft do and why many checksums are the same.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832|||Thanks Peso.sql

beta headache

Hi Folks,

I'm still using the the SQL Server 2005 June CTP to develop. I'm working for a client that intends to upgrade, but has not done so yet. We believed that we had 365 days from the date of installation before it would expire (late July). But Visual Studio 2005 Beta 2 just expired, which is used by SQL Server Business Intelligence Development Studio, so now we can no longer develop SSIS packages or Reporting Services reports.

What can I do?

I thought that maybe installing Visual Studio 2005 express might work, but when I went there, it seems that it is split up into modules (e.g. Visual Basic 2005 express, Visual C++ express, etc...). I don't know if just one of these would be sufficient and if so, which one. Even if it would, it says I'll need to completely remove the SQL Server 2005 beta, as well as the Visual Studio 2005 beta, and I think the .net framework.

My contract is up at the end of June, and it was understood that the client was going to take care of the upgrade. Is there a quick fix here? What can I do with minimal effort? (I don't really have time to wait for the client to get the upgrade approved and done. I know, sniff, sniff! lol.)

Uninstalling and reinstalling really shouldn't be terribly time consuming. You should definitely get off the betas and onto released software. For SQL, you can go straight to Express at SP1 level (i.e., don't bother installing Express RTM because the remastered version with SP1 bits is now available, and better). If you don't have stuff in master like special logins, a simple uninstall/reinstall will be easiest. If you do feel a need to retain the Master database, use the special /savesysdb switch when you uninstall and then you can carry all that stuff over to the current build.

Sorry, I don't have any answers for you on the Visual Studio Express bits to use. I'm sure they've got a forum up here where you can ask that question.

Best way to compare two ENTIRE rows in seperate tables?

Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.

Here's the basic lowdown:

I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.

I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.

So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.

Right now I explicitly check the value of each column. ie:

WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc

I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.

Any thoughts?in my opinion, a JOIN with a column-by-column comparison is the best way to go

WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3

another approach:select pkey
from (
select * from table1
union
select * from table2
) u
group
by pkey
having count(*) = 2 since UNION removes duplicate rows, this query will give you all the pkeys which have something different

of course, then you gotta dive back into the tables to see the actual data, so ...|||I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.There is.select a.pkey
from (select pkey, binary_checksum(*) as checkvalue from a) checksumsa
inner join (select pkey, binary_checksum(*) as checkvalue from b) as checksumb
on checksumsa.pkey = checksumsb.pkey
where checksumsa.checkvalue <> checksumsb.checkvalueBUT...binary_checksum values are not guaranteed to be different 100% of the time. There is a slight chance that the data could be modified in a way that leaves the checksum value unchanged, though the chances are slim.
Have you considered placing a trigger on your secondary table to set a flag when a record is updated? In the end, that may be your best approach.|||Sounds like Delta processing

http://weblogs.sqlteam.com/brettk/archive/2004/04/23/1281.aspx|||When using binary checksums (which is the way I do this comparison across several databases and tables we have) you also have to be aware that there can be differences reported if one of the servers being used in the comparison is a LINKED server...
http://www.dbforums.com/showthread.php?t=1213716|||Good stuff guys! I'll be leveraging a bit of what Rudy and Brett posted for what I need. I was positive there had to be a more elegant method than what I was doing...

Thanks duders!