Blog ala Vidar

I’m currently creating a new data model for a customer. They’ve got an Elements table with a field called “Num” (Number) which is supposed to be incremental for each domain. How to solve that? Well, you can create a UDF and set the default value of the No field to this UDF, but then you don’t support inserting more than one row. So, how did I solve it? There’s (at least) two ways. You either create an instead of trigger, or do as I did. Here’s an example:

CREATE TABLE MyElements (
	Domain NVARCHAR(128),
	Num INT,
	PrimKey UNIQUEIDENTIFIER
)
GO

ALTER TABLE MyElements
	ADD CONSTRAINT DF_MyElements_PrimKey
	DEFAULT (NEWSEQUENTIALID())
	FOR PrimKey
GO

ALTER TABLE MyElements
	ADD CONSTRAINT DF_MyElements_Num
	DEFAULT (RAND(CONVERT([INT], CONVERT([VARBINARY](36), NEWID(), 0), 0))*(100000))
	FOR Num
GO

CREATE TRIGGER MyElements_ITrig
ON MyElements
FOR INSERT
AS

WITH MyCTE AS
(
	SELECT
	  PrimKey, Num,
	  ROW_NUMBER() OVER(PARTITION BY Domain ORDER BY PrimKey) AS RowID
		FROM MyElements AS E
)

UPDATE E
	SET E.Num = C.RowID
	FROM MyElements AS E
	INNER JOIN Inserted AS I ON E.PrimKey = I.PrimKey
	INNER JOIN MyCTE AS C ON I.PrimKey = C.PrimKey AND C.Num  C.RowID

First we create a table. Notice that the PrimKey field has NEWSEQUENTIALID() as default, and that the Num field has a default value with some cool random functionality. Then we create the trigger where we have a CTE that creates a ROW_NUMBER partitioned by Domain, ordered by PrimKey. Then we do an update of our table. Try yourself!

INSERT INTO MyElements
  (Domain)
SELECT TOP 1000
  'Omega'
	FROM sys.syscomments