Rajendra Gupta
user-defined data type

An Overview of User-defined SQL Server Types

November 3, 2020 by

In this article, we will explore User-defined SQL Server Types and their usage.

An Overview of System Data Types

SQL Server supports various data types for storing different kinds of data. These data types store characters, numeric, decimal, string, binary, CLR and Spatial data types. Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.

System Data Types

Here the data types are in different groups.

  • Exact Numeric: bit, Tinyint, Smallint, Int, Bigint, Numeric, Decimal, SmallMoney, Money.
  • Approximate Numeric: float, real
  • Data and Time: DateTime, Smalldatatime, date, time, Datetimeoffset, Datetime2
  • Character Strings: char, varchar, text
  • Unicode Character strings: Nchar, Nvarchar, Ntext
  • Binary strings: binary, Varbinary, image
  • Other Data types: sql_variant, timestamp, Uniqueidentifier, XML
  • CLR data types: hierarchyid
  • Spatial data types: geometry, geography

You can explore my earlier article An Overview of SQL Server Datatypes for understanding these data types and their ranges.

User-defined SQL Server data types in SQL Server

If you expand the user-defined data types in the [AdventureWorks2019] database, you would see existing UDF’s in it. As the name suggests, these data types are created by the user.

User-defined SQL Server data types in SQL Server

So, can we create our data types in SQL Server? Let’s explore these UDTs in this article.

User-defined SQL Server Types

The UDT is similar to an alias data type, and it uses the existing system data types in SQL Server or Azure SQL database. SQL Server uses the Microsoft.Net framework. Common language runtime to define and implement the user-defined types.

Suppose your multiple tables use the email address column. The different developers might set a different value for Nvarchar() data type. In this case, you can define a user-defined type, and you can use these types like a regular data type.

SQL Server supports two kinds of user-defined types.

  • User-defined data type
  • User-defined table type

User-defined data types (UDDT) in SQL Server Types

A user-defined data type uses the existing data types with a set of constraints or rules. To create a UDDT, right-click on the user-defined data type folder and New user-defined data type. On this page, you can see the following inputs.

  • Schema: By default, it uses the dbo schema. We can use any existing schema here
  • Name: Give a customized name for the data type. Here, we give it a name as [PhoneNumber]
  • Data type: Select the existing system data type to map with the user-defined data type. You can select the appropriate data type from the drop-down
  • Length: Specify the length in case of a variable data type
  • Allow Nulls: Put a check if you want to allow the NULL values
  • Storage: It shows the bytes it consumes

User-defined SQL Server Types

Now, generate the script to be familiar with its equivalent T-SQL. It uses the CREATE TYPE statement, as shown below.

Create the user-defined data type and refresh the object explorer. It shows you on the list, as shown below.

refresh the object explorer

Now, you can use this user-defined data type similar to a regular data type.

user-defined data type

In the [PhoneNumber] user-defined data type, we did not allow NULL values; therefore, if you try to insert a NULL, it gives you the following error.

NULL values

You can use this data type multiple times in any SQL tables, procedures, functions. For example, let’s create another [CustomerInfo] table using the below script.

Now, suppose you got a requirement to increase the variable-length for data type used for both [EmpContactNumber] and [CustContactNumber] columns.

In this case, we cannot alter an existing user-defined data type. We need to drop and recreate the user-define data type, but it should have any reference objects.

variable-length data type

You can follow two approaches.

  • You can either remove object dependencies, recreate user-defined data type
  • Alternatively, create a new user-defined data type and alter the table to use the new UDDT

Use binding for rules and defaults in user-defined SQL Server Types

We can bind the rules and default values in the user-defined data types as well. Suppose we want to have a contact number with the following conditions:

  • The phone number should have country codes
  • We require the phone number format similar to +91- 1234567890
  • Its length can be a maximum of 14 bytes

In the below query, we do the following things:

  • If any user does not insert any contact number, it uses a default Unknown value
  • It creates a rule to define the maximum length and symbols (+,-) at specific locations

Now, create a new user-defined data type and map the existing default and rule, as shown below.

New user-defined data type

In the generated script from SSMS, we can see that it uses sys.sp_binddefault and sys.sp_bindrule stored procedures to bind these default and rules with user-defined data types.

Once we have created the new user-defined data type, create a test table and insert the sample records. It should raise an error message if we try to insert any value that violates the rules definition.

Initial two insert statements work fine because it obeys the existing rule. The last two statements give an error due to the violation of the rule.

insert statements errors

In the table, we see two records. The first record is with the phone number, and the second record is with the default value.

Check table values

User-defined table SQL Server Types

SQL Server allows a particular data type [table] to store a data set. In this data type, we specify the data types and their properties similar to a table.

In the below user-defined table type, we defined two columns [ProductName] and [Cost] with their corresponding data types. Table-valued parameters provide flexibility and better performance than the temporary table in some cases.SQL Server does not maintain the statistics for these table-valued parameters; therefore, you should take you should test your requirements and workload.

In the next step, we create a stored procedure that uses the user-defined table data type and selects the values from the variable.

It uses READONLY arguments for the table-valued parameters. We cannot perform the data manipulation operations – Update, delete, insert on the table-valued parameters in the stored procedure body.

Now, inserts few records in the table variable and executes the stored procedure.

It returns the values as shown below:

Create a Stored Procedure

Use default values in a user-defined table SQL Server Types

Create sample records

Creating a user-defined table SQL Server Types along with a primary key and clustered index

We can define the primary key, clustered and non-clustered index on a user-defined table type similar to a regular table.

In the below script, we define a table type [ProductTableType_1] and define the following things:

  • It has [ProductID], [ProductName] and [Cost] columns
  • It has a primary key on the [ProductID] column. By default, a primary key also creates a clustered index on the column
  • It has a non-clustered index on the [Cost] column

Once we create a user-defined table type, refresh the object explorer and view the newly created table type.

Types along with a primary key

Expand the user-defined table type [ProductTableType_1] and view the defined indexes (clustered, non-clustered) in the indexes subfolder.

View indexes in SSMS

Right-click on an index to open its properties. It shows the index name, index type along with its column properties.

Index properties

Conclusion

In this article, we explored the user-defined SQL Server Types and the benefits of using them in your SQL Server environment. You can explore these types and start using them, starting from the development environment.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views