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

I've had some success with making four smallint (or whatever smallish integer datatype you prefer) columns -- one for each octet. Then, you can make a view which smashes them together as a char string (for display) or then you can write simple operators to determine who all is in what subnet etc.

It is quite fast (provided you do proper indexing) and also allows for really easy querying (no string manipulation!).

Answer 2

Since an IP address has 32 bits in it, can you just use a LONG to store the numerical value?
It wouldn't be as space-wasteful as using VARCHAR, but then you'd have to decode it back to an IP before you use it, every time, and the delay and overhead that costs might not be worth it.

Answer 3

One of my favorite articles talks about why you shouldn't use regular expressions to parse IP addresses. Most of what they're talking about is really explaining why you should be very careful with textual representations of IP addresses. I suggest you read it before deciding what datatype to use in your database, and probably also for whatever handling your app will be doing (even though the article is written about Perl, it's useful for any language).

I think in the end a 32 bit datatype (or four 8-bit datatypes) would be the best choice.

Answer 4

IPV4? int? or tinyint x 4?

It really depends on whether it's just storage and retrieval or if it's going to be a ranged search criteria.

Answer 5

Don't forget about IPv6 - you need a lot more room if you need to store them - 128bits compares to IPv4's 32.

I'd go for bigint, though you will need some helper code to translate to human friendly versions.

Answer 6

The most appropriate data type for storing an IPv4 address in an MSSQL database, is an int. The only fiddly bit is converting it back to the dotted notation for display/sorting, hence I recommend you create a view that automates this for you.