User Defined Data Type

Transact-SQL
1

Hi,

When do I need to create SQL Server user defined data type?

2

You can create user defined data types when you want to have some specific characteristics for your data - see here for an example.

Most of the features you are trying to achieve can be achieved via other means e.g. check constraints, so I almost never create user defined data types. I sometimes do however, create user defined table types which are useful for passing data to stored procs. Others on the forum who have found good uses and rationale for using user-defined data types perhaps can give more insights.

3

I have managed to avoid them ... in over 20 years of using MS SQL Server ....

I have created one DEFAULT which has been copied around between databases that I have built over the years, it returns the @@servername. From memory it could only be used as a Default, for a column, if it was first created as a DEFAULT object. It's probably possible to use @@servername direct these days, without the hassle of creating the Default object.

CREATE DEFAULT [dbo].[My_serverName] 
AS
-- @@servername can only be used as a default if it is made into a "Default Object" first
@@servername
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[MyDef_serverName]',
                       @objname=N'[dbo].[MyTable].[MyServer]',
                       @futureonly='futureonly'
GO
4

You need them when you create a table type so that, for example, you can pass a table into a stored proc rather than just a simple variable.

They can be very useful to use if you have a critical data column that you want to keep consistent across the db. For example, say "customer_number" is defined as char(4). You could create a "customer_number_type" defined as char(4), then in the actual tables the type would be that instead of char(4) to insure consistency and to allow all uses of the column to be identified later.

1 Like