Solved

Questions about generated hash codes in MS SQL.

Posted on 2011-09-30
7
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 60

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 60

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 60

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

707 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