Creating a SQL Server UDDT for an IP Address?

I would like to create a user-defined data type called IPv4 to store IP addresses. It seems straightforward enough in concept but the implementation is hard to figure out.

I tried:

create type IPv4 from varchar(15);  go    create rule IPv4_Rule  as           (CONVERT(tinyint, PARSENAME(@Address, 4)) > -1)      and (CONVERT(tinyint, PARSENAME(@Address, 3)) > -1)      and (CONVERT(tinyint, PARSENAME(@Address, 2)) > -1)      and (CONVERT(tinyint, PARSENAME(@Address, 1)) > -1)  go    exec sp_bindrule 'IPv4_Rule', 'IPv4'  go  

But this doesn't work because illegal addresses throw an exception and the rule does not apply. This is unfortunate because testing for an exception is the easiest way to verify that a value is legal.

I can use very similar code in a stored procedure try/catch block which does work but rules can't reference other database objects.

A regex would be great but SQL Server doesn't support them.

Finally, MS says
https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sql-server-ver15

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE. For more information, see Unique Constraints and Check Constraints.

I don't believe a check constraint can be bound to a type.

All of this is bringing me to the believe that UDDTs are an under-developed or crippled feature which are useless in all but the most trivial cases. If so, it is too bad because it seems like a feature with a lot of promise.

Note, I considered using a CLR type but am concerned about it being slow for real-time queries.

Am I missing something? Is there a better approach.

https://stackoverflow.com/questions/66675449/creating-a-sql-server-uddt-for-an-ip-address March 17, 2021 at 10:47PM