Solved

RowGUID - why use one?

Posted on 2016-08-14
6
127 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 49

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 14

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 51

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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

632 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