Datatype for storing ip address in SQL Server
The technically correct way to store IPv4 is binary(4), since that is what it actually is (no, not even an INT32/INT(4), the numeric textual form that we all know and love (255.255.255.255) being just the display conversion of its binary content).
If you do it this way, you will want functions to convert to and from the textual-display format:
Here's how to convert the textual display form to binary:
CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)ASBEGIN DECLARE @bin AS BINARY(4) SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) RETURN @binENDgoAnd here's how to convert the binary back to the textual display form:
CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)ASBEGIN DECLARE @str AS VARCHAR(15) SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) ); RETURN @strEND;goHere's a demo of how to use them:
SELECT dbo.fnBinaryIPv4('192.65.68.201')--should return 0xC04144C9goSELECT dbo.fnDisplayIPv4( 0xC04144C9 )-- should return '192.65.68.201'goFinally, when doing lookups and compares, always use the binary form if you want to be able to leverage your indexes.
UPDATE:
I wanted to add that one way to address the inherent performance problems of scalar UDFs in SQL Server, but still retain the code-reuse of a function is to use an iTVF (inline table-valued function) instead. Here's how the first function above (string to binary) can be re-written as an iTVF:
CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLEAS RETURN ( SELECT CAST( CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) AS BINARY(4)) As bin )goHere's it in the example:
SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')--should return 0xC04144C9goAnd here's how you would use it in an INSERT
INSERT INTo myIpTableSELECT {other_column_values,...}, (SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))
You can use varchar. The length of IPv4 is static, but that of IPv6 may be highly variable.
Unless you have a good reason to store it as binary, stick with a string (textual) type.
Here is some code to convert either IPV4 or IPv6 in varchar format to binary(16) and back. This is the smallest form I could think of. It should index well and provide a relatively easy way to filter on subnets. Requires SQL Server 2005 or later. Not sure it's totally bulletproof. Hope this helps.
-- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2')-- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2')-- SELECT dbo.fn_ConvertIpAddressToBinary('bogus')ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary( @ipAddress VARCHAR(39))RETURNS BINARY(16) ASBEGINDECLARE @bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2) , @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT , @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)SELECT @delim = '.' , @prevColIndex = 0 , @limit = 4 , @vbytes = 0x , @parts = 0 , @colIndex = CHARINDEX(@delim, @ipAddress)IF @colIndex = 0 BEGIN SELECT @delim = ':' , @limit = 8 , @colIndex = CHARINDEX(@delim, @ipAddress) WHILE @colIndex > 0 SELECT @parts = @parts + 1 , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) SET @colIndex = CHARINDEX(@delim, @ipAddress) IF @colIndex = 0 RETURN NULL ENDSET @ipAddress = @ipAddress + @delimWHILE @colIndex > 0 BEGIN SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1) IF @delim = ':' BEGIN SET @zone = RIGHT('0000' + @token, 4) SELECT @vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)') , @vbytes = @vbytes + @vbzone IF @token = '' WHILE @parts + 1 < @limit SELECT @vbytes = @vbytes + @vbzone , @parts = @parts + 1 END ELSE BEGIN SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2) SELECT @vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)') , @vbytes = @vbytes + @vbzone END SELECT @prevColIndex = @colIndex , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) END SET @bytes = CASE @delim WHEN ':' THEN @vbytes ELSE 0x000000000000000000000000 + @vbytes END RETURN @bytesEND-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]( @bytes BINARY(16))RETURNS VARCHAR(39) ASBEGINDECLARE @part VARBINARY(2) , @colIndex TINYINT , @ipAddress VARCHAR(39)SET @ipAddress = ''IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000 BEGIN SET @colIndex = 13 WHILE @colIndex <= 16 SELECT @part = SUBSTRING(@bytes, @colIndex, 1) , @ipAddress = @ipAddress + CAST(CAST(@part AS TINYINT) AS VARCHAR(3)) + CASE @colIndex WHEN 16 THEN '' ELSE '.' END , @colIndex = @colIndex + 1 IF @ipAddress = '0.0.0.1' SET @ipAddress = '::1' ENDELSE BEGIN SET @colIndex = 1 WHILE @colIndex <= 16 BEGIN SET @part = SUBSTRING(@bytes, @colIndex, 2) SELECT @ipAddress = @ipAddress + CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)') + CASE @colIndex WHEN 15 THEN '' ELSE ':' END , @colIndex = @colIndex + 2 END ENDRETURN @ipAddress END