how the db can auto generate uniqueindentifiers?

Question

I have a sql table with id (guid) column. how can I force the DB to auto generate a new guid for every new record?

Solution 1

Add DEFAULT(newid()).

Solution 2

Using the DEFAULT(newid()) approach, as @SLaks mentioned, you would allow anyone to alter the Guid value from the Guid column, and that could be bad.

One approach to avoid that would be setting the "Guid" Column as a Persisted Computed Column. That way you couldn't even "force" another value onto the Column. But, since the NEWID() function is Non Deterministic, you couldn't define the Computed Column as Persisted, and the whole approach goes down, since not setting is as Persisted would result as a new Guid everytime you Select that row.

That said, I believe you have 2 choices: Stick with the DEFAULT(newid()) approach or work with Triggers for that.

Solution 3

Please try using the following query:

CREATE TABLE TEST
(
    ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO

Comments

[Elad Benda]

thanks. will it make any sance to define it as identitiy guid?