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);

declare @subnet_sk int;

-- 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
       ,null
       ,null)

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

-- ip 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')

-- 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')

-- 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')