Adding a uniqueidentifier column and adding the default to generate new guid

I have the following SQL command:

ALTER TABLE dbo.UserProfiles
ADD ChatId UniqueIdentifier NOT NULL,
UNIQUE(ChatId),
CONSTRAINT "ChatId_default" SET DEFAULT newid()

I want to be able to make this column unique, and I want it to be able to generate a new guid every time a row is added to the table. This column is not an IDENTITY column because I already have one. This is something separate. How would I go about adding this column to a table with users already in it.

Answers


see this sample:

create table test (mycol UniqueIdentifier NOT NULL default newid(), name varchar(100))
insert into test (name) values ('Roger Medeiros')
select * from test

for add a not null field on a populated table you need this.

alter table test add mycol2 UniqueIdentifier NOT NULL default newid() with values

CREATE UNIQUE NONCLUSTERED INDEX IX_test ON dbo.test
(
mycol
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Don't use newid() as default, instead use newsequentialid(). newid() creates a lot of fragmentation and that's bad for indexes.

As far as adding the new column to a table with existing data, simply do this:

    ALTER TABLE your_table
    ADD your_column UNIQUEIDENTIFIER DEFAULT newsequentialid() NOT null

Need Your Help

Remember (persist) the filter, sort order and current page of jqGrid

jquery cookies persistence jqgrid filtering

My application users asked if it were possible for pages that contain a jqGrid to remember the filter, sort order and current page of the grid (because when they click a grid item to carry out a ta...