What's the difference between the data types ROWGUID and UNIQUEIDENTIFIER

Hello Roy,

The UNIQUEIDENTIFIER data type is a simple data type that allows you to store GUID values in a column. You can choose the GUID value yourself, you can update the value later and the value is stored in the database.

ROWGUID isn’t actually a data type, it is simply shorthand for the UNIQUEIDENTIFIER data type with a few extra properties on the column definition. You can see these properties listed in the properties column on your screenshot.

Aside from UNIQUE, NOT NULL and DEFAULT NEWSEQUENTIALID(), it will also cause the ROWGUIDCOL property to be added to be column. Columns marked by the ROWGUIDCOL flag can be selected or compared by using the ROWGUIDCOL alias, without needing to know the actual name of the column. This can be helpful in various scenarios and SQL Server itself depends on it in a few cases. Most notably, when using a FILESTREAM data type in a table, that table must contain a column marked with the ROWGUICOL flag.

The ROWGUID data type in the SF is marked as Calculated because you cannot supply the value for the column itself, due to the properties on the column definition. It will always generate the next sequential GUID value for any new row. The value also cannot be modified later on. But just like UNIQUEIDENTIFIER, the value is stored in the database.

I hope this helps.

Thank you Vincent,

Let's see if I understand it properly.

A column that is of a domain linked to ROWGUID is always Mandatory (not null), never Identity (it is no integer and has its own incremental function) or Editable (just like a calculated column or a column marked as Identity).
If it is part of a primary key, and it is part of a foreign key then it can be edited just like a lookup.

A UNIQUEIDENTIFIER has non of the desired featured as a default (other than being able to store a GUID). A UNIQUEIDENTIFIER is necessary if you want to store a GUID received from another system that you need to remember in order to communicate it to this other system.

So if I want to have a real GUID within your own application, I better use the ROWGUID and not the UNIQUEIDENTIFIER.

Question:

Are the ROWGUID limitations only within the defaults of a Thinkwise application, or also there when using T-SQL within Thinkwise and/or the database itself? E.g. when I create a record directly in the database table (INSERT INTO) is the ROWGUID automatically filled, or do I have to call NEWSEQUENTIALID() myself? By the way, while I was able to find the Newid() system function, I didn't find this NEWSEQUENTIALID() function in the database.

Hello Roy,

A column that is of a domain linked to ROWGUID is always Mandatory (not null), never Identity (it is no integer and has its own incremental function) or Editable (just like a calculated column or a column marked as Identity).

Correct.

If it is part of a primary key, and it is part of a foreign key then it can be edited just like a lookup.

No, a ROWGUID column can never be edited. It will be generated once when the record is created and will then always be an inseparable identifier for that record. ROWGUIDCOLs are used in database replication scenarios for instance and this way, even if the entire record has changed, two records can still be matched as being the same record and can be replicated accordingly.

A UNIQUEIDENTIFIER has non of the desired featured as a default (other than being able to store a GUID). A UNIQUEIDENTIFIER is necessary if you want to store a GUID received from another system that you need to remember in order to communicate it to this other system.

Correct. It is possible to apply most of the features of ROWGUID to a regular UNIQUEIDENTIFIER column as well. If you are using version 2021.1 of the Thinkwise platform, you can mark any column as having a default value expression. This way, you should be able to add the DEFAULT NEWSEQUENTIALID() properties to the column definition as well. However, the column will remain editable and you can still opt to enter your own GUID rather than the generated one. Of course this can be prevented through the various methods of column-level authorization that our platform offers, but these will only apply to the Thinkwise Platform. Other applications will be able to insert records with their own GUID values or update existing ones.

So if I want to have a real GUID within your own application, I better use the ROWGUID and not the UNIQUEIDENTIFIER.

It really depends on what your requirements are. Since you specifically want a GUID, I imagine that you have some requirements that I can’t foresee, otherwise an identity column would probably suffice. If you make a UNIQUEIDENTIFIER column, mark it as primary key and add a default value expression to it, you can get very close to what ROWGUID would offer you as well. It will be a little more flexible, whether that’s a good thing or not depends on the situation.

Are the ROWGUID limitations only within the defaults of a Thinkwise application, or also there when using T-SQL within Thinkwise and/or the database itself? E.g. when I create a record directly in the database table (INSERT INTO) is the ROWGUID automatically filled, or do I have to call NEWSEQUENTIALID() myself?

These properties are native to SQL Server and are managed by SQL Server. They will always apply.

By the way, while I was able to find the Newid() system function, I didn't find this NEWSEQUENTIALID() function in the database.

NEWSEQUENTIALID() is a special built-in function which can only be used as the DEFAULT expression in a column definition. It cannot simply be selected like NEWID() can. NEWSEQUENTIALID() is much faster when used on indexed columns, as the value is guaranteed to be greater than any of the previous values, making it predictable where it should be placed on an index. If it is not possible to use NEWSEQUENTIALID() as a default value expression on a regular UNIQUEIDENTIFIER column (I haven’t tested this, but I can imagine that it might not work, since it’s not a selectable function) and you want to make it a primary key column, then I would say just for that reason ROWGUID is probably the better choice.

I hope this explains the differences more clearly.

Thanks Vincent,

Only question that remains is how do I reference a table with a ROWGUID.

Normally when I have a record in a table it can reference through a foreign key column to the primary key column of another table. Thinkwise lets you drag the relation between the source and target tables (that even adds the primary key column to the target table) and even gives you a default lookup for this. The foreign key column is editable in this target table (even if it is not in the source table), otherwise you cannot select an item from the source table.

Your NO-answer implies that this is not possible if the primary key column in the source table is a ROWGUID, as by drawing a reference to the target table it also will be a ROWGUID in the target table. Does this mean that in this case I will have to manually change the domain of this foreign key column from ROWGUID to UNIQUEIDENTIFIER? How else do I create a lookup to a source table in which the primary key is of type ROWGUID?

Hello Roy,

It is true that ROWGUID values can never be edited, but that doesn’t mean that they can’t be referenced and chosen in a target table as a lookup value. I understand why you might think that these two things are mutually exclusive, but they aren’t. I have highlighted below where your understanding is wrong.

Your NO-answer implies that this is not possible if the primary key column in the source table is a ROWGUID, as by drawing a reference to the target table it also will be a ROWGUID in the target table.

When drawing a reference to a ROWGUID column in a source table, the corresponding column in the target table doesn’t need to be ROWGUID as well. In fact, it shouldn’t be because then you get exactly the problem that you describe. ROWGUID is not a data type just like IDENTITY is not a data type. When you reference an IDENTITY column, your target column data type will typically be INT and will have none of the IDENTITY properties applied to it, it merely references the value. When you reference a ROWGUID column, your target column data type will be UNIQUEIDENTIFIER and it will have none of the ROWGUID properties applied to it.

The confusion is probably caused by the fact that we offer ROWGUID as a data type in the SF when it is in fact UNIQUEIDENTIFIER + some properties. Whereas for IDENTITY columns we have decided not to make a separate data type, but rather add a checkbox at the column level to indicate that some properties should be added to it, even though it is conceptually the same thing. This is a bit inconsistent.

I hope this helps.

As an addition to the above, if referencing a ROWGUID column without having the target column yet causes the target column to be added as a ROWGUID column as well, then this is an oversight in the SF. As far as I understand it, that should not be how it behaves. Again, this issue is probably caused by the choice of defining ROWGUID as its own data type in the SF.

I realize that I misunderstood your comment a little.

Does this mean that in this case I will have to manually change the domain of this foreign key column from ROWGUID to UNIQUEIDENTIFIER?

So yes, this is exactly the case. The SF should have done this for you but doesn’t due to a bug. You can manually correct it in that way. Could you report an issue for this in TCP? That way we can track it and update you when it is fixed.

I am currently trying to implement the above.

I notice that when having a table with a ROWGUID id you cannot make it primary. If this would have been the only primary key you have a problem with validation “Table has no primary key".

Creating a relation from a source that has a ROWGUID id to a target that either has a ROWGUID or UNIQUEIDENTIFIER is possible, but validation results in various errors.

Question remains:

How can you use ROWGUID when wanting a reference relation on that ROWGUID?

Hello Roy,

You are doing it correctly, the validation is mistaken. This is an extension of the issue that causes the target column to be created as a ROWGUID as well, when drawing a reference. The Software Factory doesn’t handle ROWGUID very well right now. It treats ROWGUID as a data type even though it isn’t, causing errors like the ones you’re experiencing. Could you report an issue for this as well please?

As for using ROWGUID as the primary key, this is technically possible on the database. I’m actually not sure why the Software Factory doesn’t allow this. ROWGUIDs make for better clustered indexes than regular UNIQUEIDENTIFIERs since they are sequential. Maybe someone else can elaborate on the reason that they can’t be primary key. You could post an idea to allow ROWGUIDs to be primary keys in the Ideas section.

Hope that helps.

Issue reported under number: 79675