Friday, June 15, 2012

make guids auto-populate in SQL

Follow the declaration for the guid id column with DEFAULT newid() when crafting a new table in MSSQL to make the column automatically populate guids upon record insertion. The presence of this code snippet allows us to run insert statements against the table specified below without having to insert guids for the id column. Instead, the guids are generated for us. Otherwise, the insertion would fail as otherwise we would need to specifically insert guids into the Id column when inserting records.

BEGIN TRANSACTION
GO
CREATE TABLE [AAC\vafscjaesct].Whatever
   (
   Id uniqueidentifier NOT NULL DEFAULT newid(),
   DataPoint decimal(3, 0) NOT NULL,
   SomethingElse varchar(3) NOT NULL
   ) ON Form224Data
GO
ALTER TABLE [AAC\vafscjaesct].Whatever ADD CONSTRAINT
   PK_Whatever PRIMARY KEY CLUSTERED
   (
   Id
   ) WITH( STATISTICS_NORECOMPUTE = OFF,
      IGNORE_DUP_KEY = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON) ON Form224Data
GO
ALTER TABLE [AAC\vafscjaesct].Whatever SET (LOCK_ESCALATION = TABLE)
GO
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (1,'foo')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (2,'foo')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (2,'bar')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (3,'bar')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (4,'bar')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (4,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (5,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (6,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (7,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (7,'qux')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (8,'qux')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (9,'qux')
COMMIT

 
 

this is a database table on Twitpic

No comments:

Post a Comment