Checksum and unique key creation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Checksum and unique key creation

Author

Topic

CanadaDBA

583 Posts

Posted - 2012-01-10 : 13:05:09

I want to create a unique key based on a string column. I am using CheckSum for this reason but both following return -1786986963.

CHECKSUM('Misc Partner - Central North - Central North')
CHECKSUM('Misc Partner - Central South - Central South')

I heard it is possible to apply some technique to produce (almost) unique values using checksum. Please advise if you can help.

Thanks,

Canada DBA

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 13:07:25

ALTER TABLE myTable99 ADD myCol99 int NOT NULL IDENTITY(1,1)

That should give you a unique [Id] per row

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-10 : 13:32:18

Might help to know why you want an "almost unique hashing key" for a given row?

CanadaDBA

583 Posts

Posted - 2012-01-10 : 13:46:15

quote:
Originally posted by Kristen

Might help to know why you want an "almost unique hashing key" for a given row?

I prefer unique but know using different algorithms only reduces the chance of non unique keys. But the smaller chance the better.

I have 16 columns in my DW and needed to create unique values in Dimension views. Using HashByte() instead of Checksum seems resolved my problem.

HASHBytes('MD5','Misc Partner - Central North - Central North')

Canada DBA

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 13:55:07

quote:
Originally posted by CanadaDBA

quote:
Originally posted by Kristen

Might help to know why you want an "almost unique hashing key" for a given row?

I prefer unique but know using different algorithms only reduces the chance of non unique keys. But the smaller chance the better.

I have 16 columns in my DW and needed to create unique values in Dimension views. Using HashByte() instead of Checksum seems resolved my problem.

HASHBytes('MD5','Misc Partner - Central North - Central North')

Canada DBA

If you include the creation date down to the microsecond, I would guess that WOULD be unique

WHY do you need to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-10 : 16:17:37

quote:
Originally posted by CanadaDBA

I prefer unique but know using different algorithms only reduces the chance of non unique keys. But the smaller chance the better.

I have 16 columns in my DW and needed to create unique values in Dimension views. Using HashByte() instead of Checksum seems resolved my problem.

HASHBytes('MD5','Misc Partner - Central North - Central North')

Canada DBA

As you have found out CHECKSUM does not produce unique values. HASHBYTES will produce MORE unique values, but not all will be unique. Sounds like you are going down the wrong path here. If you wanted to provide more information, we might be able to help you before it's too late.

quote:
Originally posted by X002548
If you include the creation date down to the microsecond, I would guess that WOULD be unique

WHY do you need to do this?

<snip>huge sig</snip>

If you try to use a date as part of a unique key, you will get burned eventually.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-11 : 03:38:07

quote:
Originally posted by CanadaDBA

I want to create a unique key based on a string column. I am using CheckSum for this reason but both following return -1786986963.

CHECKSUM('Misc Partner - Central North - Central North')
CHECKSUM('Misc Partner - Central South - Central South')

SELECT *, DENSE_RANK() OVER (PARTITION BY Col1 ORDER BY Col1) FROM dbo.Table1

N 56°04'39.26"
E 12°55'05.63"

CharlotteCodes
Starting Member

1 Post

Posted - 2012-09-14 : 16:44:01

quote:
Originally posted by CanadaDBA

quote:
Originally posted by Kristen

Might help to know why you want an "almost unique hashing key" for a given row?

I prefer unique but know using different algorithms only reduces the chance of non unique keys. But the smaller chance the better.

I have 16 columns in my DW and needed to create unique values in Dimension views. Using HashByte() instead of Checksum seems resolved my problem.

HASHBytes('MD5','Misc Partner - Central North - Central North')

Canada DBA

I suspect I'm doing something similar - I want to create a Unique ID on the fly to simulate a DW so that if/when a DW is in place, I can swap out my Cube views with no impact.

My dimension cardinality is very small so risk of dup is already super low.

I would like to know, if you've been using this approach to-date, have you had any issues?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources