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".
I came across the same issue troubleshooting performance issues for a client and discovered they had uniqueidentifiers (using newsequentialid default) as PK but non-clustered indexes. The scripts ran fine converting them to clustered indexes, but I wanted to know what this error was about and it led me to this page. Thanks for the info!
Just wanted to add that years later, using SQL Server 2016, I encountered this issue. It wasn’t even on (NEWSEQUENTIALID()), it was using a T-SQL function I wrote to populate a CHAR column. I had inserted this as the default value on tables before with no issue. But I got the series of dialogs you mention, and, absolutely, first answering “NO” followed by answering “YES” did the trick. I then inserted a row to the table and it worked just fine.
Disregard my previous comment. Looks like I erroneously put the default value on two columns: the one I intended, and another column which was an int column. Got myself confused. My comment is irrelevant.