NEWSEQUENTIALID() defect in SSMS. Wasn’t it fixed already in SQL 2012 RTM?

I have recently received this question from different sources, so I think it’s worth documenting why some people still see validation error for the default value of a column when it’s defined as a NEWSEQUENTIALID() built-in.

The tracking defect on Connect (https://connect.microsoft.com/SQLServer/feedback/details/472092/newsequentialid-bug-in-management-studio) keeps receiving complains about the observed, undesired behavior.

It’s been fixed in Denali (SQL11 or SQL 2012). However, if you don’t just set the expression “right”, you still get the error. And people typically still hits this problem, even with the fix. Let me explain what I mean:

Say you open the designer for the table whose default value’s expression you had previously set to NEWID() and now wish to replace with NEWSEQUENTIALID(). Well, this is what you get:

Notice that the expression has been enclosed by a pair of parenthesis you didn’t write, but that’s just fine.

What people tend to do in many occasions is just position the cursor between the W and the I in the existing newid keyword and write the missing SEQUENTIAL part to achieve their goal.

Once the control loses the focus, its contents gets validated, and just fails with the following error:

 

 

The reason being that the fix just checks whether the expression matches exactly (after leading and trailing spaces get removed) newsequential(). If it doesn’t matches that, it triggers the execution of the validation batch:

BEGIN TRAN

declare @a uniqueidentifier

select @a = (newsequentialid())

which will naturally fail with error 302 (The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression), and abort the transaction.

So, no parenthesis surrounding the expression, spaces between the parenthesis used by the builtin, or anything like that.

With versions of SSMS previous to Denali you’ll have to live with receiving the annoying messages, and asking the designer to ignore the validation errors and keep trying to save the changes. If that’s the only type of error you have, saving the altered schema will succeed.

 

Another typical scenario is that where the user opens in the designer a given table in which a given column’s default had already been set to newsequentialid() and on a later visit to the design view, they try to, for example, set the column as a Unique Key. In that case, validation will also trigger, and it will fail because of the reason explained above.

While this gets properly fixed, make sure you remove any surrounding parenthesis from any reference to the NEWSEQUENTIALID() built-in from the default binding field.