For one project of mine I needed to use GUID for column identification inside SQL Server 2008 R2. As usually, I expected everything to go fine. I just add new column (Id in my case) and set it’s data type to “uniqueidentifier”. After that just set that field’s default to whichever function generates new GUID. Since I am running SQL Server 2008 R2 I opted to use NEWSEQUENTIALID (I will explain why in some other post).
As I am programmer first and database administrator only when needed, my natural environment when dealing with database is right-clicking Design and doing changes in more visual way.
As soon as I changed “Default Value or Binding” property to “(NEWSEQUENTIALID())” I was greeted with “Error validating the default for column ‘id’. Do you want to cancel your changes?” After checking all documentation and rechecking it again, I just answered to this question “NO”.
After other fields were adjusted and I tried to save changes, I was greeted with another message: “Warnings were encountered during pre-save validation process, and might result in failure during save. Do you want to continue attempting to save?”. Correct answer here is “YES”.
Save went without hitch. After additional Internet searches I found that this is simple error in validation procedure. And it is in SQL Server since version 2005. Two versions after, it is still alive and kicking.
Until Microsoft fixes this in SQL Server 2020, just remember to hit first “NO” and second “YES”.