Hi
i came accross of the problem with checksum function which we use
intensively for large db searches.
According to manul it should always produce different value for different
string value and unique for the same strings.
Here is an example when different strings produce the same checksum value:
select checksum('AJUBELTKAJUBEL')
select checksum('AJUBSBTKAJUBSB')
select checksum('AJUCGBTKAJUCGB')
--
-2013265910Gene,
Although CHECKSUM should always produce the same value for the same string,
it absolutely does not always produce a different value for different
strings. The Books Online do not promise this, but say, "we do not
recommend using CHECKSUM to detect whether values have changed, unless your
application can tolerate occasionally missing a change."
When you think about it, CHECKSUM returns an integer value, with only
4,294,967,296 possible values. However, a 10-character string using the 10
digits and 26 letters of the alphabet has 3,656,158,440,062,976 possible
values. The likelihood of collision is increased, because the algorithm
uses exclusive-ors in computing the has value.
RLF
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:61D1F361-C95A-4F5C-B29A-249EFE0412D8@.microsoft.com...
> Hi
> i came accross of the problem with checksum function which we use
> intensively for large db searches.
> According to manul it should always produce different value for different
> string value and unique for the same strings.
> Here is an example when different strings produce the same checksum value:
> select checksum('AJUBELTKAJUBEL')
> select checksum('AJUBSBTKAJUBSB')
> select checksum('AJUCGBTKAJUCGB')
> --
> -2013265910
>|||Thank you Russell again.
I was under impression that different combination of letters should always
produce different integer.
Regards, Gene.
"Russell Fields" wrote:
> Gene,
> Although CHECKSUM should always produce the same value for the same string,
> it absolutely does not always produce a different value for different
> strings. The Books Online do not promise this, but say, "we do not
> recommend using CHECKSUM to detect whether values have changed, unless your
> application can tolerate occasionally missing a change."
> When you think about it, CHECKSUM returns an integer value, with only
> 4,294,967,296 possible values. However, a 10-character string using the 10
> digits and 26 letters of the alphabet has 3,656,158,440,062,976 possible
> values. The likelihood of collision is increased, because the algorithm
> uses exclusive-ors in computing the has value.
> RLF
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:61D1F361-C95A-4F5C-B29A-249EFE0412D8@.microsoft.com...
> > Hi
> >
> > i came accross of the problem with checksum function which we use
> > intensively for large db searches.
> > According to manul it should always produce different value for different
> > string value and unique for the same strings.
> >
> > Here is an example when different strings produce the same checksum value:
> >
> > select checksum('AJUBELTKAJUBEL')
> > select checksum('AJUBSBTKAJUBSB')
> > select checksum('AJUCGBTKAJUCGB')
> > --
> >
> > -2013265910
> >
>
>|||Gene,
Just getting back to you. If you are saving the checkdigit for a string in
a column that is a good way to speed up some searches. In fact, the
CHECKDIGIT was designed as a hash function for just that reason. However,
to make sure you have a hit, you also have to use the actual value. E.g.
DECLARE @.LongString NVARCHAR (255)
SET @.LongString = 'We do not recommend using CHECKSUM to detect whether
values have changed, unless your
application can tolerate occasionally missing a change.'
CREATE TABLE LongStrings
(StringKey INT,
SearchableText NVARCHAR(255),
TextHash INT)
CREATE INDEX TextHash ON LongStrings (TextHash)
INSERT INTO LongStrings VALUES (1, @.LongString, CHECKSUM (@.LongString))
-- And insert many many more rows in real life.
SELECT * FROM LongStrings
WHERE TextHash = CHECKSUM (@.LongString)
AND SearchableText = @.LongString
You will notice that only the hash has an index. This reduces storage for
the LongStrings table since it does not have the big index on
SearchableText, but it does require some extra I/O to compare the
SearchableText for rows where TextHash is equal to the queried value.
RLF
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:D1BFCA68-84CA-4DFB-8F20-DD70E3C2791D@.microsoft.com...
> Thank you Russell again.
> I was under impression that different combination of letters should always
> produce different integer.
> Regards, Gene.
> "Russell Fields" wrote:
>> Gene,
>> Although CHECKSUM should always produce the same value for the same
>> string,
>> it absolutely does not always produce a different value for different
>> strings. The Books Online do not promise this, but say, "we do not
>> recommend using CHECKSUM to detect whether values have changed, unless
>> your
>> application can tolerate occasionally missing a change."
>> When you think about it, CHECKSUM returns an integer value, with only
>> 4,294,967,296 possible values. However, a 10-character string using the
>> 10
>> digits and 26 letters of the alphabet has 3,656,158,440,062,976 possible
>> values. The likelihood of collision is increased, because the algorithm
>> uses exclusive-ors in computing the has value.
>> RLF
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:61D1F361-C95A-4F5C-B29A-249EFE0412D8@.microsoft.com...
>> > Hi
>> >
>> > i came accross of the problem with checksum function which we use
>> > intensively for large db searches.
>> > According to manul it should always produce different value for
>> > different
>> > string value and unique for the same strings.
>> >
>> > Here is an example when different strings produce the same checksum
>> > value:
>> >
>> > select checksum('AJUBELTKAJUBEL')
>> > select checksum('AJUBSBTKAJUBSB')
>> > select checksum('AJUCGBTKAJUCGB')
>> > --
>> >
>> > -2013265910
>> >
>>|||Grrr, I see that I used CHECKDIGIT in the first paragraph. That should be,
of course, CHECKSUM. - RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23J0Q6OYEIHA.3980@.TK2MSFTNGP03.phx.gbl...
> Gene,
> Just getting back to you. If you are saving the checkdigit for a string
> in a column that is a good way to speed up some searches. In fact, the
> CHECKDIGIT was designed as a hash function for just that reason. However,
> to make sure you have a hit, you also have to use the actual value. E.g.
> DECLARE @.LongString NVARCHAR (255)
> SET @.LongString = 'We do not recommend using CHECKSUM to detect whether
> values have changed, unless your
> application can tolerate occasionally missing a change.'
> CREATE TABLE LongStrings
> (StringKey INT,
> SearchableText NVARCHAR(255),
> TextHash INT)
> CREATE INDEX TextHash ON LongStrings (TextHash)
> INSERT INTO LongStrings VALUES (1, @.LongString, CHECKSUM (@.LongString))
> -- And insert many many more rows in real life.
> SELECT * FROM LongStrings
> WHERE TextHash = CHECKSUM (@.LongString)
> AND SearchableText = @.LongString
> You will notice that only the hash has an index. This reduces storage for
> the LongStrings table since it does not have the big index on
> SearchableText, but it does require some extra I/O to compare the
> SearchableText for rows where TextHash is equal to the queried value.
>
> RLF
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:D1BFCA68-84CA-4DFB-8F20-DD70E3C2791D@.microsoft.com...
>> Thank you Russell again.
>> I was under impression that different combination of letters should
>> always
>> produce different integer.
>> Regards, Gene.
>> "Russell Fields" wrote:
>> Gene,
>> Although CHECKSUM should always produce the same value for the same
>> string,
>> it absolutely does not always produce a different value for different
>> strings. The Books Online do not promise this, but say, "we do not
>> recommend using CHECKSUM to detect whether values have changed, unless
>> your
>> application can tolerate occasionally missing a change."
>> When you think about it, CHECKSUM returns an integer value, with only
>> 4,294,967,296 possible values. However, a 10-character string using the
>> 10
>> digits and 26 letters of the alphabet has 3,656,158,440,062,976 possible
>> values. The likelihood of collision is increased, because the algorithm
>> uses exclusive-ors in computing the has value.
>> RLF
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:61D1F361-C95A-4F5C-B29A-249EFE0412D8@.microsoft.com...
>> > Hi
>> >
>> > i came accross of the problem with checksum function which we use
>> > intensively for large db searches.
>> > According to manul it should always produce different value for
>> > different
>> > string value and unique for the same strings.
>> >
>> > Here is an example when different strings produce the same checksum
>> > value:
>> >
>> > select checksum('AJUBELTKAJUBEL')
>> > select checksum('AJUBSBTKAJUBSB')
>> > select checksum('AJUCGBTKAJUCGB')
>> > --
>> >
>> > -2013265910
>> >
>>
>
Showing posts with label checksum. Show all posts
Showing posts with label checksum. Show all posts
Thursday, March 8, 2012
checksums and data types
I'm creating a checksum column in a table that is to be calculated over several columns within the table.
One of the columns to be included in the checksum formula has a data type ntext.
But on trying to complete this new table design (or similarly using alter table in QA) - both return an error stating that the data type is invalid for the checksum function.
This happens for both ntext and text data types.
Can anyone tell me if there is a way round this without having to change the data type - or the valid data types that can be used for the checksum funciton?
Also reasons why would be helpful!
Thanksplease ignore - http://www.dbforums.com/t989557.html shows this not to be possible...
nevermind|||you might want to try something like so:
SELECT checksum(col1,col2,CAST(CAST(col3 as varchar(1)) as int))
FROM testTable
I am not sure if this totally works. Text and Ntext are meant to hold large amounts of text data like notes field in a customer service application. There is no implicit data conversion between int and ntext\text in sql server because that is just one of the rules and it would'nt make much since do so. To tell the truth it sounds like your problem is a design issue. However you can explicitly convert data types as shown above but please keep in mind if you try to cast character data in col3 above to an int, you will recieve an error. So you might have to add an IsNumeric in there as well.|||CHECKSUM works with non-numeric data, so there is no need to recast as INT in your formula.
Though I'm still not sure that is going to give him what he needs...|||Why are you doing this? To enforce data integrity upon INSERT/UPDATE? Or to support some business rule? Either way you're already using a database, so the answer should be in design, not checksum-based tricks.|||hey trotsky!!
calm down.|||I'd love to hear the reason behind this... I can't for the life of me figure out why you might want/need to do it. I'm also with rdjabarov, and think that this smells very strongly of a high GQ (geek quotient) workaround for a case of poor relational design!
-PatP|||that's twice that you have agreed with RDjabarov.
hmmmmmmm is the feud over?
:D|||Feud? Did I miss a meeting?
-PatP|||must have been the coma. when i first go here you guys would go at it like turtles and bunnies.
One of the columns to be included in the checksum formula has a data type ntext.
But on trying to complete this new table design (or similarly using alter table in QA) - both return an error stating that the data type is invalid for the checksum function.
This happens for both ntext and text data types.
Can anyone tell me if there is a way round this without having to change the data type - or the valid data types that can be used for the checksum funciton?
Also reasons why would be helpful!
Thanksplease ignore - http://www.dbforums.com/t989557.html shows this not to be possible...
nevermind|||you might want to try something like so:
SELECT checksum(col1,col2,CAST(CAST(col3 as varchar(1)) as int))
FROM testTable
I am not sure if this totally works. Text and Ntext are meant to hold large amounts of text data like notes field in a customer service application. There is no implicit data conversion between int and ntext\text in sql server because that is just one of the rules and it would'nt make much since do so. To tell the truth it sounds like your problem is a design issue. However you can explicitly convert data types as shown above but please keep in mind if you try to cast character data in col3 above to an int, you will recieve an error. So you might have to add an IsNumeric in there as well.|||CHECKSUM works with non-numeric data, so there is no need to recast as INT in your formula.
Though I'm still not sure that is going to give him what he needs...|||Why are you doing this? To enforce data integrity upon INSERT/UPDATE? Or to support some business rule? Either way you're already using a database, so the answer should be in design, not checksum-based tricks.|||hey trotsky!!
calm down.|||I'd love to hear the reason behind this... I can't for the life of me figure out why you might want/need to do it. I'm also with rdjabarov, and think that this smells very strongly of a high GQ (geek quotient) workaround for a case of poor relational design!
-PatP|||that's twice that you have agreed with RDjabarov.
hmmmmmmm is the feud over?
:D|||Feud? Did I miss a meeting?
-PatP|||must have been the coma. when i first go here you guys would go at it like turtles and bunnies.
Wednesday, March 7, 2012
checksum_agg on field list not working
Help this was working. Moved on to another DB.
Whenever I use checksum_agg on the result of Binary checksum (with a
field list) always returns zero.
When I try with * - all columns it works.
Cannot use all columns - too slow
select distinct binary_checksum('sp1,sp2,sp3,sp4,sp5,sp6
') from myTable
===> 1949676592 (result)
select checksum_agg(binary_checksum('sp1,sp2,sp
3,sp4,sp5,sp6')) from
myTable
===> 0 (result)
select checksum_agg(1949676592)
===> 1949676592 (result)
select checksum_agg(binary_checksum(*)) from myTable
===> -2019434987 (result)
select checksum_agg(binary_checksum(-2019434987))
===> -2019434987 (result)
? have no ideaSorted Field list should not be in quotes
Whenever I use checksum_agg on the result of Binary checksum (with a
field list) always returns zero.
When I try with * - all columns it works.
Cannot use all columns - too slow
select distinct binary_checksum('sp1,sp2,sp3,sp4,sp5,sp6
') from myTable
===> 1949676592 (result)
select checksum_agg(binary_checksum('sp1,sp2,sp
3,sp4,sp5,sp6')) from
myTable
===> 0 (result)
select checksum_agg(1949676592)
===> 1949676592 (result)
select checksum_agg(binary_checksum(*)) from myTable
===> -2019434987 (result)
select checksum_agg(binary_checksum(-2019434987))
===> -2019434987 (result)
? have no ideaSorted Field list should not be in quotes
CHECKSUM_AGG and BINARY_CHECKSUM performance problems
Gentlemen,
I am using the following query to get a list of grouped checksum data.
SELECT CAST(Field0_datetime AS INT),
CHECKSUM_AGG(BINARY_CHECKSUM(Field1_bigint, Field2_datetime,
Field3_datetime, Field4_bigint, Field5_bigint, CAST(Field6_float
Decimal(38,6)), Field7_datetime))
FROM Table1
WHERE Field0_datetime BETWEEN '2003-01-01' AND '2003-01-20'
GROUP BY CAST(Field0_datetime AS INT)
Please notice the used filter: from January 1 to January 20.
That query takes about 6 minutes do return the data. The result is 18
records.
However, when I execute the same query filtering BETWEEN '2003-01-01' and
'2003-01-10', this time it takes only 1 second to return data.
When I execute the query filtering BETWEEN '2003-01-10' and '2003-01-20' the
query takes another 1 second to return data.
So why 6 minutes to process them together??
The table have an index by Field0_datetime.
It contains about 1.5 millions records total, using around 1.7Gb of
diskspace, indexes included.
From 2003-01-01 and 2003-01-20, there are 11401 records selected. Don't look
like that much.
The situation is repeatable, I mean, if I execute the queries back and
again, they takes the about the same ammount of time to execute, so I don't
think this problem is related to cache or something like that.
I would appreciate any advice about what might be wrong with my situation.
Thanks a lot and kind regards,
Orly Junior
IT ProfessionalBy using the profiler, I found that while executing the first query (20 days
span), the system don't use the index. How it possible?
A simpler version of the query that causes the same problem is:
select checksum_agg(binary_checksum([dc])) from [table1] where [dc] between
'2003-01-01' and '2003-01-20'
The profiler reports it will be using a clustered index scan wich is
unacceptable since the table have a lot of records.
Why the hell it is not using the [dc] index ?? If a tight the criteria to
between a 10-day span it uses the index correctly.
Do you have any idea why is that happening?
Thanks in advance and best regards,
Orly Junior
IT Professional
"Orly Junior" <nomail@.nomail.com> wrote in message
news:42b0c9e6$0$32014$a729d347@.news.telepac.pt...
> Gentlemen,
> I am using the following query to get a list of grouped checksum data.
> SELECT CAST(Field0_datetime AS INT),
> CHECKSUM_AGG(BINARY_CHECKSUM(Field1_bigint, Field2_datetime,
> Field3_datetime, Field4_bigint, Field5_bigint, CAST(Field6_float
> Decimal(38,6)), Field7_datetime))
> FROM Table1
> WHERE Field0_datetime BETWEEN '2003-01-01' AND '2003-01-20'
> GROUP BY CAST(Field0_datetime AS INT)
> Please notice the used filter: from January 1 to January 20.
> That query takes about 6 minutes do return the data. The result is 18
> records.
> However, when I execute the same query filtering BETWEEN '2003-01-01' and
> '2003-01-10', this time it takes only 1 second to return data.
> When I execute the query filtering BETWEEN '2003-01-10' and '2003-01-20'
> the query takes another 1 second to return data.
> So why 6 minutes to process them together??
> The table have an index by Field0_datetime.
> It contains about 1.5 millions records total, using around 1.7Gb of
> diskspace, indexes included.
> From 2003-01-01 and 2003-01-20, there are 11401 records selected. Don't
> look like that much.
> The situation is repeatable, I mean, if I execute the queries back and
> again, they takes the about the same ammount of time to execute, so I
> don't think this problem is related to cache or something like that.
> I would appreciate any advice about what might be wrong with my situation.
> Thanks a lot and kind regards,
> Orly Junior
> IT Professional|||Orly Junior (nomail@.nomail.com) writes:
> By using the profiler, I found that while executing the first query (20
> days span), the system don't use the index. How it possible?
When you have a non-clustered index that can be used to compute a query,
SQL Server cannot always use this index blindly. If the selection is
small, the index is find. If the selection is large, the index spells
disaster. This is because every hit in the pages, requires an access to
the data pages. This can up with more pages reads, than use scanning the
table once.
Now, in your case, there are 11041 rows that matches the WHERE clause.
The table is 1.7 GB, which is 207 000 pages. Even if some of those
1.7 GB are indexes, the table scan is obviously more expensive.
But SQL Server does not build query plans from full knowledge, but from
statistics it has saved about the table. If this statistics is inaccurate
for some reason, the estimate may be incorrect. By default, SQL Server
does only sample data for its statistics.
You can try "UPDATE STATISTICS tbl WITH FULLSCAN" and see if this
has any effect. SQL Server will now look at all rows. However, it
saves data in a histogramme, so you may still lose accuracy. DBCC
SHOW_STATISTICS may give some information.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
checksum() of char, varchar, nchar, nvarchar, or sql_variant
Karam,
checksum and binary_checksum can be used for char, varchar, nchar, and
nvarchar. If you need it for sql_variant, you could convert the
sql_variant value to varbinary before applying the checksum or
binary_checksum. The following works fine for me (SQL Server 2000 sp3).
declare @.t table (
a char(10),
b nchar(10),
c varchar(10),
d nvarchar(10),
e sql_variant
)
insert into @.t values ('abc','def','ghi','jkl',cast(3.2 as sql_variant))
insert into @.t values ('abc','def','ghi','jkl',cast('mno' as sql_variant))
insert into @.t values ('abc','def','ghi','jkl',cast(PI() as sql_variant))
select
checksum(a),
checksum(b),
checksum(c),
checksum(d),
checksum(cast(e as varbinary(8000)))
from @.t
Steve Kass
Drew University
Karam Chand wrote:
>Hello,
>As the books online suggest we cannot generate checksum() value of the above types
using Checksum() or binary_checksum(), but my app requires it. Can somebody tell me
how to do that? Or is it just not possible... Maybe I can use some external program
min
g language?
>Karam
>Karam,
If you include a, b, c, d, and e in the output, it should look fine:
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 3.2 135216
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 mno 27535
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 3.1415926535897931 200148684
You should be aware of the fact that CHECKSUM is not guaranteed to
return different values from different input. There are only
~4000000000 possible checksum values, but far more possible input values.
SK
Karam Chand wrote:
>Hello,
>I tried your query on SQL Server 2000 and checksum is always returning me t
he same value for different set of data. If you execute the checksum() for y
our above data, I am getting result:
>304227412 1174430821 25065 6974316 135216
>304227412 1174430821 25065 6974316 27535
>304227412 1174430821 25065 6974316 200148684
>As you can see, its all same so it is difficult to know the difference?
>Karam
> -- Steve Kass wrote: --
> Karam,
> checksum and binary_checksum can be used for char, varchar, nchar, a
nd
> nvarchar. If you need it for sql_variant, you could convert the
> sql_variant value to varbinary before applying the checksum or
> binary_checksum. The following works fine for me (SQL Server 2000 sp3
).
> declare @.t table (
> a char(10),
> b nchar(10),
> c varchar(10),
> d nvarchar(10),
> e sql_variant
> )
> insert into @.t values ('abc','def','ghi','jkl',cast(3.2 as sql_variant
))
> insert into @.t values ('abc','def','ghi','jkl',cast('mno' as sql_varia
nt))
> insert into @.t values ('abc','def','ghi','jkl',cast(PI() as sql_varian
t))
> select
> checksum(a),
> checksum(b),
> checksum(c),
> checksum(d),
> checksum(cast(e as varbinary(8000)))
> from @.t
> Steve Kass
> Drew University
> Karam Chand wrote:
>
gramming language?
>
checksum and binary_checksum can be used for char, varchar, nchar, and
nvarchar. If you need it for sql_variant, you could convert the
sql_variant value to varbinary before applying the checksum or
binary_checksum. The following works fine for me (SQL Server 2000 sp3).
declare @.t table (
a char(10),
b nchar(10),
c varchar(10),
d nvarchar(10),
e sql_variant
)
insert into @.t values ('abc','def','ghi','jkl',cast(3.2 as sql_variant))
insert into @.t values ('abc','def','ghi','jkl',cast('mno' as sql_variant))
insert into @.t values ('abc','def','ghi','jkl',cast(PI() as sql_variant))
select
checksum(a),
checksum(b),
checksum(c),
checksum(d),
checksum(cast(e as varbinary(8000)))
from @.t
Steve Kass
Drew University
Karam Chand wrote:
>Hello,
>As the books online suggest we cannot generate checksum() value of the above types
using Checksum() or binary_checksum(), but my app requires it. Can somebody tell me
how to do that? Or is it just not possible... Maybe I can use some external program
min
g language?
>Karam
>Karam,
If you include a, b, c, d, and e in the output, it should look fine:
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 3.2 135216
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 mno 27535
abc 34400 def 1132889051 ghi 40390
jkl -2087894091 3.1415926535897931 200148684
You should be aware of the fact that CHECKSUM is not guaranteed to
return different values from different input. There are only
~4000000000 possible checksum values, but far more possible input values.
SK
Karam Chand wrote:
>Hello,
>I tried your query on SQL Server 2000 and checksum is always returning me t
he same value for different set of data. If you execute the checksum() for y
our above data, I am getting result:
>304227412 1174430821 25065 6974316 135216
>304227412 1174430821 25065 6974316 27535
>304227412 1174430821 25065 6974316 200148684
>As you can see, its all same so it is difficult to know the difference?
>Karam
> -- Steve Kass wrote: --
> Karam,
> checksum and binary_checksum can be used for char, varchar, nchar, a
nd
> nvarchar. If you need it for sql_variant, you could convert the
> sql_variant value to varbinary before applying the checksum or
> binary_checksum. The following works fine for me (SQL Server 2000 sp3
).
> declare @.t table (
> a char(10),
> b nchar(10),
> c varchar(10),
> d nvarchar(10),
> e sql_variant
> )
> insert into @.t values ('abc','def','ghi','jkl',cast(3.2 as sql_variant
))
> insert into @.t values ('abc','def','ghi','jkl',cast('mno' as sql_varia
nt))
> insert into @.t values ('abc','def','ghi','jkl',cast(PI() as sql_varian
t))
> select
> checksum(a),
> checksum(b),
> checksum(c),
> checksum(d),
> checksum(cast(e as varbinary(8000)))
> from @.t
> Steve Kass
> Drew University
> Karam Chand wrote:
>
gramming language?
>
CHECKSUM() of binary data
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
KaramCan anybody help me?|||CHECKSUM() by parts.
split ntext to blocks of nvarchar(4000) and use check sum.
Originally posted by karam_chand03
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam|||Thanks for the answer.
Can I use it for datatypes like image, sql_variant?
A simple code on how to break it up and get a hash value will be helpful.|||Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||you can generate checksum() to whole column
like
select checksum(*) from tablename
provided none of the columns are of text ntext image data types
Originally posted by karam_chand03
Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||Hello,
Thats the problem. I do have those unsupported columns and I need to generate hash value from it. In MySQL (where I come from) has a md5() function to generate hash value dfor every type of data it supports.
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam|||I don't think so.
convert text to varchar and ntext to nvarchar....
and check.
Originally posted by karam_chand03
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
KaramCan anybody help me?|||CHECKSUM() by parts.
split ntext to blocks of nvarchar(4000) and use check sum.
Originally posted by karam_chand03
Hello,
I need to generate HASH of text values for my app. I can generate hash values for normal fields using CHEKCSUM and BINARY_CHECKSUM function but it does not support checksum of text, ntext, image, and cursor, as well as sql_variant.
How can I generate checksums of such datatype.
Karam|||Thanks for the answer.
Can I use it for datatypes like image, sql_variant?
A simple code on how to break it up and get a hash value will be helpful.|||Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||you can generate checksum() to whole column
like
select checksum(*) from tablename
provided none of the columns are of text ntext image data types
Originally posted by karam_chand03
Hello,
I am still unable to figure out but how can I generate checksum of the whole column in one SQL query? Is it possible?
Karam|||Hello,
Thats the problem. I do have those unsupported columns and I need to generate hash value from it. In MySQL (where I come from) has a md5() function to generate hash value dfor every type of data it supports.
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam|||I don't think so.
convert text to varchar and ntext to nvarchar....
and check.
Originally posted by karam_chand03
What I can think of is that I can convert every such data to varbinary and then employ checksum on it. But that is failing as if I convert some char values to varbinary and then running checksum on it, it is returning the same data.
Is it feasible?
Karam
Checksum Transformation
An updated version of the Checksum Transformation has finally made it out into the big wide world.
Checksum Transformation
(http://www.sqlis.com/default.aspx?21)Any details on how "unique" a checksum value is?
The inbuilt SQL Server CHECKSUM() function seems to have a pretty horrible collision rate due to being only a INT. It's not very useful in determining unique rows amongst anything more than a small handful of rows.|||I have no real numbers on how unique it is, only my testing, which seemed rather good. It uses the .Net Hash function (C#), and the internals of that are not documented.
Feel free to try it.
Using an Int makes it easier to combine multiple columns, where as most other methods would produce a very large value, or just not be capable of handling multiple values.
Darren
Checksum problem on system database
Hey there,
we're having an issue with the SQL (MSDE) database that sharepoint uses.
The service starts and immediately stops. In the logs (in dutch) there's
an error: "Het checksumbestand van de systeemdatabase heeft een
ongeldige ondertekening." freely translated it says "The checksumfile
from the system database has a invalid signature".
Anyone know how to solve this or where start searching? Does SQL have
something like eseutil I might try to repair the database?
TIA
hi,
Freaky wrote:
> Hey there,
> we're having an issue with the SQL (MSDE) database that sharepoint
> uses. The service starts and immediately stops. In the logs (in
> dutch) there's an error: "Het checksumbestand van de systeemdatabase
> heeft een ongeldige ondertekening." freely translated it says "The
> checksumfile from the system database has a invalid signature".
> Anyone know how to solve this or where start searching? Does SQL have
> something like eseutil I might try to repair the database?
if you have a valid master database backup, you can restore it as indicated
in http://msdn2.microsoft.com/en-us/library/Aa173557(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/Aa176749(SQL.80).aspx ... if you
don't, you are in trouble, as MSDE does not provide the Rebuild utility
(rebuildm.exe) and the scripts to re-generate it.. if this is the case, you
have to uninstall and reinstall the MSDE instance.. you can or course keep
your user's databases files an re-attach them once re-installed..
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||Hey Andrea,
thx for the re. I was already afraid I would have to reinstall. It's the
instance from sharepoint. We do maintenance at a lot of customers and
some have full SQL versions so perhaps I could use the rebuildm tool
there. But reinstalling sharepoint and replacing the database STS and
STS_Config might be faster as I think those are the only ones that matter.
Anyways thanks again
Andrea Montanari wrote:
> hi,
> Freaky wrote:
> if you have a valid master database backup, you can restore it as indicated
> in http://msdn2.microsoft.com/en-us/library/Aa173557(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/Aa176749(SQL.80).aspx ... if you
> don't, you are in trouble, as MSDE does not provide the Rebuild utility
> (rebuildm.exe) and the scripts to re-generate it.. if this is the case, you
> have to uninstall and reinstall the MSDE instance.. you can or course keep
> your user's databases files an re-attach them once re-installed..
> regards
we're having an issue with the SQL (MSDE) database that sharepoint uses.
The service starts and immediately stops. In the logs (in dutch) there's
an error: "Het checksumbestand van de systeemdatabase heeft een
ongeldige ondertekening." freely translated it says "The checksumfile
from the system database has a invalid signature".
Anyone know how to solve this or where start searching? Does SQL have
something like eseutil I might try to repair the database?
TIA
hi,
Freaky wrote:
> Hey there,
> we're having an issue with the SQL (MSDE) database that sharepoint
> uses. The service starts and immediately stops. In the logs (in
> dutch) there's an error: "Het checksumbestand van de systeemdatabase
> heeft een ongeldige ondertekening." freely translated it says "The
> checksumfile from the system database has a invalid signature".
> Anyone know how to solve this or where start searching? Does SQL have
> something like eseutil I might try to repair the database?
if you have a valid master database backup, you can restore it as indicated
in http://msdn2.microsoft.com/en-us/library/Aa173557(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/Aa176749(SQL.80).aspx ... if you
don't, you are in trouble, as MSDE does not provide the Rebuild utility
(rebuildm.exe) and the scripts to re-generate it.. if this is the case, you
have to uninstall and reinstall the MSDE instance.. you can or course keep
your user's databases files an re-attach them once re-installed..
regards
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||Hey Andrea,
thx for the re. I was already afraid I would have to reinstall. It's the
instance from sharepoint. We do maintenance at a lot of customers and
some have full SQL versions so perhaps I could use the rebuildm tool
there. But reinstalling sharepoint and replacing the database STS and
STS_Config might be faster as I think those are the only ones that matter.
Anyways thanks again
Andrea Montanari wrote:
> hi,
> Freaky wrote:
> if you have a valid master database backup, you can restore it as indicated
> in http://msdn2.microsoft.com/en-us/library/Aa173557(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/Aa176749(SQL.80).aspx ... if you
> don't, you are in trouble, as MSDE does not provide the Rebuild utility
> (rebuildm.exe) and the scripts to re-generate it.. if this is the case, you
> have to uninstall and reinstall the MSDE instance.. you can or course keep
> your user's databases files an re-attach them once re-installed..
> regards
CheckSum in SQL2000
I want to use checksum function for ntext data field in a table. But this
function accepts only varchar. Is there a way i can find checksum for ntext
fields in SQL 2000.
Thanks!!!I think you can cast it to a varchar
ie checksum(cast(field1 as varchar(8000))|||If you absolutely need to use CHECKSUM on the ntext field and, depending on
the size of the ntext values you already have in the specific table, you
might be able to convert the ntext to nchar/nvarchar or char/varchar and
apply checksum afterwards:
create table ENTEXT
(ID int, txt ntext)
insert into ENTEXT
values (1, 'any value')
select CHECKSUM(convert(char(8000),txt))
, CHECKSUM(convert(nchar(4000),txt))
from ENTEXT
drop table ENTEXT
The only limit would be the number of characters you can house temporarily
(4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
"skg" wrote:
> I want to use checksum function for ntext data field in a table. But this
> function accepts only varchar. Is there a way i can find checksum for ntex
t
> fields in SQL 2000.
> Thanks!!!
>
>|||Thanks All!!!
Mostly my text fields are more than 8k. I am just curious will the checksum
work if i do a substring and
compute checksum of individual chunks of 4k and finally call
checksum_agg(checksum(value)) to
get the aggregate checksum for the data field.
TIA
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:86760E51-9059-4609-B55F-6000E5113C1D@.microsoft.com...
> If you absolutely need to use CHECKSUM on the ntext field and, depending
> on
> the size of the ntext values you already have in the specific table, you
> might be able to convert the ntext to nchar/nvarchar or char/varchar and
> apply checksum afterwards:
> create table ENTEXT
> (ID int, txt ntext)
> insert into ENTEXT
> values (1, 'any value')
> select CHECKSUM(convert(char(8000),txt))
> , CHECKSUM(convert(nchar(4000),txt))
> from ENTEXT
> drop table ENTEXT
> The only limit would be the number of characters you can house temporarily
> (4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
>
>
> "skg" wrote:
>|||Would checksuming only the first 8000 characters suffice? For what purpose
are you wanting to use the checksum?
"skg" <skg@.yahoo.com> wrote in message
news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I want to use checksum function for ntext data field in a table. But this
>function accepts only varchar. Is there a way i can find checksum for ntext
>fields in SQL 2000.
> Thanks!!!
>|||JT thanks!!!
We have pdf documents which are saved in db in text field. we want to remove
duplicates.
thx
"JT" <someone@.microsoft.com> wrote in message
news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
> Would checksuming only the first 8000 characters suffice? For what purpose
> are you wanting to use the checksum?
> "skg" <skg@.yahoo.com> wrote in message
> news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||This is the problem with storing unstructured data (or at least data with an
unfamiliar structure) in a relational database, so identifying rows
containing duplicate 'values' is not simple.
Think about how PDF documents are structured internally. I'm not familiar
with the PDF format specifically, but I'm guessing it begins with a header
containing meta data fields about the documents's size, the file name the
originaly saved as, the date/time created and last written to, and probably
even it's own internal checksum values. Therefore, even if the documents are
several hundred KBs or even MBs in size, only the first 8000 or 4000 bytes
or so may uniquely identify them. You don't need to actually parse this
information, just understand it's a stream of unique bytes and create a
checksum value off of it. This would apply not just to PDFs but also to MS
Word, JPGs, ZIPs or most any structured file type. You will need to read up
on the specifications involved and experiment to confirm this theory would
consistently work. I'm interested in the results, becuase I could possibly
use this technique myself, so reply back to the group when you discover
something.
"skg" <skg@.yahoo.com> wrote in message
news:OKPXYxvIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> JT thanks!!!
> We have pdf documents which are saved in db in text field. we want to
> remove duplicates.
> thx
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
>
function accepts only varchar. Is there a way i can find checksum for ntext
fields in SQL 2000.
Thanks!!!I think you can cast it to a varchar
ie checksum(cast(field1 as varchar(8000))|||If you absolutely need to use CHECKSUM on the ntext field and, depending on
the size of the ntext values you already have in the specific table, you
might be able to convert the ntext to nchar/nvarchar or char/varchar and
apply checksum afterwards:
create table ENTEXT
(ID int, txt ntext)
insert into ENTEXT
values (1, 'any value')
select CHECKSUM(convert(char(8000),txt))
, CHECKSUM(convert(nchar(4000),txt))
from ENTEXT
drop table ENTEXT
The only limit would be the number of characters you can house temporarily
(4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
"skg" wrote:
> I want to use checksum function for ntext data field in a table. But this
> function accepts only varchar. Is there a way i can find checksum for ntex
t
> fields in SQL 2000.
> Thanks!!!
>
>|||Thanks All!!!
Mostly my text fields are more than 8k. I am just curious will the checksum
work if i do a substring and
compute checksum of individual chunks of 4k and finally call
checksum_agg(checksum(value)) to
get the aggregate checksum for the data field.
TIA
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:86760E51-9059-4609-B55F-6000E5113C1D@.microsoft.com...
> If you absolutely need to use CHECKSUM on the ntext field and, depending
> on
> the size of the ntext values you already have in the specific table, you
> might be able to convert the ntext to nchar/nvarchar or char/varchar and
> apply checksum afterwards:
> create table ENTEXT
> (ID int, txt ntext)
> insert into ENTEXT
> values (1, 'any value')
> select CHECKSUM(convert(char(8000),txt))
> , CHECKSUM(convert(nchar(4000),txt))
> from ENTEXT
> drop table ENTEXT
> The only limit would be the number of characters you can house temporarily
> (4000 for nchar/nvarchar or 8000 for char/varchar) during the conversion
>
>
> "skg" wrote:
>|||Would checksuming only the first 8000 characters suffice? For what purpose
are you wanting to use the checksum?
"skg" <skg@.yahoo.com> wrote in message
news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I want to use checksum function for ntext data field in a table. But this
>function accepts only varchar. Is there a way i can find checksum for ntext
>fields in SQL 2000.
> Thanks!!!
>|||JT thanks!!!
We have pdf documents which are saved in db in text field. we want to remove
duplicates.
thx
"JT" <someone@.microsoft.com> wrote in message
news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
> Would checksuming only the first 8000 characters suffice? For what purpose
> are you wanting to use the checksum?
> "skg" <skg@.yahoo.com> wrote in message
> news:uNJQatfIGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||This is the problem with storing unstructured data (or at least data with an
unfamiliar structure) in a relational database, so identifying rows
containing duplicate 'values' is not simple.
Think about how PDF documents are structured internally. I'm not familiar
with the PDF format specifically, but I'm guessing it begins with a header
containing meta data fields about the documents's size, the file name the
originaly saved as, the date/time created and last written to, and probably
even it's own internal checksum values. Therefore, even if the documents are
several hundred KBs or even MBs in size, only the first 8000 or 4000 bytes
or so may uniquely identify them. You don't need to actually parse this
information, just understand it's a stream of unique bytes and create a
checksum value off of it. This would apply not just to PDFs but also to MS
Word, JPGs, ZIPs or most any structured file type. You will need to read up
on the specifications involved and experiment to confirm this theory would
consistently work. I'm interested in the results, becuase I could possibly
use this technique myself, so reply back to the group when you discover
something.
"skg" <skg@.yahoo.com> wrote in message
news:OKPXYxvIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> JT thanks!!!
> We have pdf documents which are saved in db in text field. we want to
> remove duplicates.
> thx
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Gw84IpIGHA.208@.tk2msftngp13.phx.gbl...
>
CheckSum function
Why when I use different uniqueidentifier id will generate the same checksum
value? 911433607
SELECT checksum(CAST(('{A933B626-9F52-4D62-8B59-A7B1E1F243D0}') AS
uniqueidentifier))
SELECT checksum(CAST(('{844A9A36-3B5D-4359-8A62-A61A8836714F}') AS
uniqueidentifier))CHECKSUM won't produce a unique value for every conceivable input. How
could it, given that there are only a few billion possible checksums?
David Portas
SQL Server MVP
--|||To add to David's response:
http://www.cut-the-knot.org/do_you_know/pigeon.shtml
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Simon Lim" <Simon Lim@.discussions.microsoft.com> wrote in message
news:AAB87EED-C006-4F09-B302-6512F4A47E79@.microsoft.com...
> Why when I use different uniqueidentifier id will generate the same
checksum
> value? 911433607
> SELECT checksum(CAST(('{A933B626-9F52-4D62-8B59-A7B1E1F243D0}') AS
> uniqueidentifier))
>
> SELECT checksum(CAST(('{844A9A36-3B5D-4359-8A62-A61A8836714F}') AS
> uniqueidentifier))|||True also. thanks.
"Adam Machanic" wrote:
> To add to David's response:
> http://www.cut-the-knot.org/do_you_know/pigeon.shtml
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Simon Lim" <Simon Lim@.discussions.microsoft.com> wrote in message
> news:AAB87EED-C006-4F09-B302-6512F4A47E79@.microsoft.com...
> checksum
>
>
value? 911433607
SELECT checksum(CAST(('{A933B626-9F52-4D62-8B59-A7B1E1F243D0}') AS
uniqueidentifier))
SELECT checksum(CAST(('{844A9A36-3B5D-4359-8A62-A61A8836714F}') AS
uniqueidentifier))CHECKSUM won't produce a unique value for every conceivable input. How
could it, given that there are only a few billion possible checksums?
David Portas
SQL Server MVP
--|||To add to David's response:
http://www.cut-the-knot.org/do_you_know/pigeon.shtml
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Simon Lim" <Simon Lim@.discussions.microsoft.com> wrote in message
news:AAB87EED-C006-4F09-B302-6512F4A47E79@.microsoft.com...
> Why when I use different uniqueidentifier id will generate the same
checksum
> value? 911433607
> SELECT checksum(CAST(('{A933B626-9F52-4D62-8B59-A7B1E1F243D0}') AS
> uniqueidentifier))
>
> SELECT checksum(CAST(('{844A9A36-3B5D-4359-8A62-A61A8836714F}') AS
> uniqueidentifier))|||True also. thanks.
"Adam Machanic" wrote:
> To add to David's response:
> http://www.cut-the-knot.org/do_you_know/pigeon.shtml
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Simon Lim" <Simon Lim@.discussions.microsoft.com> wrote in message
> news:AAB87EED-C006-4F09-B302-6512F4A47E79@.microsoft.com...
> checksum
>
>
Checksum computation help
Please execute the script below to understand the problem -
--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?
I know you're trying to accomplish something...but you got me lost..
It's in the same manner as your previous threads...
Can you give us a "big picture" view of what you're trying to accomplish?
I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -
ID...County...Univ...Dept.....Status
1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad
I have an index on the Status field coz I can see all Bad records on top.
Today I have in my source system -
ID...County...Univ...Dept
1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training
I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.
So how do I go about this problem? Is there a need for another column in here.
--
create table test(id int, col1 int,col2 varchar(5),col3 datetime)
create table test2(id int, col1 int,col2 varchar(5),col3 datetime)
--id & col1 make up the PK.
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'e','02/06/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'e','02/06/2004')
select *
from test
select *
from test2
--The rows are identical.
--Script A
select t.*
from test t
join test2 t2 on t2.id=t.id
where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)
--The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
--SCRIPT B
select t.*
from test t
join test2 t2 on t2.col2=t.col2
where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)
-- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.
TRUNCATE TABLE TEST
TRUNCATE TABLE TEST2
insert test values(4,4,'d','02/06/2004')
insert test values(4,4,'d','02/01/2004')
insert test2 values(4,4,'d','02/06/2004')
insert test2 values(4,4,'d','02/01/2004')
--Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.
Thanks
drop table test
drop table test2
go
--Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.|||And did you look up CHECKSUM() in BOL?
I know you're trying to accomplish something...but you got me lost..
It's in the same manner as your previous threads...
Can you give us a "big picture" view of what you're trying to accomplish?
I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...|||I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -
ID...County...Univ...Dept.....Status
1...A......XYZ...Accounting...Processed - Good
1...A......ABC...Accounting...Processed - Bad
1...A......XYZ...Marketing...Processed - Good
1...B......PQR...HR............Processed - Good
1...C......XXX...HR............Processed - Bad
I have an index on the Status field coz I can see all Bad records on top.
Today I have in my source system -
ID...County...Univ...Dept
1...A......ABC...Accounting
1...A......XYZ...Accounting
1...A......XYZ...Marketing
1...B......PQR...HR
1...C......XXX...HR
2...C......YYY...Training
I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.
So how do I go about this problem? Is there a need for another column in here.
CHECKSUM , produces same hash for two different inputs. is this right?
Hi,
We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.
We are using following type of inside the trigger.
UPDATE [dbo].[Hospital]
SET
[HospitalID]= I.[HospitalID],
[Name]= I.[Name],
[HospitalNumber]= I.[HospitalNumber],
[ServerName] = I.[ServerName],
[IsAuthorized]= I.[IsAuthorized],
[IsAlertEnabled]= I.[IsAlertEnabled],
[AlertStartDate]= I.[AlertStartDate],
[AlertEndDate]= I.[AlertEndDate],
[IsTraining]= I.[IsTraining],
[TestMessageInterval]= I.[TestMessageInterval],
[DelayAlertTime]= I.[DelayAlertTime],
[IsDelayMessageAlert]= I.[IsDelayMessageAlert],
[IsTestMessageAlert]= I.[IsTestMessageAlert],
[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],
[IsWANDownAlert]= I.[IsWANDownAlert],
[IsWANUpAlert]= I.[IsWANUpAlert],
[CreateUserID]= Hospital.[CreateUserID],
[CreateWorkstationID]= Hospital.[CreateWorkstationID],
[CreateDate]= Hospital.[CreateDate] ,
/* record created date is never updated */
[ChangeUserID]= suser_name(),
[ChangeWorkstationID]= host_name(),
[ChangeDate]= getdate() ,
/* Updating the record modified field to now */
[CTSServerID]= I.[CTSServerID]
FROM inserted i
WHERE
i.[HospitalID]= Hospital.[HospitalID]
AND binary_checksum(
Hospital.[HospitalID],
Hospital.[Name],
Hospital.[HospitalNumber],
Hospital.[ServerName],
Hospital.[IsAuthorized],
Hospital.[IsAlertEnabled],
Hospital.[AlertStartDate],
Hospital.[AlertEndDate],
Hospital.[IsTraining],
Hospital.[TestMessageInterval],
Hospital.[DelayAlertTime],
Hospital.[IsDelayMessageAlert],
Hospital.[IsTestMessageAlert],
Hospital.[IsUnAuthorizedMessageAlert],
Hospital.[IsWANDownAlert],
Hospital.[IsWANUpAlert]) !=
binary_checksum(
I.[HospitalID],
I.[Name],
I.[HospitalNumber],
I.[ServerName],
I.[IsAuthorized],
I.[IsAlertEnabled],
I.[AlertStartDate],
I.[AlertEndDate],
I.[IsTraining],
I.[TestMessageInterval],
I.[DelayAlertTime],
I.[IsDelayMessageAlert],
I.[IsTestMessageAlert],
I.[IsUnAuthorizedMessageAlert],
I.[IsWANDownAlert],
I.[IsWANUpAlert]) ;
Here is the checksum example which produces same results for two different input.
DECLARE @.V1 VARCHAR(10)
DECLARE @.V2 VARCHAR(10)
SELECT @.V1 = NULL, @.V2=NULL
SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@.V1,@.V2,0,30,180,1,0,1,1,1),
binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@.V1,@.V2,0,30,180,1,1,1,1,1)
Lookat the two binary_checksum above, they are different and should not match, but they both return same value.
Can someone please provide some info on these.
Did any one looked at this? I guess this is a very very critical. The checksum is used by storage engine to verify the page integrity and the checksum is stored in every page. if it is producing the same hash for two different inputs it may not verify page correctly.
|||CHECKSUMS ARE NOT UNIQUE. You cannot use checksums in the way you are trying to use them.Check out BOL under BINARY_CHECKSUM
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.
and CHECKSUMIf one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.
Yes, you are correct, there is a small insignificant chance the checksum may be the same on a page even though the data has changed. In large binary data, like SQL server's 64k pages, this chance is in the range of 100 million to 1.
|||
Tom,
In these case the checksum is consistently produces same hash for two different inputs. So every time when I was updating the two columns specified above, it failed. So I thought there is a bug in the code which is not detecting the changes in the input.
Anyway, your suggestion to use HashBytes is very helpful.
Thanks,
CHECKSUM , produces same hash for two different inputs. is this right?
Hi,
We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.
We are using following type of inside the trigger.
UPDATE [dbo].[Hospital]
SET
[HospitalID]= I.[HospitalID],
[Name]= I.[Name],
[HospitalNumber]= I.[HospitalNumber],
[ServerName] = I.[ServerName],
[IsAuthorized]= I.[IsAuthorized],
[IsAlertEnabled]= I.[IsAlertEnabled],
[AlertStartDate]= I.[AlertStartDate],
[AlertEndDate]= I.[AlertEndDate],
[IsTraining]= I.[IsTraining],
[TestMessageInterval]= I.[TestMessageInterval],
[DelayAlertTime]= I.[DelayAlertTime],
[IsDelayMessageAlert]= I.[IsDelayMessageAlert],
[IsTestMessageAlert]= I.[IsTestMessageAlert],
[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],
[IsWANDownAlert]= I.[IsWANDownAlert],
[IsWANUpAlert]= I.[IsWANUpAlert],
[CreateUserID]= Hospital.[CreateUserID],
[CreateWorkstationID]= Hospital.[CreateWorkstationID],
[CreateDate]= Hospital.[CreateDate] ,
/* record created date is never updated */
[ChangeUserID]= suser_name(),
[ChangeWorkstationID]= host_name(),
[ChangeDate]= getdate() ,
/* Updating the record modified field to now */
[CTSServerID]= I.[CTSServerID]
FROM inserted i
WHERE
i.[HospitalID]= Hospital.[HospitalID]
AND binary_checksum(
Hospital.[HospitalID],
Hospital.[Name],
Hospital.[HospitalNumber],
Hospital.[ServerName],
Hospital.[IsAuthorized],
Hospital.[IsAlertEnabled],
Hospital.[AlertStartDate],
Hospital.[AlertEndDate],
Hospital.[IsTraining],
Hospital.[TestMessageInterval],
Hospital.[DelayAlertTime],
Hospital.[IsDelayMessageAlert],
Hospital.[IsTestMessageAlert],
Hospital.[IsUnAuthorizedMessageAlert],
Hospital.[IsWANDownAlert],
Hospital.[IsWANUpAlert]) !=
binary_checksum(
I.[HospitalID],
I.[Name],
I.[HospitalNumber],
I.[ServerName],
I.[IsAuthorized],
I.[IsAlertEnabled],
I.[AlertStartDate],
I.[AlertEndDate],
I.[IsTraining],
I.[TestMessageInterval],
I.[DelayAlertTime],
I.[IsDelayMessageAlert],
I.[IsTestMessageAlert],
I.[IsUnAuthorizedMessageAlert],
I.[IsWANDownAlert],
I.[IsWANUpAlert]) ;
Here is the checksum example which produces same results for two different input.
DECLARE @.V1 VARCHAR(10)
DECLARE @.V2 VARCHAR(10)
SELECT @.V1 = NULL, @.V2=NULL
SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@.V1,@.V2,0,30,180,1,0,1,1,1),
binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@.V1,@.V2,0,30,180,1,1,1,1,1)
Lookat the two binary_checksum above, they are different and should not match, but they both return same value.
Can someone please provide some info on these.
Did any one looked at this? I guess this is a very very critical. The checksum is used by storage engine to verify the page integrity and the checksum is stored in every page. if it is producing the same hash for two different inputs it may not verify page correctly.
|||CHECKSUMS ARE NOT UNIQUE. You cannot use checksums in the way you are trying to use them.Check out BOL under BINARY_CHECKSUM
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.
and CHECKSUMIf one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.
Yes, you are correct, there is a small insignificant chance the checksum may be the same on a page even though the data has changed. In large binary data, like SQL server's 64k pages, this chance is in the range of 100 million to 1.
|||
Tom,
In these case the checksum is consistently produces same hash for two different inputs. So every time when I was updating the two columns specified above, it failed. So I thought there is a bug in the code which is not detecting the changes in the input.
Anyway, your suggestion to use HashBytes is very helpful.
Thanks,
CHECKSUM & CHECKSUM_AGG in T-SQL
Hi,
I recently researched on the CHECKSUM & CHECKSUM_AGG functions in T-Sql and found them really useful. However, I was skeptical that there are chances of these functions returning the same values for non-identical inputs. I just got on to the forums and found more than one unhappy folks writing about their experience with these functions.
I am designing a large database (warehouse) and found these functions tempting to implement for the sake of
using CHECKSUM for
- indexing long character fields
- multiple colums of the same table that would involve in a join and use the new checksum field instead
using CHECKSUM_AGG for
- I bulkcopy flat file soruce data into a character field of a table and to ensure that I am not loading the same file multiple times, I plan to use CHECKSUM_AGG( CHECKSUM( [FlatFileRecord] ) ) and verify that no two loads have the same output.
Can some body suggest if I can trust these methods for my purpose?
Many thanks in advance!!
Thanks,
Harish
You can trust CHECKSUM to be very selective, but most likely you will get collisions from time to time. I would not use only CHECKSUM to "verify that no two loads have the same output", I would add a comprehensive check for rows with the same CHECKSUM.|||Thanks Kuz for your response.. I found CHECKSUM_AGG very efficient in terms of performance. For a 6 million row table, the result of my expression CHECKSUM_AGG( CHECKSUM( [FlatFileRecord] ) ) executed in just 40 seconds. I intend to store the result in a log and then when I load the next file, the same expression will be evaluated on the new data and compared with the previous values in the log. However, its not yet clear to me if I can use the CHECKSUM for my purposes I listed earlier.
Thanks
|||Well.. I found one of the comment few months back from Microsoft SQL Team.. here i gave as it is..
"Please don't use CHECKSUM or BINARY_CHECKSUM functions. They are not guaranteed to produce unique values for input. They are simple hash functions used to divide set of values into different ranges (for example to create compact indexes or partition the data). In fact, with the current implementation you can get duplicate checksum values quite easily and there are certain types of input values that will simply produce unexpected results (repeated values, NULLs etc). You could use hashbytes in SQL Server 2005 which can generate MD5 or MD4 hash for example which can avoid collisions but still no guarantee to produce unique value for each input."
Now you have decide which one you have to use...|||
Thanks Sekar... Here is my summary. The CHECKSUM & CHECKSUM_AGG are deterministic(same output always for the same input) but cannot guarantee unique output for each input. Please correct if I am wrong.
|||Yes.. You got it perfectly.. 
checksum
It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but there
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...[vbcol=seagreen]
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but there
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...[vbcol=seagreen]
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
checksum
It seems that the checksum function does not distinguish between positive
and negative decimals or floats. Is this by design? For example
select checksum(1.0)
select checksum(-1.0)
returns:
-1374215283
-1374215283Elmer Miller wrote:
> It seems that the checksum function does not distinguish between positive
> and negative decimals or floats. Is this by design? For example
> select checksum(1.0)
> select checksum(-1.0)
> returns:
> -1374215283
> -1374215283
That's right. CHECKSUM doesn't necessarily return distinct results for
different inputs.
SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
ABC ASH
-- --
1132495864 1132495864
(1 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but there
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> > It seems that the checksum function does not distinguish between positive
> > and negative decimals or floats. Is this by design? For example
> > select checksum(1.0)
> >
> > select checksum(-1.0)
> >
> > returns:
> >
> > -1374215283
> >
> > -1374215283
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
>> Elmer Miller wrote:
>> > It seems that the checksum function does not distinguish between
>> > positive
>> > and negative decimals or floats. Is this by design? For example
>> > select checksum(1.0)
>> >
>> > select checksum(-1.0)
>> >
>> > returns:
>> >
>> > -1374215283
>> >
>> > -1374215283
>> That's right. CHECKSUM doesn't necessarily return distinct results for
>> different inputs.
>> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
>> ABC ASH
>> -- --
>> 1132495864 1132495864
>> (1 row(s) affected)
>>
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
and negative decimals or floats. Is this by design? For example
select checksum(1.0)
select checksum(-1.0)
returns:
-1374215283
-1374215283Elmer Miller wrote:
> It seems that the checksum function does not distinguish between positive
> and negative decimals or floats. Is this by design? For example
> select checksum(1.0)
> select checksum(-1.0)
> returns:
> -1374215283
> -1374215283
That's right. CHECKSUM doesn't necessarily return distinct results for
different inputs.
SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
ABC ASH
-- --
1132495864 1132495864
(1 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but there
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> > It seems that the checksum function does not distinguish between positive
> > and negative decimals or floats. Is this by design? For example
> > select checksum(1.0)
> >
> > select checksum(-1.0)
> >
> > returns:
> >
> > -1374215283
> >
> > -1374215283
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
>> Elmer Miller wrote:
>> > It seems that the checksum function does not distinguish between
>> > positive
>> > and negative decimals or floats. Is this by design? For example
>> > select checksum(1.0)
>> >
>> > select checksum(-1.0)
>> >
>> > returns:
>> >
>> > -1374215283
>> >
>> > -1374215283
>> That's right. CHECKSUM doesn't necessarily return distinct results for
>> different inputs.
>> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
>> ABC ASH
>> -- --
>> 1132495864 1132495864
>> (1 row(s) affected)
>>
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>
checksum
It seems that the checksum function does not distinguish between positive
and negative decimals or floats. Is this by design? For example
select checksum(1.0)
select checksum(-1.0)
returns:
-1374215283
-1374215283Elmer Miller wrote:
> It seems that the checksum function does not distinguish between positive
> and negative decimals or floats. Is this by design? For example
> select checksum(1.0)
> select checksum(-1.0)
> returns:
> -1374215283
> -1374215283
That's right. CHECKSUM doesn't necessarily return distinct results for
different inputs.
SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
ABC ASH
-- --
1132495864 1132495864
(1 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but ther
e
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...[vbcol=seagreen]
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
>
and negative decimals or floats. Is this by design? For example
select checksum(1.0)
select checksum(-1.0)
returns:
-1374215283
-1374215283Elmer Miller wrote:
> It seems that the checksum function does not distinguish between positive
> and negative decimals or floats. Is this by design? For example
> select checksum(1.0)
> select checksum(-1.0)
> returns:
> -1374215283
> -1374215283
That's right. CHECKSUM doesn't necessarily return distinct results for
different inputs.
SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
ABC ASH
-- --
1132495864 1132495864
(1 row(s) affected)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It is odd. I am trying to use checksum to build an index. The documentation
states that checksum is intended for the building of hash indexes.
However...
I have a table of approx 1.3 million rows and 25 columns (mixed types). I
ran the following statements:
select count(*) as vol, checksum(*) as Hash from <tablename> group by
checksum(*) order by vol desc
This returned 227 records that had the same check sums as another row in the
same table. No row was matched more than twice. This means that 0.01% of the
rows have the same check sums. I then took a look at the rows and they are
very different. They do have the same data types across the columns but ther
e
is a 0.01% chance that my table returns the same checksum despite the data
within them being very different.
This means that I can't really use it as an index. Could there be another
way of creating an index from columns? perhaps an MD5 Hash?
thanks for any help on this.
"David Portas" wrote:
> Elmer Miller wrote:
> That's right. CHECKSUM doesn't necessarily return distinct results for
> different inputs.
> SELECT CHECKSUM(N'ABC') AS ABC,CHECKSUM(N'ASH') AS ASH;
> ABC ASH
> -- --
> 1132495864 1132495864
> (1 row(s) affected)
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Some other checksums will give you better results but basically no checksum
will guarantee you won't have collisions. Checksums are useful as indexes
just as hash functions are useful in building hash tables even though
uniqueness is not guaranteed. If I understood your statistics correctly,
you would return a maximum of two rows which is pretty good for 1.3 million
candidates. Presumably once you have narrowed the search to two or three
rows you can use some other means to get the exact row you want.
A checksum ensures that no two identical rows will return different
checksums but it doesn't ensure that the same checksum can't be returned
from different rows.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sharat Koya" <SharatKoya@.discussions.microsoft.com> wrote in message
news:4E79996B-27A5-46B5-8E9B-E3FFC68024D5@.microsoft.com...[vbcol=seagreen]
> It is odd. I am trying to use checksum to build an index. The
> documentation
> states that checksum is intended for the building of hash indexes.
> However...
> I have a table of approx 1.3 million rows and 25 columns (mixed types). I
> ran the following statements:
> select count(*) as vol, checksum(*) as Hash from <tablename> group by
> checksum(*) order by vol desc
> This returned 227 records that had the same check sums as another row in
> the
> same table. No row was matched more than twice. This means that 0.01% of
> the
> rows have the same check sums. I then took a look at the rows and they are
> very different. They do have the same data types across the columns but
> there
> is a 0.01% chance that my table returns the same checksum despite the data
> within them being very different.
> This means that I can't really use it as an index. Could there be another
> way of creating an index from columns? perhaps an MD5 Hash?
> thanks for any help on this.
>
>
> "David Portas" wrote:
>
Subscribe to:
Posts (Atom)