Menu
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?
433
Yes, there are a number of ways you can auto-generate key values for your tables. The most common ways are via the use of the IDENTITY column property or by specifying a uniqueidentifier (GUID) data type along with defaulting with either the NEWID() or NEWSEQUENTIALID() function.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
-- If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function. -- This will return a new random uniqueidentifier e.g. You can directly use this with INSERT statement to insert new row in table.
SQL Server NEWID to Generate GUID Type the below code in SSMS and execute. DECLARE @guid uniqueidentifier = NEWID(); SELECT @guid as 'GUID'; Here we created a variable named guid of data type uniqueidentifier.
Please try using the following query:
CREATE TABLE TEST
(
ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
TESTCOLUMN CHAR(2000) DEFAULT REPLICATE('X',2000)
)
GO
Add DEFAULT(newid()).
43
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.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
