Setting IDENTITY_INSERT ON for SQL Server table valued type

Question

Is there a way to set IDENTITY_INSERT ON for table valued type? The way how it is done with tables - isn't working.

CREATE TYPE dbo.tvp_test AS TABLE
(
    id INT NOT NULL IDENTITY(1, 1),
    a  INT NULL
);
GO

DECLARE @test dbo.tvp_test;

SET IDENTITY_INSERT @test ON;

INSERT INTO @test VALUES (1, 1);

DROP TYPE dbo.tvp_test;

Error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '@test'

`IDENTITY_INSERT` allows you to alter a table specifically, not a variable. If you need a type which has an `IDENTITY` and one that does not, you'll need 2 types. — Thom A, Nov 16 '20 at 09:09
Why on earth would you want that anyway? IMHO, using identity insert should only be used when copying data from one table to another. At least, I can't seem to think of any other valid use-case for it. — Zohar Peled, Nov 16 '20 at 09:52
@ZoharPeled because I want to have this sequence generated automatically in 1 case and be predefined in another — Dmitrij Kultasev, Nov 16 '20 at 09:59

score 2 · Answer 1 · answered Nov 16 '20 at 09:57

Is there a way to set IDENTITY_INSERT ON for table valued type?

TL;DR: No.

SET IDENTITY_INSERT is a command to be used against a table object, not a variable. SET IDENTITY_INSERT (Transact-SQL):

SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table. ##Syntax
SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }  
Arguments

database_name
Is the name of the database in which the specified table resides.

schema_name
Is the name of the schema to which the table belongs.

table_name
Is the name of a table with an identity column.

Notice that this makes no reference to a variable at all; that's because it can't be used against one.

If you do need two versions of a Table Type, one that allows explicit values of its ID column, and the other that uses an IDENTITY, you will need to define 2 table types; one with an IDENTITY property and the other without:

CREATE TYPE dbo.tvp_test_i AS TABLE (id INT NOT NULL IDENTITY(1, 1),
                                     a INT NULL);

CREATE TYPE dbo.tvp_test_ni AS TABLE (id INT NOT NULL,
                                     a INT NULL);
GO

DECLARE @i dbo.tvp_test_i;
INSERT INTO @i (a)
VALUES(17),(21);

DECLARE @ni dbo.tvp_test_ni;
INSERT INTO @ni (id,a)
VALUES(3,95),(5,34);

SELECT *
FROM @i;

SELECT *
FROM @ni;

score 0 · Answer 2 · answered Nov 19 '20 at 10:40

You could accommodate both ids (auto-generated and/or manually specified) in the same table type with some added overhead:

CREATE TYPE dbo.tvp_test_xyz AS TABLE
(
    autoid INT NOT NULL IDENTITY(1, 1), --auto generated id
    manualid int null, --manually inserted id (filled in when needed)
    id as isnull(manualid, autoid) unique, --the final id, this is used in queries etc....
    a  INT NULL
);
GO


declare @t as dbo.tvp_test_xyz ;

--case, use autogenerated id
insert into @t(a)
select top (100) object_id
from sys.all_objects;

--id = autoid
select *
from @t;

select * from @t
where id between 10 and 20;

--case, manual id
delete from @t;

insert into @t(manualid, a)
select top (100) row_number() over(order by name desc), object_id
from sys.all_objects;

--id = manualid
select * from @t;

select * from @t
where id between 10 and 20;

Setting IDENTITY_INSERT ON for SQL Server table valued type

2 Answers2

SET IDENTITY_INSERT (Transact-SQL)

Arguments