cleaning html tags.

does any one has any sql server function that passes some text and returns a string without html tags.

example:

nice day
should return nice day

or if other html tags strip them off.

thanks for your help.

-FrThis is not something that SQL Server is particularly good at.

You can probably accomplish this through instantiating the VBScript.RegExp libary and making use of a regular expression to strip out the HTML.

This article gives an example:Regular Expressions in T-SQL.

The pattern would probably be somthing like this: <[^>]*
Terri|||How about doing the same thing in c#?

Thanks
Fra

cheksum function bug

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

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

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

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

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

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:
>

Checking for Primary Key Dependencies

Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.

SQL 2000 serverI don't know about dependancies, but iof you use
EXEC sp_primarykeys (check books for parameter)
This will give you a list of the PK

--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector - www.ciquery.com/tools/sqldirector/

"Dan Hartshorn" <dharts@.yahoo.com> wrote in message news:8ce6b687.0308281418.12df77dc@.posting.google.c om...
> Anybody know if there is a system function that can be used from a
> stored procedure that determines if a given primary key has existing
> dependencies? I want to make a check for this and if there are none, I
> will delete the record. If there are, I will change a field called
> bitStatus from 1 to 0. Enterprise Mgr. does something like this under
> All Tasks, Display Dependencies. The normal way I do it is to manually
> check for the existance of the primary key in every dependent table.
>
> SQL 2000 server|||John Bell (jbellnewsposts@.hotmail.com) writes:
> How about sp_depends?
> If you want different output look at the source in master.

Not sure what you are thinking of, but it does not seem to me that
SQL Server saves any of this kind of information on sysdepends. It
saves CHECK constraints, but not FOREIGN KEY constraints. Presumably,
because CHECK constraints have code, while FK constraints have not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan,

It sounds like you are asking this:

How can I find out if a particular primary key value
exists in the foreign key column of any dependent table?

Or for example, does customer number #324989 have any
entries in the Orders table, the BadChecks table, or the
ReturnedMerchandise table?

The dependencies displayed in Enterprise Manager are
dependencies of database objects (tables, columns,
views, etc.), not of single rows of data, so I don't think
sp_depends or Enterprise Manager is going to have anything.

And I don't think there is simple function to do this,
but there are solutions to the problem. This
is something like a garbage collection problem for
pointers in C - how do we know we've deleted the last pointer
to allocated data?

In the context of PK/FK relationships, you could
keep a master count of referring rows. Keep an integer,
in the main table - not just a bit, and that count will
be at zero when there are no references. It would have to
be updated by triggers on the dependent tables.

Let us know if this is what you are asking about.

-- Steve Kass
-- Drew University
-- Ref: 5D4089BF-0B8A-4D45-B820-923A2D6886EE

Dan Hartshorn wrote:
> Anybody know if there is a system function that can be used from a
> stored procedure that determines if a given primary key has existing
> dependencies? I want to make a check for this and if there are none, I
> will delete the record. If there are, I will change a field called
> bitStatus from 1 to 0. Enterprise Mgr. does something like this under
> All Tasks, Display Dependencies. The normal way I do it is to manually
> check for the existance of the primary key in every dependent table.
> SQL 2000 server