Sql – the most appropriate data type for storing an IP address in SQL server?

ip-addressipv4sqlsql-servertypes

What should be the most recommended datatype for storing an IPv4 address in SQL server?

Or maybe someone has already created a user SQL data-type (.Net assembly) for it?

I don't need sorting.

Best Solution

Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.

A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return '14':

SELECT PARSENAME('123.234.23.14', 1)

(numbering is right to left).