default+udp

IF OBJECT_ID('dbo.T1') IS NOT NULL

  DROP TABLE dbo.T1;

GO

CREATE TABLE dbo.T1

(

  keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY CHECK (keycol > 0),

  datacol VARCHAR(10) NOT NULL

);

GO

IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL

  DROP FUNCTION dbo.fn_T1_getkey;

GO

CREATE FUNCTION dbo.fn_T1_getkey() RETURNS INT

AS

BEGIN

  RETURN

    CASE

      WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1       ELSE (SELECT MIN(keycol + 1)

            FROM dbo.T1 AS A

            WHERE NOT EXISTS

              (SELECT *

               FROM dbo.T1 AS B

               WHERE B.keycol = A.keycol + 1))

    END;

END

GO

ALTER TABLE dbo.T1 ADD DEFAULT(dbo.fn_T1_getkey()) FOR keycol;

INSERT INTO dbo.T1(datacol) VALUES('a');

INSERT INTO dbo.T1(datacol) VALUES('b');

INSERT INTO dbo.T1(datacol) VALUES('c');

DELETE FROM dbo.T1 WHERE keycol = 2;

INSERT INTO dbo.T1(datacol) VALUES('d');

select * from dbo.T1

CREATE FUNCTION dbo.fn_add1(@keycol AS INT) RETURNS INT

WITH SCHEMABINDING

AS

BEGIN

  RETURN @keycol + 1;

END

ALTER TABLE dbo.T1

  ADD col1 AS dbo.fn_add1(keycol)   CONSTRAINT UQ_T1_col1 UNIQUE;

ALTER TABLE dbo.T1

  ADD col2 AS dbo.fn_add1(keycol) PERSISTED NOT NULL

    CONSTRAINT PK_T1 PRIMARY KEY;