[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Questions about generated hash codes in MS SQL.

Posted on 2011-09-30
7
Medium Priority
?
297 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

656 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