How should I store an IP address in SQL Server?

Many people 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 am currently dealing with a warehouse that is approaching 1 terabyte, and believe me, every place we can save a byte or two, we do. So, let's explore a few ways we can store an IP address: 

• One CHAR(15) column
  e.g.     127.0.0.1 
   
  Pros: 
      - programming is simple 
      - no conversion / extraction costs 
      - human readable 
   
  Cons: 
      - high storage costs 
      - difficult to search
   
  
• Four TINYINT columns
  e.g.     127     0     0     1 
   
  Pros: 
      - lower storage requirements than CHAR 
      - easy searchability 
      - can index on each octet 
   
  Cons: 
      - programming slightly more complex 
      - higher conversion costs 
      - more complicated schema
   
  
• One INT column
  e.g.     2130706433 
   
  Pros: 
      - lower storage requirements than CHAR 
      - save storage without complicating schema 
   
  Cons: 
      - programmability is more complex 
      - higher conversion costs 
      - human readability is at or at least near 0 ¹ 
      - in this implementation, requires SQL Server 2000 (UDF)

So, how do we implement each of these methods? 
 
Well, first, let's open Query Analyzer and make sure we're using a non-essential database: 
 

USE PUBS  GO

 
Now, let's create our first table, using a CHAR(15) column. If you're storing IP addresses in a single column, this is probably very similar to what you're doing: 
 

CREATE TABLE dbo.IPv0  (      ipAddress CHAR(15)  )  GO

 
Now, the second table, using four TINYINT columns: 
 

CREATE TABLE dbo.IPv1  (      octet1 TINYINT,      octet2 TINYINT,      octet3 TINYINT,      octet4 TINYINT  )  GO

 
And finally, the third table, using a single INT column: 
 

CREATE TABLE dbo.IPv2  (      ipAddress INT  )  GO

 
I built a stored procedure to help facilitate the populating of the four TINYINT solution: 
 

CREATE PROCEDURE dbo.storeIPoctetsv1      @ip CHAR(15)  AS  BEGIN      SET NOCOUNT ON      INSERT IPv1(octet1, octet2, octet3, octet4)          SELECT              CONVERT(TINYINT, PARSENAME(@ip, 4)),              CONVERT(TINYINT, PARSENAME(@ip, 3)),              CONVERT(TINYINT, PARSENAME(@ip, 2)),              CONVERT(TINYINT, PARSENAME(@ip, 1))  END

 
And a function that forms the basis of converting an IP address to an INT. (This function cheats a little by using PARSENAME instead of true character parsing, and one colleague even called it a hack -- elegant, but a hack nonetheless.) 
 

CREATE FUNCTION dbo.ipStringToInt  (      @ip CHAR(15)  )  RETURNS INT  AS  BEGIN      DECLARE @rv INT,          @o1 INT,          @o2 INT,          @o3 INT,          @o4 INT,          @base INT        SELECT          @o1 = CONVERT(INT, PARSENAME(@ip, 4)),          @o2 = CONVERT(INT, PARSENAME(@ip, 3)),          @o3 = CONVERT(INT, PARSENAME(@ip, 2)),          @o4 = CONVERT(INT, PARSENAME(@ip, 1))        IF (@o1 BETWEEN 0 AND 255)          AND (@o2 BETWEEN 0 AND 255)          AND (@o3 BETWEEN 0 AND 255)          AND (@o4 BETWEEN 0 AND 255)      BEGIN               SELECT @base = CASE              WHEN @o1 < 128 THEN                  (@o1 * 16777216)              ELSE                  -(256 - @o1) * 16777216              END                SET @rv = @base +               (@o2 * 65536) +               (@o3 * 256) +              (@o4)      END      ELSE          SET @rv = -1      RETURN @rv  END

 
Okay, now let's build a loop and insert some data. 
 

DECLARE @i INT  SET @i = 0  WHILE @i < 5000  BEGIN      INSERT IPv0 VALUES('204.71.34.21')      INSERT IPv0 VALUES('12.38.145.32')      INSERT IPv0 VALUES('127.0.0.1')        EXEC dbo.storeIPoctetsv1 @ip = '204.71.34.21'      EXEC dbo.storeIPoctetsv1 @ip = '12.38.145.32'      EXEC dbo.storeIPoctetsv1 @ip = '127.0.0.1'        INSERT IPv2 VALUES(dbo.ipStringToInt('204.71.34.21'))      INSERT IPv2 VALUES(dbo.ipStringToInt('12.38.145.32'))      INSERT IPv2 VALUES(dbo.ipStringToInt('127.0.0.1'))        SET @i = @i + 1  END

 
Okay, now that we have some data, let's see what our storage looks like. 
 

EXEC sp_spaceused 'IPv0'  EXEC sp_spaceused 'IPv1'  EXEC sp_spaceused 'IPv2'

 
Here are the results: 
 
 
As we may have expected, the storage requirements for the CHAR(15) version are nearly 150% of that for each of the other two methods. But what about retrieval costs? 
 
To facilitate converting the single INT back to a human-readable string, I created the following function: 
 

CREATE FUNCTION dbo.ipIntToString  (      @ip INT  )  RETURNS CHAR(15)  AS  BEGIN      DECLARE @o1 INT,          @o2 INT,          @o3 INT,          @o4 INT        IF ABS(@ip) > 2147483647          RETURN '255.255.255.255'        SET @o1 = @ip / 16777216        IF @o1 = 0          SELECT @o1 = 255, @ip = @ip + 16777216        ELSE IF @o1 < 0      BEGIN          IF @ip % 16777216 = 0              SET @o1 = @o1 + 256          ELSE          BEGIN              SET @o1 = @o1 + 255              IF @o1 = 128                  SET @ip = @ip + 2147483648              ELSE                  SET @ip = @ip + (16777216 * (256 - @o1))          END      END      ELSE      BEGIN          SET @ip = @ip - (16777216 * @o1)      END        SET @ip = @ip % 16777216      SET @o2 = @ip / 65536      SET @ip = @ip % 65536      SET @o3 = @ip / 256      SET @ip = @ip % 256      SET @o4 = @ip        RETURN          CONVERT(VARCHAR(4), @o1) + '.' +          CONVERT(VARCHAR(4), @o2) + '.' +          CONVERT(VARCHAR(4), @o3) + '.' +          CONVERT(VARCHAR(4), @o4)  END

 
(It's a little more complex than it's brother.) 
 
Now, if we run the following queries, and turn execution plan and client statistics on: 
 

SELECT TOP 50 IPAddress      FROM IPv0    SELECT TOP 50 IPAddress =       CONVERT(VARCHAR(4), octet1)+'.'+      CONVERT(VARCHAR(4), octet2)+'.'+      CONVERT(VARCHAR(4), octet3)+'.'+      CONVERT(VARCHAR(4), octet4)      FROM IPv1    SELECT TOP 50 IPAddress =       dbo.ipIntToString(ipAddress)      FROM IPv2

 
Believe it or not, the above three queries require about the same amount of time, work and I/O to return data. The latter two have an additional scalar computation but at an extremely low cost (almost 0), so they end up with about the same performance. 
 
So, the winner seems to be storing the octets in four TINYINT columns... while the storage costs are the same as a single INT, it has three big advantages: simpler programming, indexability of individual octets, and human readability. However, I'm not going to decide for you... hopefully I have given you enough ammo to do further research for your own environment. Please let us know if you have any information we could add to this article. 
 
Now, you'll probably want to clean up this stuff we created: 
 

DROP TABLE IPv0  DROP TABLE IPv1  DROP TABLE IPv2    DROP PROCEDURE dbo.storeIPoctetsv1    DROP FUNCTION dbo.ipStringToInt  DROP FUNCTION dbo.ipIntToString

 

1. If you know anyone who can look at an integer value like 2130706433, and tell me it represents 127.0.0.1, I'd like to shake his / her hand.