Subnet, DHCP Range and IP Address

drop table [subnet]
drop table [ip_address]
drop table [dhcp_range]

CREATE TABLE [subnet](
	[subnet_sk] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
	[ipv4_begin] [binary](4) NULL,
	[ipv4_end] [binary](4) NULL,
	[ipv6_begin] [binary](16) NULL,
	[ipv6_end] [binary](16) NULL);

CREATE TABLE [ip_address](
	[ip_address_sk] [int] IDENTITY(1,1) NOT NULL,
	[subnet_sk] [int] NOT NULL,
	[address] [varbinary](16) NOT NULL,
	[address_family] [varchar](16) NOT NULL);

CREATE TABLE [dhcp_range](
	[dhcp_range_sk] [int] IDENTITY(1,1) NOT NULL,
	[subnet_sk] [int] NOT NULL,
	[begin_address] [varbinary](16) NOT NULL,
	[end_address] [varbinary](16) NOT NULL,
	[address_family] [varchar](16) NOT NULL);

-- insert subnet `Nothing Assigned` that will have nothing associated w/ it
-- searching in `Nothing Assigned` should return 0xAC101129 as the min
-- the other address available would be all addresses up to 0xAC10112E.
insert [subnet]
values ('Nothing Assigned'
       ,0xAC101128
       ,0xAC10112F
       ,null
       ,null)

-- searching in `Chopped up` should return 0xC0A81B1F as the min.
-- multiple other addresses are available but I have not enumerated them here
insert [subnet]
values ('Chopped Up'
       ,0xC0A81B00
       ,0xC0A81BFF
       ,0xFC000000000000000000000000000000
       ,0xFC00000000000000FFFFFFFFFFFFFFFF)

declare @subnet_sk int

select @subnet_sk = s.subnet_sk
  from [subnet] s
 where s.name = 'Chopped Up'

-- IPv4 addresses for `Chopped Up`
insert [ip_address] values (@subnet_sk, 0xC0A81B01, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B0A, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B76, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B0D, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B0E, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B8E, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B91, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B0F, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B99, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B9B, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B10, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B13, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B15, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B16, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B17, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B18, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B19, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B1B, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B1C, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B1E, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B20, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B04, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B05, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B34, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B35, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B37, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B06, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B07, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B08, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B09, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B11, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B03, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B0C, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B4D, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B14, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B21, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B02, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B1D, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B0B, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B12, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81B1A, 'InterNetwork')

-- IPv6 addresses for chopped up
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000100, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000200, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000300, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000400, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000500, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000600, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000700, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000800, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC000000000000000000000000000900, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000CFFFFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FCFFFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFCFFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFCFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFCFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFCFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFCFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFCFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFCFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFCFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFCFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFCFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFFCFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFFFCFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFFFFCE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFFFFBE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFFFBFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFFBFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFFBFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFFBFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFFBFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFFBFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFFBFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFFBFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFFBFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFFBFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFFBFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FFBFFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000FBFFFFFFFFFFFFFE, 'InterNetworkV6')
insert [ip_address] values (@subnet_sk, 0xFC00000000000000BFFFFFFFFFFFFFFE, 'InterNetworkV6')

-- IPv4 dhcp ranges for `Chopped Up`
insert [dhcp_range] values (@subnet_sk, 0xC0A81B29, 0xC0A81B33, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B36, 0xC0A81B36, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B38, 0xC0A81B4C, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B4E, 0xC0A81B75, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B77, 0xC0A81B8D, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B8F, 0xC0A81B90, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B92, 0xC0A81B98, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B9A, 0xC0A81B9A, 'InterNetwork')
insert [dhcp_range] values (@subnet_sk, 0xC0A81B9C, 0xC0A81BF9, 'InterNetwork')

-- IPv6 dhcp ranges for `Chopped Up`
insert [dhcp_range] values (@subnet_sk, 0xFC000106600000000000000000000000, 0xFC0001066000000000000000FFFFFFFF, 'InterNetworkV6')
insert [dhcp_range] values (@subnet_sk, 0xFC000106670000000000000000000000, 0xFC0001066700000000000000FFFFFFFF, 'InterNetworkV6')
insert [dhcp_range] values (@subnet_sk, 0xFC000106678000000000000000000000, 0xFC0001066780000000000000FFFFFFFF, 'InterNetworkV6')
insert [dhcp_range] values (@subnet_sk, 0xFC000106679000000000000000000000, 0xFC0001066790000000000000FFFFFFFF, 'InterNetworkV6')

-- searching in `Mostly Contiguous` should return 0xC0A8160C as the min
-- the other address available would be 0xC0A816FE.
insert [subnet]
values ('Mostly Contiguous'
       ,0xC0A81600
       ,0xC0A816FF
       ,null
       ,null)

select @subnet_sk = s.subnet_sk
  from [subnet] s
 where s.name = 'Mostly Contiguous'

-- ip addresses for `Mostly Contiguous`
insert [ip_address] values (@subnet_sk, 0xC0A81601, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8160B, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8160D, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8160E, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8160F, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81610, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81616, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81612, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81613, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81602, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81614, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81617, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81619, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8161B, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8161C, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81603, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8161E, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8161F, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81604, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81607, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81609, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8161A, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81611, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81615, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8161D, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81606, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81608, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81618, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A81605, 'InterNetwork')
insert [ip_address] values (@subnet_sk, 0xC0A8160A, 'InterNetwork')

-- dhcp ranges for `Mostly Contiguous`
insert [dhcp_range] values (@subnet_sk, 0xC0A81620, 0xC0A816FD, 'InterNetwork')