Solved

Questions about generated hash codes in MS SQL.

Posted on 2011-09-30
7
277 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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 …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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