//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: How should I store an IP address in SQL Server?


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 1 
          - 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.

Related Articles

Are there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?

 

 


Created: 2/18/2003 | Last Updated: 2/22/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (178)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...