Sql-server – I am wanting to use UDTT in the database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

sql serversql-clruser-defined-table-typeuser-defined-type

Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR)

If they do require CLR, is there an alternative I can use for my stored procedure parameter that serves the same functionality?

Best Answer

User-Defined Table Types are a TSQL feature, not requiring CLR to be enabled. Nor are they CLR types "under the covers". eg

exec sp_configure 'clr enabled', 0
reconfigure with override

go

create type tableType as table(id int)

go
declare @t tableType
insert into @t(id) values (1)

select * from @t

Then to show it's not a CLR type "under the covers" run

select * from sys.dm_clr_appdomains
go
declare @t hierarchyid =  hierarchyid::Parse('/1/1/3/')
go
select * from sys.dm_clr_appdomains

to see how there's an AppDomain created when you create a hierarchyid but not a User-Defined Table Type.

Related Question