Sql server newsequentialid error validating the default for column

If you are not worried about fragmentation, what's the point?create table #test(id int, txt varchar(50), gid uniqueidentifier) insert into #test select 1 ,'test','72b48f77-0e26-de11-acd4-001bfc39ff92' union select 2, 'test2', '92f0fc8f-0e26-de11-acd4-001bfc39ff92' union select 3, 'test3', '122aa19b-0e26-de11-acd4-001bfc39ff92' select * from #test order by gid asc create table XXX(some Guid uniqueidentifier DEFAULT NEWSEQUENTIALID(), x INT) create table YYY(some Guid uniqueidentifier DEFAULT NEWSEQUENTIALID(), y Date Time) GO insert into XXX(x) values(1) insert into XXX(x) values(2) insert into XXX(x) values(3) GO insert into YYY(y) values(current_timestamp) insert into YYY(y) values(current_timestamp) insert into YYY(y) values(current_timestamp) GO insert into XXX(x) values(4) insert into XXX(x) values(5) insert into XXX(x) values(6) GO SELECT * FROM XXX 6A6E85CB-CCA3-E111-9E8E-005056C00008 1 6B6E85CB-CCA3-E111-9E8E-005056C00008 2 6C6E85CB-CCA3-E111-9E8E-005056C00008 3 **CCEA7AF2-CCA3-E111-9E8E-005056C00008 4** Gap here because we 'switched' to y CDEA7AF2-CCA3-E111-9E8E-005056C00008 5 CEEA7AF2-CCA3-E111-9E8E-005056C00008 6 SELECT * FROM YYY 8F9438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 .503 909438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 .210 919438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 .220 insert into XXX(x) values(1) insert into XXX(x) values(2) BEGIN TRAN insert into XXX(x) values(3) insert into XXX(x) values(4) ROLLBACK TRAN insert into XXX(x) values(5) insert into XXX(x) values(6) GO 686EFE5B-CDA3-E111-9E8E-005056C00008 696EFE5B-CDA3-E111-9E8E-005056C00008 6C6EFE5B-CDA3-E111-9E8E-005056C00008 6D6EFE5B-CDA3-E111-9E8E-005056C00008 i.e.It seems every time I start doing a project using visual studio, I stumble upon all the possible bugs. I am able to ignore the message in visual studio 2010.Let's hope it would work on the remote sql server I'm about to upload the data to next week. Is there a way to set the default value to a constant value.

Actually, each rec already had a GUID-ID autovalue column in Access. in such case change the datatype in sql server for that column to Varchar(100) instead of rowguid.I would like to have it set by SQL Server Express automatically.There are articles on the web that recommend using one of the following commands as the default value: NEWID() NEWSEQUENTIALID() I put this in VS 2010 column properties of my table, in Default Value or Binding. Are you trying to increment a column ID sequentially upon new insert?Running this in ssms results in squential guids: insert into Table_1 (Name) values('test13a'); insert into Table_1 (Name) values('test14a'); insert into Table_1 (Name) values('test15a'); insert into Table_1 (Name) values('test16a'); insert into Table_1 (Name) values('test17a'); newsequentialid is primarily to solve the issue of page fragmentation when your table is clustered by a uniqueidentifier. I set up two test tables, one where the newsequentialid column is the primary key and one where it is not (like yours), and in the primary key the GUIDs were always sequential. I do not know the internals/technical reasons it behaves that way, but it seems clear that newsequentialid() is only truly sequential when your table is clustered by it.Otherwise, it seems to behave similarly to newid() / Row Guid.

Leave a Reply