How should I store an IP address in SQL Server?

Question

question

Anthony Osborn asked Nov 13, '09

How should I store an IP address in SQL Server?

ip-address

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-13T12:53:26Z

Matt Whitfield answered Nov 13, '09

4 tinyint's gets my vote for an IPv4 address, and I would probably store an IPv6 address in a binary(16).

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-13T12:49:40Z

dave ballantyne answered Nov 13, '09

My personal preference would be 4 tinyint's.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-11-13T13:03:35Z

Blackhawk-17 answered Nov 13, '09

It depends...

How much manipulation are you doing on them?
What kind of querying will be done?
What indexing will be required?

Four tiny INTs, as has been suggested, is a good solution if you will be looking at particular octets and/or doing manipulating/grouping at that level.

A VARCHAR(15) if you are going for plain, basic storage of an IPv4 - including the periods. Best suited for simple inventory.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Nov 13, 2009 at 01:24 PM

I think the Ask team should code in that you get automatic rep for starting an answer with 'it depends' :)

1 ·

Blackhawk-17 commented · Nov 13, 2009 at 01:35 PM

It should also be a macro in the Edit dialogue ;)

0 ·

question