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


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':


(numbering is right to left).