Solved

RowGUID - why use one?

Posted on 2016-08-14
6
110 Views
Last Modified: 2016-08-15
Hi, I'm working with MS T-SQL Management Studio. I have a question about ROWGUIDs. I came across a Database Table that had a "rowguid", which is automatically generated using the NEWID( ) function and although I understand what it is...(i.e. a unique identifier value for that row) I still have a couple of questions.

So, let say I have a Products Table and I'm debating whether to add a "rowguid" or not. - Why would I want one and how would I use it?

The "Identity" priamry key already provides me a unique identifier for that row, so why would I need (or want) a second one? Also, its a 32 but number...how would I ever use the "rowguid" once I had it?

Thanks for your help and advice,
Fulano
0
Comment
Question by:Mr_Fulano
[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
6 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 41756016
Typically a rowguid is used with replication, such as having several satellite databases being replicated to a central db.
0
 
LVL 13

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 100 total points
ID: 41756157
In addition to replication, some frameworks (like the .NET Entity Framework) use GUIDs as keys for the entities. So, a lot depends upon the following:
a. The amount of data that will be generated and stored
b. The code which is going to consume the data (it helps if the underlying storage uses keys in the same format as the code)
c. The nature of integration implemented (replication, etc)
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 41756200
Big difference is a ROWGUID is intended to be unique worldwide. So, unless you need an unique universal key (Replication solutions usually use this to avoid the same key being generated for the Publisher and Subscriber databases) you should go always for an identity data type.
1
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 200 total points
ID: 41756311
All the answers are fine, to add to your understanding: You can also use a RowGUID instead of an identity column, there is nothing forcing to use both.

A GUID can then be used in the same way as the identity integer ID, it is just a data type taking more bytes than an int. So another reason for it despite its global uniqueness is you can have more rows with it than 2 billion if you'd need that.

Some more obvious things:

Replication and merging is a hard problem, when two different rows got the same integer ID from an identity column used in two affiliates databases, for example.

Any client-side tool can generate GUIDs without taking a roundtrip to the database, so eg you can generate records in C# DataTables in c# and also related records in child tables can have data referencing that new rows before even committing parent rows. The only thing you need to assure is committing all new data in the correct order to not break referential integrity foreign key constraints. With integers you can't do that. That's for example one advantage you can have even in a database not needing RowGUIDs for replication.

Bye, Olaf.
0
 

Author Comment

by:Mr_Fulano
ID: 41756340
All very good information. Thank you ALL.
0
 

Author Closing Comment

by:Mr_Fulano
ID: 41756343
Thank you ALL for your advice. I learned a lot!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Remove () 10 42
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
T-SQL Query 9 37
SQL Server and Access Database (Project Codes) get the next record 43 37
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

710 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