By: Michelle Gutzait | Updated: 2009-04-08 | 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:
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:
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).
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-04-08