none
What are User-Defined Table Types?

    Question

  • Under the Programmability folder there is a folder Types.  I am trying to understand what this object is used for and when.  I have tried reading several web pages but I still don't understand when I would use it.  Specifically User-Defined Table Types.

    Can anyone provide me with a simple reason when you would use one and when?


    lcerni

    Friday, July 19, 2013 2:27 PM

Answers

  • Hallo Icerni,

    basically a UDT (user definied data type) is nothing else as a "synonyme" for for a "real" data type.
    Let's say you are developing an application which has many attributes storing phone numbers.

    If you have multiple developers in your team each one will define "phone" with different data type and different data length. If you say that "phone" should always be a varchar(25) you can define such a UDT in the following way:

    -- Create the data type
    CREATE TYPE dbo.Phone FROM varchar (25) NULL
    
    -- Create table using the data type
    CREATE TABLE customer
    (
    	ID int NOT NULL	PRIMARY KEY CLUSTERED,
    	...
    	...
    	Phone dbo.Phone NOT NULL
    )
    GO
    

    As you can see from the script first a UDT has been defines as varchar(25) an IN the definition of the relation it has been used like a common data type.

    It looks pretty fine for having standards but ...
    - from my point of view - it's not recommended to handle standards with UDT because you can't change them "on the fly"...

    If you want to change the data type you can't do it with an "ALTER TYPE" but have to DROP it first and than recreate it. If you try it with the above example it wil fail because it's bound to the attribute [Phone] in the relation [dbo].[customer]

    -- This command wil fail!!!
    DROP TYPE [dbo].[Phone]
    GO
    
    -- would like to recreate it but have to drop the
    -- type from the attribute [phone] first
    CREATE TYPE [dbo].[Phone] FROM [varchar](30) NULL
    GO

    Msg will be:

    Meldung 3732, Ebene 16, Status 1, Zeile 2
    Cannot drop type 'dbo.Phone' because it is being referenced by object 'customer'.
    There may be other objects that reference this type.

    As you can see from the above message you have to drop the UDT first from the attribute.

    -- First alter the attribute itself
    ALTER TABLE dbo.customer
    ALTER COLUMN [Phone] varchar(25) NOT NULL;
    GO
    
    -- then drop the data type
    DROP TYPE [dbo].[Phone]
    GO
    
    -- and recreate it with the new settings
    CREATE TYPE [dbo].[Phone] FROM [varchar](30) NULL
    GO
    
    -- and add it to the attribute in the relation
    ALTER TABLE dbo.customer
    ALTER COLUMN [Phone] dbo.phone NOT NULL;

    Now it will work - imagine you have multiple relations which use the UDDT :)
    I had bad experiences with it and avoided the usage since than.

    A User Definied Table Type seems a pretty good approach if you are working quite often with table variables which have always the same structure. So defining each time in every procedure the same table structure may be boring and will lead to mistakes in the definition. Therefore the usage of UDTT is a pretty good choice.

    Let's say you need in many cases country codes for your queries in stored procedures and you decide to use table variables instead of temporary tables. Than you can use the following example as good demontration of usage of table types:

    CREATE TYPE CountryCodes AS TABLE
    (
    	ISO			char(3)		NOT NULL	PRIMARY KEY,
    	CountryName	char(30)	NOT NULL
    );
    
    
    DECLARE	@cc AS dbo.CountryCodes;
    INSERT INTO @cc (ISO, CountryName)
    VALUES
    ('049', 'Germany');
    
    SELECT * FROM @cc;

    Another GREAT benefit of table types is that you can use it as variable for a procedure :) The next example will show the demo how to use it:

    -- implementation of procedure
    CREATE PROC dbo.demo
    	@c AS CountryCodes READONLY
    AS
    	SET NOCOUNT ON
    	SELECT * FROM @c;
    	SET NOCOUNT OFF;
    GO

    Unfortunately table variables have to be READONLY as variable for procedures. Now take my previous example. Instead of using my "own" SELECT i call the proc:

    DECLARE	@cc AS dbo.CountryCodes;
    INSERT INTO @cc (ISO, CountryName)
    VALUES
    ('049', 'Germany');
    
    EXEC dbo.demo @cc;

    I know - really simple examples but they may give you a good overview of the "user data types" :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    • Marked as answer by lcerni Friday, July 19, 2013 4:25 PM
    Friday, July 19, 2013 2:56 PM
  • Probably the most common use is a parameter to stored procedures and functions.  If you want to pass a table as a parameter to a stored procedure or a function, you must do that as a table type.  An example of doing this can be found at http://msdn.microsoft.com/en-us/library/bb510489.aspx.

    Tom

    • Proposed as answer by Naomi NModerator Friday, July 19, 2013 3:52 PM
    • Marked as answer by lcerni Friday, July 19, 2013 4:25 PM
    Friday, July 19, 2013 3:05 PM

All replies

  • Hallo Icerni,

    basically a UDT (user definied data type) is nothing else as a "synonyme" for for a "real" data type.
    Let's say you are developing an application which has many attributes storing phone numbers.

    If you have multiple developers in your team each one will define "phone" with different data type and different data length. If you say that "phone" should always be a varchar(25) you can define such a UDT in the following way:

    -- Create the data type
    CREATE TYPE dbo.Phone FROM varchar (25) NULL
    
    -- Create table using the data type
    CREATE TABLE customer
    (
    	ID int NOT NULL	PRIMARY KEY CLUSTERED,
    	...
    	...
    	Phone dbo.Phone NOT NULL
    )
    GO
    

    As you can see from the script first a UDT has been defines as varchar(25) an IN the definition of the relation it has been used like a common data type.

    It looks pretty fine for having standards but ...
    - from my point of view - it's not recommended to handle standards with UDT because you can't change them "on the fly"...

    If you want to change the data type you can't do it with an "ALTER TYPE" but have to DROP it first and than recreate it. If you try it with the above example it wil fail because it's bound to the attribute [Phone] in the relation [dbo].[customer]

    -- This command wil fail!!!
    DROP TYPE [dbo].[Phone]
    GO
    
    -- would like to recreate it but have to drop the
    -- type from the attribute [phone] first
    CREATE TYPE [dbo].[Phone] FROM [varchar](30) NULL
    GO

    Msg will be:

    Meldung 3732, Ebene 16, Status 1, Zeile 2
    Cannot drop type 'dbo.Phone' because it is being referenced by object 'customer'.
    There may be other objects that reference this type.

    As you can see from the above message you have to drop the UDT first from the attribute.

    -- First alter the attribute itself
    ALTER TABLE dbo.customer
    ALTER COLUMN [Phone] varchar(25) NOT NULL;
    GO
    
    -- then drop the data type
    DROP TYPE [dbo].[Phone]
    GO
    
    -- and recreate it with the new settings
    CREATE TYPE [dbo].[Phone] FROM [varchar](30) NULL
    GO
    
    -- and add it to the attribute in the relation
    ALTER TABLE dbo.customer
    ALTER COLUMN [Phone] dbo.phone NOT NULL;

    Now it will work - imagine you have multiple relations which use the UDDT :)
    I had bad experiences with it and avoided the usage since than.

    A User Definied Table Type seems a pretty good approach if you are working quite often with table variables which have always the same structure. So defining each time in every procedure the same table structure may be boring and will lead to mistakes in the definition. Therefore the usage of UDTT is a pretty good choice.

    Let's say you need in many cases country codes for your queries in stored procedures and you decide to use table variables instead of temporary tables. Than you can use the following example as good demontration of usage of table types:

    CREATE TYPE CountryCodes AS TABLE
    (
    	ISO			char(3)		NOT NULL	PRIMARY KEY,
    	CountryName	char(30)	NOT NULL
    );
    
    
    DECLARE	@cc AS dbo.CountryCodes;
    INSERT INTO @cc (ISO, CountryName)
    VALUES
    ('049', 'Germany');
    
    SELECT * FROM @cc;

    Another GREAT benefit of table types is that you can use it as variable for a procedure :) The next example will show the demo how to use it:

    -- implementation of procedure
    CREATE PROC dbo.demo
    	@c AS CountryCodes READONLY
    AS
    	SET NOCOUNT ON
    	SELECT * FROM @c;
    	SET NOCOUNT OFF;
    GO

    Unfortunately table variables have to be READONLY as variable for procedures. Now take my previous example. Instead of using my "own" SELECT i call the proc:

    DECLARE	@cc AS dbo.CountryCodes;
    INSERT INTO @cc (ISO, CountryName)
    VALUES
    ('049', 'Germany');
    
    EXEC dbo.demo @cc;

    I know - really simple examples but they may give you a good overview of the "user data types" :)


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    • Marked as answer by lcerni Friday, July 19, 2013 4:25 PM
    Friday, July 19, 2013 2:56 PM
  • Probably the most common use is a parameter to stored procedures and functions.  If you want to pass a table as a parameter to a stored procedure or a function, you must do that as a table type.  An example of doing this can be found at http://msdn.microsoft.com/en-us/library/bb510489.aspx.

    Tom

    • Proposed as answer by Naomi NModerator Friday, July 19, 2013 3:52 PM
    • Marked as answer by lcerni Friday, July 19, 2013 4:25 PM
    Friday, July 19, 2013 3:05 PM