0

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?

1 Answer 1

4

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.

4
  • 1
    Adding that system CLR types (hierarchyid,geometry,geography) work even when clr enabled is set to zero. But your answer is correct, of course.
    – Dan Guzman
    Commented Apr 12, 2021 at 22:27
  • Just curious so I can show my DBA: do you have any documentation for UDTT not requiring CLR?
    – Jack.Frost
    Commented Apr 12, 2021 at 22:48
  • 1
    @JackHerer surely proving it trumps documentation? As discussed in the comments to your cross post though it looks like CREATE TYPE documentation is a bit confusing as the preamble is much the same as it was in 2005 before table types were a thing. So it gives the false impression that types are either aliases or CLR. Someone is submitting an edit so hopefully you can show the updated page to the DBA Commented Apr 12, 2021 at 23:09
  • Agreed, thank you
    – Jack.Frost
    Commented Apr 12, 2021 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.