fun with IP Addresses in MSSQL

mssql_hosts_ipaddr.sql

CREATE FUNCTION dbo.udf_ipv4_format (@addr binary(16) )
RETURNS varchar(20) with SCHEMABINDING AS
BEGIN 
return IIF(SUBSTRING(@addr,0,8)=0,
			CONCAT( 
				CAST(SUBSTRING(@addr,13,1) AS INT),'.',
				CAST(SUBSTRING(@addr,14,1) AS INT),'.',
				CAST(SUBSTRING(@addr,15,1) AS INT),'.',
				CAST(SUBSTRING(@addr,16,1) AS INT)
			),NULL)
END;
GO

CREATE TABLE dbo.Host (
	HostID INT NOT NULL IDENTITY,
	HostName VARCHAR(512) NOT NULL,

	-- Internal IP's unformated IPv6
	IPaddress BINARY(16) NOT NULL 
		CONSTRAINT DF_Host_IPAddress DEFAULT (0x0),
	IPV4  AS dbo.udf_ipv4_format(IPaddress) PERSISTED,
		
	CONSTRAINT PK_Host PRIMARY KEY CLUSTERED(HostID),
	CONSTRAINT UQ_HostName UNIQUE (HostName),
	CONSTRAINT UQ_HostAddress UNIQUE (IPV4)
);
GO

insert into dbo.host(HostName, IPaddress) 
  values('my_host', 0x00000000000000000000FFFFFFFFFFFF),
  ('test_host', 0x00000000000000000000ffffac107601);
 GO
 
 select * from dbo.host;