Creating table in tempdb using user-defined type

I understand that to create a temp table in tempdb using a user-defined
type, the udt must be defined in Model. However, this doesn't work in
my situation, due to security limitations being placed on the Model
database. (I am using SQL Server 2000).
I'm wondering if there is any viable workaround, short of of using
fixed datatypes when creating the temp table.
For example, say I want to create a temp table as follows:
CREATE TABLE #MyTable(
MyColumn ut_MyColumnType
)
As noted, the above will not work unless ut_MyColumnType is defined in
Model, which can't be done in my situation.
Since I know that ut_MyColumnType is Varchar, I tried using the
COL_LENGTH function to retrieve the size from a column in a non-temp
table that I know is defined using ut_MyColumn type (is there some way
to get the size directly from ut_MyColumnType') into the variable
@.MyColumnLength, and then doing:
CREATE TABLE #MyTable(
MyColumn Varchar(@.MyColumnLength)
)
But this unfortunately doesn't work (Error 170: Incorrect syntax near
'@.MyColumnLength').
Is there any reasonable way to create a temp table using either a
user-defined type not defined in Model, or some substitute method as I
was trying above'IraG wrote:
> I understand that to create a temp table in tempdb using a
> user-defined type, the udt must be defined in Model. However, this
> doesn't work in my situation, due to security limitations being
> placed on the Model database. (I am using SQL Server 2000).
> I'm wondering if there is any viable workaround, short of of using
> fixed datatypes when creating the temp table.
> For example, say I want to create a temp table as follows:
> CREATE TABLE #MyTable(
> MyColumn ut_MyColumnType
> )
> As noted, the above will not work unless ut_MyColumnType is defined in
> Model, which can't be done in my situation.
> Since I know that ut_MyColumnType is Varchar, I tried using the
> COL_LENGTH function to retrieve the size from a column in a non-temp
> table that I know is defined using ut_MyColumn type (is there some way
> to get the size directly from ut_MyColumnType') into the variable
> @.MyColumnLength, and then doing:
> CREATE TABLE #MyTable(
> MyColumn Varchar(@.MyColumnLength)
> )
> But this unfortunately doesn't work (Error 170: Incorrect syntax near
> '@.MyColumnLength').
> Is there any reasonable way to create a temp table using either a
> user-defined type not defined in Model, or some substitute method as I
> was trying above'
You need to use dynamic sql in order to extract the data type and length
of the udt. That's easy enough. The problem is that you are creating a
temp table and because you need to use dynamic sql, the scope of the
temp table is only valid inside the dynamic sql call. Once that call
returns, the temp table is gone. If you were creating a permanent table,
you could do it.
For example, the following fails on the select of the temp table:
exec sp_addtype 'test_type', 'varchar(123)', 'NOT NULL'
go
exec sp_help 'test_type'
create proc dbo.create_temp_table
as
Begin
create table #udt (
[name] varchar(255),
[type] varchar(255),
[length] int,
[precision] int,
[scale] int,
[nullable] varchar(3),
[default_name] varchar(255),
[rule_name] varchar(255),
[collation] varchar(255) )
insert into #udt
exec sp_help 'test_type'
declare @.type varchar(255)
declare @.length int
declare @.new_table nvarchar(1000)
Select TOP 1
@.type = [type],
@.length = [length]
From #UDT
Set @.new_table = 'Create Table #test_table (my_col ' + @.type + '(' +
CAST(@.length as VARCHAR(4)) + '))'
Print @.new_table
Exec (@.new_table)
Select * from #test_table -- fails
Drop table #udt
End
go
exec dbo.create_temp_table
go
exec sp_droptype 'test_type'
go
David Gugick
Imceda Software
www.imceda.com|||Can you create a stored procedure which creates your udt's and mark the proc
as startup
(sp_procoption)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IraG" <ijgla@.clas.ucsb.edu> wrote in message
news:1116459324.112987.289060@.g47g2000cwa.googlegroups.com...
>I understand that to create a temp table in tempdb using a user-defined
> type, the udt must be defined in Model. However, this doesn't work in
> my situation, due to security limitations being placed on the Model
> database. (I am using SQL Server 2000).
> I'm wondering if there is any viable workaround, short of of using
> fixed datatypes when creating the temp table.
> For example, say I want to create a temp table as follows:
> CREATE TABLE #MyTable(
> MyColumn ut_MyColumnType
> )
> As noted, the above will not work unless ut_MyColumnType is defined in
> Model, which can't be done in my situation.
> Since I know that ut_MyColumnType is Varchar, I tried using the
> COL_LENGTH function to retrieve the size from a column in a non-temp
> table that I know is defined using ut_MyColumn type (is there some way
> to get the size directly from ut_MyColumnType') into the variable
> @.MyColumnLength, and then doing:
> CREATE TABLE #MyTable(
> MyColumn Varchar(@.MyColumnLength)
> )
> But this unfortunately doesn't work (Error 170: Incorrect syntax near
> '@.MyColumnLength').
> Is there any reasonable way to create a temp table using either a
> user-defined type not defined in Model, or some substitute method as I
> was trying above'
>|||If you only care about the type, precision and scale and don't want to
preserve rules or defaults in the temp table then you could do it from
an existing column in the current database:
EXEC sp_addtype x_type, 'INTEGER', 'NOT NULL'
GO
CREATE TABLE foo (col x_type)
/* Create a temp table */
SELECT col
INTO #foo
FROM foo
WHERE 1=0
SELECT * FROM #foo
DROP TABLE foo
EXEC sp_droptype x_type
User-defined types are virtually obsolete. In my view there is no good
reason to use them and they come with quite a few divantages. Books
Online recommends using CHECK constraints and DEFAULT constraints
instead and I would suggest you follow that advice if you can. Those
alternatives are more powerful and flexible.
David Portas
SQL Server MVP
--|||David,
Thanks for the great suggestion (SELECT col INTO #foo FROM foo WHERE
1=0). (I would mention that I actually thought of the same thing lying
in bed last night, but I know that nobody would believe me even though
it's true..)
Regarding UDTs being virtually obsolete: where exactly does BOL
recommend using CHECKs and DEFAULTs instead? I'm not clear on how using
these constraints can be used to insure uniformity when defining
columns in different tables.
I realize that UDTs can be a pain when they need to be redefined, but
at least they give you something to get a handle on in that
circumstance. And there is always the hope that future releases of SQL
Server will be more supportive of UDT redefinition.
Would anyone know if SQL Server 2005 offers any better support for UDT
redefinition?|||http://msdn.microsoft.com/library/d...br />
20qb.asp
"Rules are a backward-compatibility feature that perform some of the
same functions as CHECK constraints. CHECK constraints are the
preferred, standard way to restrict the values in a column. CHECK
constraints are also more concise than rules; there can only be one
rule applied to a column, but multiple CHECK constraints can be
applied. CHECK constraints are specified as part of the CREATE TABLE
statement, while rules are created as separate objects and then bound
to the column."
Similar story for Defaults. IMO the advantages of CHECK and DEFAULT
constraints far outweigh the tiny benefit of giving a shorthand name to
a datatype. A data dictionary, good change control procedures and a
quick cut-and-paste between CREATE TABLE statements are a surer way to
guarantee consistency of data elements.
Technically, although Rules and Defaults are deprecated I don't think
Types are (yet) but AFAIK there is no new functionality around them in
2005. Yukon has a new model for UDTs using the CLR.
David Portas
SQL Server MVP
--