User-defined Data Type and #temp table

Question

In the SQL Server 2008 database I have a user-defined data type. In one of stored procs I was going to use that type as a column type of temp table, like:

CREATE TABLE #SomeTable
(
    [some_column] UDT_SomeType,
    -- other columns 
)

Apparently my attempt failed, because, as far as I googled, my UDT_SomeType does not exist in tempdb (where temp table is actually stored).

The UDT_SomeType is actually NUMERIC(19,9), so I can use the following workaround for now:

CREATE TABLE #SomeTable
(
    [some_column] NUMERIC(19,9),
    -- others columns  
)

However I don't think it is a really good solution. Is there any kind of best practice which can be applied here?

Take a look here: http://stackoverflow.com/questions/21127745/how-do-i-create-a-temporary-table-from-a-type — Kamil Gosciminski, Aug 10 '16 at 16:11
@KamilG I understand that works, but the temp table has other columns. So the solution you provided can be applied only if I define a new table-type — Artem, Aug 10 '16 at 17:57

Simone · Answer 1 · 2018-10-18T07:39:57.427

1

Important Information:

This workaround should never be used in any SP or structured query; its only usage should be limited to temporary queries in a protected environment to avoid any possible drawback. It has been written just to demonstrate that in special cases it would be possible to have UDTs in temp tables. Thanks to @Marcel (see comment) for having pointed it out.

Unfortunately is not really straightforward but with some script you can, I would keep a small script to create all those needed types to be launched when necessary.

Aux script:

USE [tempdb]
GO
CREATE TYPE [UDT_SomeType] FROM NUMERIC(19,9)
GO

Normal script:

USE [YOURDB]
GO

CREATE TABLE #SomeTable
(
    [some_column] UDT_SomeType,
    -- other columns 
)

edited Oct 18 '18 at 07:39

answered Nov 28 '17 at 11:28

Simone

1,828
1
13
20

this is a good workaround. An effort to use this approach is minimal – Artem Nov 29 '17 at 07:46
1

This work around has several drawbacks. First of all it is *not reentrant*. When different instances of the code are running concurrently it is not defined which instance creates the UDT. Secondly writing the native type of the UDT explicitly *leads the concept behind UDT ad absurdum*. It violates the DRY principle. – Marcel Oct 16 '18 at 20:51
@Marcel you're perfectly right. In my defense I can only say I forgot to mention that when I use this workaround is just to run some temporary query and never thought to put this in any SP. Reading once more the question i see that an SP is mentioned, so I'll edit my answer with a big CAVEAT. Thanks for pointing it out – Simone Oct 18 '18 at 07:34

User-defined Data Type and #temp table

1 Answers1

Linked