Solved

RowGUID - why use one?

Posted on 2016-08-14
6
65 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 11

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 45

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
using t-sql EXISTS 8 23
Creating Alerts in sql sever 2 13
BULK LOGGED - log full 9 13
Access Migration to Sql Server 2 19
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now