Solved

Questions about generated hash codes in MS SQL.

Posted on 2011-09-30
7
258 Views
Last Modified: 2012-05-12
I have seen tables created by others that generate hash code.
How do you do that , as in auto-increment, what is it's purpose and how can I utilize it?
Experts please keep in mind that I use the SQL Management Studio GUI and not code directly.
Still a newb.


Thank you in advance.
0
Comment
Question by:colonelblue
  • 3
  • 2
  • 2
7 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 375 total points
ID: 36894541
colonelblue, you are probably talking about the UNIQUEIDENTIFIER data type that looks like:
7E8DD316-8A75-4482-A179-5C6AB2EF8344

Open in new window


You can create this as a primary key column in your database and set the 'Default value or binding' under column properties to be NEWID() or NEWSEQUENTIALID(), depending on your needs.

Here is an example of 10 NEWID() values:
D896C1D0-181A-4E8F-9BDA-5A7C6CB63610
54EDE693-E04F-40C9-B307-5E69ABFD7609
E06D50A8-3E89-4009-8A0F-5EB007B8B2CC
C82EE48E-87EF-4D6C-B9B9-AEDF13A5C6C6
6A19DF78-3AD2-4E1A-9BBF-C2204AB3D55B
63D89B47-A74F-4A3D-A8B2-C8FE246AE134
49EF1434-CA24-4631-A2E0-CE4166261A7D
5645E7C2-DE67-4544-93E7-D5D67D9FF3FE
963E4EF7-0177-42D3-958E-E6B9DAFE7A79
ADCDFC64-46AC-4510-BF78-FB182DFEEFF2

Open in new window


Here are 10 NEWSEQUENTIALID() values:
E1A2C665-AFEB-E011-99D3-00269E14E808
E2A2C665-AFEB-E011-99D3-00269E14E808
E3A2C665-AFEB-E011-99D3-00269E14E808
E4A2C665-AFEB-E011-99D3-00269E14E808
E5A2C665-AFEB-E011-99D3-00269E14E808
E6A2C665-AFEB-E011-99D3-00269E14E808
E7A2C665-AFEB-E011-99D3-00269E14E808
E8A2C665-AFEB-E011-99D3-00269E14E808
E9A2C665-AFEB-E011-99D3-00269E14E808
EAA2C665-AFEB-E011-99D3-00269E14E808

Open in new window


Hopefully you can see the difference in functionality to go along with what the *SEQUENTIAL* portion of the latter implies.

Hope that helps!

Kevin
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 36895109
Or you could be refering to the HASHBYTES() function that converts character data to binary.  So the following:
SELECT HASHBYTES('SHA1', 'Experts-Exchange')
has the following output:
0xA099D38703F2B29940E51DF06CD5FB3C6F42C023
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 375 total points
ID: 36895129
Good point, Anthony! I got caught up in the "auto-increment" part of the question which made me thing it was for use as primary key. I guess HASHBYTES() can be used in that capacity also. GUID just comes to mind first for me as the system I manage ONLY uses GUIDs as record IDs, so I stare at them all day. ;)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36895353
>>the system I manage ONLY uses GUIDs as record IDs, so I stare at them all day.<<
My condolencies.  :)
0
 

Author Comment

by:colonelblue
ID: 36925943
Hello , sorry for the tardy reply. I had a bad cold and was out for days. :(

Wow OK, please let me know if I grasp this correctly.
There are 2 ways to utilize this:

1) A GUID? A unique identifier , not sure what the G is for.
What woudl I use this for in real world? Use this instead of a regular inceremental ID that goes like 1, 2, 3, 4, 5, etc?
How do I activate that. Please forgive I am still learning.

2) As HASHBYTES as a way to encrypt the  information.
Why else would I want to make the data binary if I may ask?
More importantly what is the process to convert them into Binary and then convert them back?


Experts thank you so much for your generosity.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 375 total points
ID: 36926200
1) Globally Unique IDentifier (GUID). Yes, you could use this as a primary key, instead of an incrementing integer. You would activate it by setting the column data type to UNIQUEIDENTIFIER and using NEWID() or NEWSEQUENTIALID() as the default value binding based on your needs per examples - http:#36894541

From T-SQL: ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), PRIMARY KEY(ID)

2) HASHes are one way. If you want to convert back, you want Encryption/Decryption. Typically you hash data that is necessary in storage, e.g., password, but is very sensitive so you do not want the data readable at rest. Instead, your application always talks and compares in the same hash algorithm. Continuing the example, user enters clear text password, it is hashed to SHA1, then compared to the database stored value. You would not try to make the db value clear text then compare as that defeats the purpose of your security on the column.

For encryption, you need to research the EncryptBy... functions. For example, EncryptByPassphrase() - http://msdn.microsoft.com/en-US/library/ms190357.aspx
0
 

Author Closing Comment

by:colonelblue
ID: 37015009
You guys are Genuises.

[bows] I am not worthy!


Thank you guys!!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now