IDENTITY as Default values

I need some feedback on a tricky subject:

Given:

CREATE TABLE [TEST] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[id2] [bigint] NULL CONSTRAINT [DF__TEST__id2__7C104AB9] DEFAULT
(dbo.IDENT('test'))
[field] varchar NULL
) ON [PRIMARY]

Given the following function

CREATE FUNCTION dbo.IDENT(@table sysname)
RETURNS bigint
AS
BEGIN
RETURN(IDENT_CURRENT(@table))
END

The idea is when id2 is not overwritten, it will be identical to id.

The table is actually a denormalized parent child relationship, with id
the internal key of the child, and id2 the key of the parent. You can
use this to have versioning of records within the table.

In this table id2 is not unique within the table, but a new parent
record id needs to be created sometimes. When the parent id2 exists, a
copy action within the table of records suffices.

Note that this is not my idea of a good design. I just need to fix an
app without going into the application code, and without creating a
trigger or proc, so my manouvering is extremely limited.

I checked this setup, even on a interleaved transaction scenario with 2
updating connections, and it seems to work, but I cannot prove to
myself this is really foolproof. Note that you need the function, using
IDENT_CURRENT directly in the default constraint does not work!

Dm Unseen

> Note that this is not my idea of a good design. I just need to fix an
> app without going into the application code, and without creating a
> trigger or proc, so my manouvering is extremely limited.

Why not a trigger? That should be transparent to application code.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DM Unseen" wrote in message

>I need some feedback on a tricky subject:
>
> Given:
>
> CREATE TABLE [TEST] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [id2] [bigint] NULL CONSTRAINT [DF__TEST__id2__7C104AB9] DEFAULT
> (dbo.IDENT('test'))
> [field] varchar NULL
> ) ON [PRIMARY]
>
> Given the following function
>
> CREATE FUNCTION dbo.IDENT(@table sysname)
> RETURNS bigint
> AS
> BEGIN
> RETURN(IDENT_CURRENT(@table))
> END
>
> The idea is when id2 is not overwritten, it will be identical to id.
>
> The table is actually a denormalized parent child relationship, with id
> the internal key of the child, and id2 the key of the parent. You can
> use this to have versioning of records within the table.
>
> In this table id2 is not unique within the table, but a new parent
> record id needs to be created sometimes. When the parent id2 exists, a
> copy action within the table of records suffices.
>
> Note that this is not my idea of a good design. I just need to fix an
> app without going into the application code, and without creating a
> trigger or proc, so my manouvering is extremely limited.
>
> I checked this setup, even on a interleaved transaction scenario with 2
> updating connections, and it seems to work, but I cannot prove to
> myself this is really foolproof. Note that you need the function, using
> IDENT_CURRENT directly in the default constraint does not work!
>
> Dm Unseen
>