Solved

RowGUID - why use one?

Posted on 2016-08-14
6
86 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
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 12

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 47

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

832 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