Datatype for storing ip address in SQL Server
What datatype should I choose for storing an IP Address in a SQL Server?
By selecting the right datatype would it be easy enough to filter by IP address then?
Solution 1:
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)
AS
BEGIN
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 @bin
END
go
And here's how to convert the binary back to the textual display form:
CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
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 @str
END;
go
Here's a demo of how to use them:
SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go
Finally, 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 TABLE
AS 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
)
go
Here's it in the example:
SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
And here's how you would use it in an INSERT
INSERT INTo myIpTable
SELECT {other_column_values,...},
(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))
Solution 2:
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.
Solution 3:
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) AS
BEGIN
DECLARE
@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
END
SET @ipAddress = @ipAddress + @delim
WHILE @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 @bytes
END
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)
ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]
(
@bytes BINARY(16)
)
RETURNS VARCHAR(39) AS
BEGIN
DECLARE
@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'
END
ELSE
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
END
RETURN @ipAddress
END
Solution 4:
As I want to handle both IPv4 and IPv6, I am using VARBINARY(16) and the following SQL CLR functions to convert the text IP address presentation to bytes and the reverse:
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
IPAddress IP;
if (IPAddress.TryParse(value.Value, out IP))
{
return new SqlBytes(IP.GetAddressBytes());
}
else
{
return new SqlBytes();
}
}
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
string output;
if (value.IsNull)
{
output = "";
}
else
{
IPAddress IP = new IPAddress(value.Value);
output = IP.ToString();
}
return new SqlString(output);
}
Solution 5:
For people using .NET can use IPAddress class to parse IPv4/IPv6 string and store it as a VARBINARY(16). Can use the same class to convert byte[] to string. If want to convert the VARBINARY in SQL:
--SELECT
-- dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4,
-- dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6
--ALTER
CREATE
FUNCTION dbo.varbinaryToIpString
(
@varbinaryValue VARBINARY(16)
)
RETURNS VARCHAR(39)
AS
BEGIN
IF @varbinaryValue IS NULL
RETURN NULL
IF DATALENGTH(@varbinaryValue) = 4
BEGIN
RETURN
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1)))
END
IF DATALENGTH(@varbinaryValue) = 16
BEGIN
RETURN
sys.fn_varbintohexsubstring(0, @varbinaryValue, 1, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 3, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 5, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 7, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 9, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2)
END
RETURN 'Invalid'
END