Another approach is to store the IP in a binary(4) column. This, as well as
the integer approach, breaks the relational design rule regarding storing
non-atomic data. However, I don't think this anything to lose sleep over,
especially if you don't need to reference individual IP octets. You also
have to option to use computed columns to atomize data (or reconstitute if
you store individual octets). The example below illustrates this approach,
although I recommend formatting data in the client application.


CREATE TABLE dbo.IP_Addresses
(
IP_Address binary(4),
Octet1 AS CAST(SUBSTRING(IP_Address, 1, 1) AS tinyint),
Octet2 AS CAST(SUBSTRING(IP_Address, 2, 1) AS tinyint),
Octet3 AS CAST(SUBSTRING(IP_Address, 3, 1) AS tinyint),
Octet4 AS CAST(SUBSTRING(IP_Address, 4, 1) AS tinyint),
IP_Address_Formatted_Decimal AS
CAST(CAST(SUBSTRING(IP_Address, 1, 1) AS tinyint) AS varchar(3)) +
'.' +
CAST(CAST(SUBSTRING(IP_Address, 1, 2) AS tinyint) AS varchar(3)) +
'.' +
CAST(CAST(SUBSTRING(IP_Address, 1, 3) AS tinyint) AS varchar(3)) +
'.' +
CAST(CAST(SUBSTRING(IP_Address, 1, 4) AS tinyint) AS varchar(3))
)

INSERT INTO dbo.IP_Addresses VALUES(0x7f000001)
INSERT INTO dbo.IP_Addresses VALUES(0xc0a80101)

SELECT *
FROM dbo.IP_Addresses


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]