What is the most appropriate data type for storing

Question

This question already has an answer here:

• Datatype for storing ip address in SQL Server 10 answers

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.

Answer 1

Quoting this:

Store IP addresses in a CHAR(15) column. Depending on how much data you're storing, this can be quite wasteful (why do we need to store the dots?). I

Answer 2

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

Answer 3

I'd probably go with a varchar or char.

And set the size to 15.