What is the most appropriate data type for storing an IP address in SQL server? [duplicate]

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).

I normally just use varchar(15) for IPv4 addresses - but sorting them is a pain unless you pad zeros.

I've also stored them as an INT in the past. System.Net.IPAddress has a GetAddressBytes method that will return the IP address as an array of the 4 bytes that represent the IP address. You can use the following C# code to convert an IPAddress to an int...

var ipAsInt = BitConverter.ToInt32(ip.GetAddressBytes(), 0);

I had used that because I had to do a lot of searching for dupe addresses, and wanted the indexes to be as small & quick as possible. Then to pull the address back out of the int and into an IPAddress object in .net, use the GetBytes method on BitConverter to get the int as a byte array. Pass that byte array to the constructor for IPAddress that takes a byte array, and you end back up with the IPAddress that you started with.

var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));

Regarding this comment in the accepted answer

sorting them is a pain unless you pad zeros.

Here's a trick for SQL Server 2008 (From Itzik Ben-Gan in this book)

with ip_addresses as(SELECT '131.33.2.201' AS ip_address UNION ALLSELECT '2.12.4.4' AS ip_address UNION ALLSELECT '131.33.2.202' AS ip_address UNION ALLSELECT '2.12.4.169' AS ip_address UNION ALLSELECT '131.107.2.201' AS ip_address )select ip_addressfrom ip_addressesORDER  BY CAST('/' + ip_address + '/' AS hierarchyid)

Returns

ip_address-------------2.12.4.42.12.4.169131.33.2.201131.33.2.202131.107.2.201