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

checksum_agg and row size error. Require explanation.

Hi,

I can see that by using the object ID rather that the object name, the
following SQL query works. Has anybody got any idea what is causing the
error?

-- Works OK
select o.id
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.id

-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select object_name(o.id)
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by object_name(o.id)

-- Error
-- Server: Msg 1540, Level 16, State 1, Line 1
-- Cannot sort a row of size 8096, which is greater than the
-- allowable maximum of 8094.
select o.name
,checksum_agg(binary_checksum(m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name

-- Workaround
select getdate()
,object_name(x.id)
,check_sum
from (select m.id
,checksum_agg(binary_checksum(m.text)) as check_sum
from syscomments m
inner join
sysobjects o
on m.id = o.id
where o.xtype in ('FN','IF','P','TF','TR','V')
group by m.id) as x

Regards

LiamUse "OPTION (ROBUST PLAN)":

select o.name
,checksum_agg(binary_checksum(*m.text))
from sysobjects o
,syscomments m
where o.id = m.id
and o.xtype in ('FN','IF','P','TF','TR','V')
group by o.name
OPTION (ROBUST PLAN)

This forces SQL Server to use a plan that works for the maximum
potential row size.

Razvan

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 & 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..