?
Solved

Questions about generated hash codes in MS SQL.

Posted on 2011-09-30
7
Medium Priority
?
292 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 1500 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 500 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 1500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 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