Do SQL Server User Defined Datatypes (UDT) affect performance?

By:   |   Comments (3)   |   Related: > Performance Tuning


Problem

Using User Defined Datatypes (UDTs) in the database has two major advantage; first, for columns that should have same data type and are, for example, compared or joined, by using same UDT, you can make sure that they are really compatible and secondly, you could use UDTs to fetch a list of all columns of the same specific type, i.e.: an ID number, by viewing the UDT's dependencies.  There is a legend among SQL Server developers and DBAs saying that UDTs may degrade performance. In this article I prove whether this believe is right or wrong.

Solution

In order to test the performance implications of UDTs on a database, I created two identical databases.  Both databases contain the same schema and same data, with the only difference that in one of the databases, I use native data types and in the other I use only UDTs.

For the tests, I decided to use the AdventureWorks sample database, shipped by Microsoft, you can download it here.

My test databases are AdventureWorks and AdventureWorks_2.

In AdventureWorks_2 database only, I created additional UDTs and I modified all columns in all tables of the [Sales] schema to use UDTs only.

The code to create the new UDTs in AdventureWorks_2 is:

use AdventureWorks_2
-- 
====================================
-- Create User-defined Data Types
-- ====================================
CREATE TYPE u_KEY FROM int null ;
CREATE TYPE u_rowguid FROM uniqueidentifier not null ;
CREATE TYPE u_Datetime FROM datetime null ;
CREATE TYPE u_name FROM nvarchar(50) not null ;
CREATE TYPE u_Description FROM nvarchar(255) null ;
CREATE TYPE u_smallmoney FROM smallmoney null ;
CREATE TYPE u_QTY FROM int null ;
CREATE TYPE u_CountryRegionCode FROM nvarchar(3) null ;
CREATE TYPE u_Money FROM money null ;
CREATE TYPE u_TaxType FROM tinyint null ;
CREATE TYPE u_CurrencyCode FROM nchar(3) null ;
CREATE TYPE u_CardType FROM nvarchar(50) null ;
CREATE TYPE u_CardNumber FROM nvarchar(25) null ;
CREATE TYPE u_Month FROM tinyint null ;
CREATE TYPE u_Year FROM smallint null ;

And here is an example of a table in the Sales schema, using the newly created UDTs:

DB Diagram1

In addition, I created 100 more random UDTs in AdventureWorks_2, just to add more overhead to the sys.systypes table, which contains the native data types as well as the UDTs.

Here is the code to create the additional overhead UDTs:

-- ====================================
-- Create User-defined Data Types 
-- ====================================
-- The following code creates 100 UDTs: 
-- * 50 int UDTs 
-- ** 25 ALLOW NULL 
-- ** 25 NOT NULL 
-- * 50 varchar(255) UDTs 
-- ** All NOT NULL 
USE AdventureWorks_2
GO
declare @cmd varchar(8000),
@i int
set @i = 1
while @i <= 100
begin
  set @cmd = 'CREATE TYPE UDT_' + convert(varchar(10),@i) + 
  ' FROM ' + case when @i <= 25 then 'int ' when @i between 26 and 50 then 'char(10) ' 
  else 'varchar(255) ' end +
  case when @i <= 20 then ' not null ' when @i between 20 and 40 then ' NULL' else 
  ' not null ' end 
  print @cmd;
  exec (@cmd);
  set @i = @i + 1
end;

The output to create these additional UDTs will be like the following, but will have 100 rows.

CREATE TYPE UDT_1 FROM int not null 
CREATE TYPE UDT_2 FROM int not null 
CREATE TYPE UDT_3 FROM int not null 
CREATE TYPE UDT_4 FROM int not null 
CREATE TYPE UDT_5 FROM int not null 
CREATE TYPE UDT_6 FROM int not null 
CREATE TYPE UDT_7 FROM int not null 
CREATE TYPE UDT_8 FROM int not null 
CREATE TYPE UDT_9 FROM int not null 
CREATE TYPE UDT_10 FROM int not null 
CREATE TYPE UDT_11 FROM int not null 
...

Performance comparison

The next step is to compare the same SELECT, INSERT, UPDATE and DELETE commands between the two database.  Comparison was captured by profiler. The code executed in both databases is below.

INSERT and SELECT

I scripted the Customer table in each of the databases and recreated an identical table Customer_2 (without the IDENTITY column).

In AdventureWorks, the table has no UDTs and in AdventureWorks_2 UDTs are in use.

AdventureWorks:

USE [AdventureWorks]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Sales].[FK_Customer_2_SalesTerritory_TerritoryID]') AND parent_object_id = OBJECT_ID(N'[Sales].[Customer_2]' ))
ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [FK_Customer_2_SalesTerritory_TerritoryID]
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Sales].[CK_Customer_2_Customer_2Type]') AND parent_object_id = OBJECT_ID(N'[Sales].[Customer_2]' ))
ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [CK_Customer_2_Customer_2Type]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Customer_2_rowguid]') AND type = 'D' )
BEGIN
   ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [DF_Customer_2_rowguid]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Customer_2_ModifiedDate]') AND type = 'D')
BEGIN
   ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [DF_Customer_2_ModifiedDate]
END
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sales].[Customer_2]') AND type in (N'U'))
DROP TABLE [Sales].[Customer_2]
GO
CREATE TABLE [Sales].[Customer_2](
[Customer_2ID] [int] NOT NULL,
[TerritoryID] [int] NULL,
[AccountNumber] nvarchar(15),
[Customer_2Type] [nchar](1) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Customer_2_Customer_2ID] PRIMARY KEY
 CLUSTERED 
 (
   [Customer_2ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) 
 ON [PRIMARY]
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for Customer_2 records.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'COLUMN',@level2name=N'Customer_2ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the territory in which the Customer_2 is located. Foreign key to SalesTerritory.SalesTerritoryID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'COLUMN',@level2name=N'TerritoryID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique number identifying the Customer_2 assigned by the accounting system.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'COLUMN',@level2name=N'AccountNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Customer_2 type: I = Individual, S = Store' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'COLUMN',@level2name=N'Customer_2Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'COLUMN',@level2name=N'rowguid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Current Customer_2 information. Also see the Individual and Store tables.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'CONSTRAINT',@level2name=N'PK_Customer_2_Customer_2ID'
GO
ALTER TABLE [Sales].[Customer_2] WITH CHECK ADD CONSTRAINT [FK_Customer_2_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
GO
ALTER TABLE [Sales].[Customer_2] CHECK CONSTRAINT [FK_Customer_2_SalesTerritory_TerritoryID]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing SalesTerritory.TerritoryID.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'CONSTRAINT',@level2name=N'FK_Customer_2_SalesTerritory_TerritoryID'
GO
ALTER TABLE [Sales].[Customer_2] WITH CHECK ADD CONSTRAINT [CK_Customer_2_Customer_2Type] CHECK ((upper([Customer_2Type])='I' OR upper([Customer_2Type])='S'))
GO
ALTER TABLE [Sales].[Customer_2] CHECK CONSTRAINT [CK_Customer_2_Customer_2Type]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [Customer_2Type]=''I'' OR [Customer_2Type]=''i'' OR [Customer_2Type]=''S'' OR [Customer_2Type]=''s''' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'CONSTRAINT',@level2name=N'CK_Customer_2_Customer_2Type'
GO
ALTER TABLE [Sales].[Customer_2] ADD CONSTRAINT [DF_Customer_2_rowguid] DEFAULT (newid()) FOR [rowguid]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of NEWID()' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'CONSTRAINT',@level2name=N'DF_Customer_2_rowguid'
GO
ALTER TABLE [Sales].[Customer_2] ADD CONSTRAINT [DF_Customer_2_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'TABLE',@level1name=N'Customer_2', @level2type=N'CONSTRAINT',@level2name=N'DF_Customer_2_ModifiedDate'
GO

AdventureWorks_2:

USE [AdventureWorks_2]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Sales].[FK_Customer_2_SalesTerritory_TerritoryID]') AND parent_object_id = OBJECT_ID(N'[Sales].[Customer_2]'))
ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [FK_Customer_2_SalesTerritory_TerritoryID]
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Sales].[CK_Customer_2_Customer_2Type]') AND parent_object_id = OBJECT_ID(N'[Sales].[Customer_2]'))
ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [CK_Customer_2_Customer_2Type]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Customer_2_rowguid]') AND type = 'D')
BEGIN
   ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [DF_Customer_2_rowguid]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Customer_2_ModifiedDate]') AND type = 'D')
BEGIN
   ALTER TABLE [Sales].[Customer_2] DROP CONSTRAINT [DF_Customer_2_ModifiedDate]
END
GO
/****** Object: Table [Sales].[Customer_2] Script Date: 04/05/2009 21:00:26 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sales].[Customer_2]') AND type in (N'U'))
DROP TABLE [Sales].[Customer_2]
GO
CREATE TABLE [Sales].[Customer_2](
[Customer_2ID] [dbo].[u_KEY] NOT NULL,
[TerritoryID] [dbo].[u_KEY] NULL,
[AccountNumber] dbo.AccountNumber null,
[Customer_2Type] [nchar](1) NOT NULL,
[rowguid] [dbo].[u_rowguid] NOT NULL,
[ModifiedDate] [dbo].[u_Datetime] NOT NULL,
CONSTRAINT [PK_Customer_2_Customer_2ID] PRIMARY KEY CLUSTERED 
   ([Customer_2ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'Primary key for Customer_2 records.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'COLUMN',@level2name=N'Customer_2ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'ID of the territory in which the Customer_2 is located. Foreign key to SalesTerritory.SalesTerritoryID.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'COLUMN',@level2name=N'TerritoryID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'Unique number identifying the Customer_2 assigned by the accounting system.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'COLUMN',@level2name=N'AccountNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Customer_2 type: I = Individual, S =   Store',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'COLUMN',@level2name=N'Customer_2Type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'COLUMN',@level2name=N'rowguid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'Date and time the record was last updated.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'Current Customer_2 information. Also see the Individual and Store tables.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name=N'Customer_2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'Clustered index created by a primary key constraint.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'CONSTRAINT',@level2name=N'PK_Customer_2_Customer_2ID'
GO
ALTER TABLE [Sales].[Customer_2] WITH CHECK ADD CONSTRAINT [FK_Customer_2_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
GO
ALTER TABLE [Sales].[Customer_2] CHECK CONSTRAINT [FK_Customer_2_SalesTerritory_TerritoryID]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=  N'Foreign key constraint referencing SalesTerritory.TerritoryID.',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'CONSTRAINT',@level2name=N'FK_Customer_2_SalesTerritory_TerritoryID'
GO
ALTER TABLE [Sales].[Customer_2] WITH CHECK ADD CONSTRAINT [CK_Customer_2_Customer_2Type] CHECK ((upper([Customer_2Type])='I' OR upper([Customer_2Type])='S'))
GO
ALTER TABLE [Sales].[Customer_2] CHECK CONSTRAINT [CK_Customer_2_Customer_2Type]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [Customer_2Type]=''I'' OR [Customer_2Type]=''i'' OR [Customer_2Type]=''S'' OR [Customer_2Type]=  ''s''',@level0type=N'SCHEMA',@level0name= N'Sales',@level1type=N'TABLE',@level1name= N'Customer_2',@level2type=N'CONSTRAINT',@level2name=N'CK_Customer_2_Customer_2Type'
GO
ALTER TABLE [Sales].[Customer_2] ADD CONSTRAINT [DF_Customer_2_rowguid] DEFAULT (newid()) FOR [rowguid]
GO
ALTER TABLE [Sales].[Customer_2] ADD CONSTRAINT [DF_Customer_2_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO

I then executed the following commands to populate the tables and then a SELECT to see how many pages each table contains:

truncate table AdventureWorks_2.Sales.Customer_2
go
truncate table AdventureWorks.Sales.Customer_2
go
insert into AdventureWorks_2.Sales.Customer_2
select * from AdventureWorks_2.Sales.Customer
go
insert into AdventureWorks.Sales.Customer_2
select * from AdventureWorks.Sales.Customer
go
-- Show how many pages each table contains:
set statistics io on
go
select * from AdventureWorks.Sales.Customer_2
go
select * from AdventureWorks_2.Sales.Customer_2
go
select * from AdventureWorks.Sales.Customer
go
select * from AdventureWorks_2.Sales.Customer
go

The statistics IO shows that Customer tables in both databases have same amount of pages and the same with Customer_2 tables:

19185 row(s) affected)
Table 'Customer_2'. Scan count 1, logical reads 162, physical reads 0, read-ahead reads 0, 
     lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(19185 row(s) affected)
Table 'Customer_2'. Scan count 1, logical reads 162, physical reads 0, read-ahead reads 0, 
     lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(19185 row(s) affected)
Table 'Customer'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, 
     lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(19185 row(s) affected)
Table 'Customer'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, 
     lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I executed the above batch 3 times in a row and here are the average results from Profiler:

INSERT ... SELECT

   Execution

CPU

 READS WRITES DURATION
AdventureWorks_2  #1 563 40047 160 554
   #2 547 40044 154 574
   #3 547 40047 155 621
  Average 552.3333 40046 156.3333 583
AdventureWorks  #1 515 40194 160 557
   #2 516 40194 153 558
   #3 532 40194 157 599
  Average 521 40194 156.6667 571.3333333

SELECT * FROM Customer_2

   Execution CPU READS WRITES DURATION
AdventureWorks_2  #1 0 162 0 601
   #2 31 162 0 600
   #3 31 162 0 587
  Average 20.66667 162 0 596
AdventureWorks  #1 16 162 0 605
   #2 15 162 0 671
   #3 15 162 0 601
  Average 15.33333 162 0 625.6666667

Both results show no correlation between the usage of UDTs and performance degradation.  I tried the same exercise with two other tables (SalesOrderDetail and SalesPerson and got similar results).

DELETE

The following script was executed 3 times in a row in each database:

drop table #tmp
go
-- Create a temporary table with customers id and a seq number
SELECT top 10 id = identity(int,1,1), Customer_2ID 
INTO #tmp 
FROM Sales.Customer_2 
ORDER BY Customer_2ID
go
-- Loop on each one of the rows in the temporary table and delte one row from Customer_2
declare @i int
set @i = 1
while @i <= 10
begin
   DELETE Sales.Customer_2 
   FROM Sales.Customer_2 c INNER JOIN #tmp ON #tmp.Customer_2ID = c.Customer_2ID 
   WHERE #tmp.id = @i
   set @i = @i + 1
end

Profiler's results of the loop containing the deletion of the rows gave the same exact results for all 6 executions:

CPU READS WRITES DURATION
0 60 0 0

UPDATE

Last, the following update command was executed in the two databases 3 times in a row each:

update Sales.Customer_2 set ModifiedDate = DATEADD(dd,1,ModifiedDate)
go

And the results from Profiler:

   Execution CPU READS WRITES DURATION
AdventureWorks_2  #1 93 166 126 95
   #2 63 162 0 102
   #3 62 162 0 107
  Average 72.66667 163.3333 42 101.3333333
AdventureWorks  #1 63 166 107 534
   #2 78 162 0 102
   #3 94 162 0 98
  Average 78.33333 163.3333 35.66667 244.6666667

Again, the results were very similar.

Summary

It seems that there is no overhead or performance implications to using UDTs.

The reason is in the fact that  the sys.columns table contains two columns that are always populated, one for the system_type_id and one for the user_type_id, as shown here:

syscolumns

The SQL Server optimizer is obviously using the system_type_id and therefore the existence of a different user_type_id is for the database management purpose only.

Next Steps
  • To learn more about SQL Server User Defined Data Types, Rules and Defaults - click here.
  • How to view object's dependencies - Listing SQL Server Object Dependencies.
  • Start using UDTs to take benefit of their advantages. The disadvantages of using UDTs are mostly related to the management overhead that the UDTs may cause (someone has to manage the UDT list and their usage in the tables).


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Michelle Gutzait Michelle Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, June 15, 2009 - 1:35:52 PM - salvationishere Back To Top (3554)

Hi,

I am trying to run a SQL stored proc from Visual Studio 2008 which takes a table-valued UDT parameter as an input.  I added this parameter in Visual Studio 2008 but when I run this program it gets an "ArgumentException - Specified type isn't registered on target server."
So I googled this problem and think I need to create a new class in Visual Studio 2008 matching the properties of this table type from SQL Server 2008.  But I can't find any examples on the internet involving *table* UDT's.  And I tried all of the scalar UDT examples, but I wasn't sure how to modify these to make it table-based. 
I also read I may need to create an assembly, although I don't know if this is required only for importing a type into SQL Server 2008 or if it can also be used to import a type into Visual Studio.  BTW, I do not see any types listed from the Server Explorer in VS 2008, although I do see the database and its SP's.  I tried refreshing the database, but the types were still not showing.
What I need to do is simple.  I have a table UDT like so:

CREATE TYPE [dbo].[parseInputFile] AS TABLE(
[NumCols] [int] IDENTITY(1,1) NOT NULL,
[strRow] [varchar](500) NOT NULL,
PRIMARY KEY CLUSTERED

And the code snippet from my C# code is:

        public static int AppendData(string[ myStringArray)
        {
                SqlConnection conn = new SqlConnection(connString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "dbo.uspAppendTableFromInput";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                SqlParameter param = cmd.Parameters.Add("@InputFileParam", SqlDbType.Udt);
                param.UdtTypeName = "AdventureWorks.dbo.parseInputFile";
                param.Value = myStringArray;
                conn.Open();
                RowsAffected = cmd.ExecuteNonQuery();

RowsAffected show zero rows affected after running this.  What am I missing?  Can u point me to a website demonstrating a simple example of this.  Or else give me a tip?  I have also tried 'param.UdtTypeName="parseInputFile";' for the above snippet, but this returned the same error.  BTW, "myStringArray" is essentially a table.  It is a series of strings.  e.g. myStringArray[0] = "Hello|Bob|How|Do", myStringArray[1] = "I|Am|Fine|And", etc.

Monday, April 20, 2009 - 1:59:47 AM - Peso Back To Top (3203)

Why would there be a discrepancy? It's the Algebrizers work to resolve all object names to object ids.
That is done in a step before executing the execution plan.

Monday, April 20, 2009 - 12:44:24 AM - david.poole Back To Top (3202)

I would not expect any significant difference in the examples given but if you bind defaults and rules to the UDTs you might experience different results.

 From my own tests http://www.sqlservercentral.com/articles/Advanced/3104/ it appears that SQL Server uses UDTs as templates to stamp out the actual true column types and the syscolumns table will have a cdefault setting if the UDT was bound to a default.

 CLR UDT types will definitely show a performance drop but then again, you wouldn't simply define a CLR UDT for something that existed as a standard type anyway!















get free sql tips
agree to terms