InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 15:47:43 GMT20http://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Tom.Thomson (11/3/2009)[/b][hr]Your integer representation flips the top bit, so it isn't a natural representation, and I already had posted a suggested mod to your transformation which delivers an integer without flipping the top bit, which was the natural representation I was advocating.[/quote]The problem is that you cannot represent the IP address in its natural representation in both integer form and binary form at the same time using the int datatype. I believe that we can both agree that 0.0.0.0 should be 0x00000000 and 0 but since we can't store 255.255.255.255 (0xFFFFFFFF) as an unmodified int without using the bigint datatype, we have no choice but to finish with bitshifting and playing with the sign bit to make our value fit within SQL Server's signed int.So the question is what's more important? Casting compatibility or integer ordering? If being able to cast to the binary form with no additional manipulation is better for you, then hands down, your variation is more faithful. However, your variation comes with its own setback that my variation does not incur. If you were to translate from 0.0.0.0 to 255.255.255.255 using your method, you'd find that you would not be able to sort your field without casting it to binary first as your translation results in the lowest integer value starting in the middle of the IP range. In my variation, that's not the cast as 0.0.0.0 yields the lowest integer and 255.255.255.255 yields the highest.To illustrate, here is the comparison. IP1 is my translation method with it's binary casted result, IP2 is yours.[code]IP IP1 IP2 --------------- ----------- ---------- ----------- ----------0.0.0.0 -2147483648 0x80000000 0 0x000000001.0.0.0 -2130706432 0x81000000 16777216 0x010000002.0.0.0 -2113929216 0x82000000 33554432 0x020000003.0.0.0 -2097152000 0x83000000 50331648 0x030000004.0.0.0 -2080374784 0x84000000 67108864 0x040000005.0.0.0 -2063597568 0x85000000 83886080 0x050000006.0.0.0 -2046820352 0x86000000 100663296 0x060000007.0.0.0 -2030043136 0x87000000 117440512 0x070000008.0.0.0 -2013265920 0x88000000 134217728 0x080000009.0.0.0 -1996488704 0x89000000 150994944 0x0900000010.0.0.0 -1979711488 0x8A000000 167772160 0x0A00000011.0.0.0 -1962934272 0x8B000000 184549376 0x0B00000012.0.0.0 -1946157056 0x8C000000 201326592 0x0C00000013.0.0.0 -1929379840 0x8D000000 218103808 0x0D00000014.0.0.0 -1912602624 0x8E000000 234881024 0x0E00000015.0.0.0 -1895825408 0x8F000000 251658240 0x0F00000016.0.0.0 -1879048192 0x90000000 268435456 0x1000000017.0.0.0 -1862270976 0x91000000 285212672 0x1100000018.0.0.0 -1845493760 0x92000000 301989888 0x1200000019.0.0.0 -1828716544 0x93000000 318767104 0x1300000020.0.0.0 -1811939328 0x94000000 335544320 0x1400000021.0.0.0 -1795162112 0x95000000 352321536 0x1500000022.0.0.0 -1778384896 0x96000000 369098752 0x1600000023.0.0.0 -1761607680 0x97000000 385875968 0x1700000024.0.0.0 -1744830464 0x98000000 402653184 0x1800000025.0.0.0 -1728053248 0x99000000 419430400 0x1900000026.0.0.0 -1711276032 0x9A000000 436207616 0x1A00000027.0.0.0 -1694498816 0x9B000000 452984832 0x1B00000028.0.0.0 -1677721600 0x9C000000 469762048 0x1C00000029.0.0.0 -1660944384 0x9D000000 486539264 0x1D00000030.0.0.0 -1644167168 0x9E000000 503316480 0x1E00000031.0.0.0 -1627389952 0x9F000000 520093696 0x1F00000032.0.0.0 -1610612736 0xA0000000 536870912 0x2000000033.0.0.0 -1593835520 0xA1000000 553648128 0x2100000034.0.0.0 -1577058304 0xA2000000 570425344 0x2200000035.0.0.0 -1560281088 0xA3000000 587202560 0x2300000036.0.0.0 -1543503872 0xA4000000 603979776 0x2400000037.0.0.0 -1526726656 0xA5000000 620756992 0x2500000038.0.0.0 -1509949440 0xA6000000 637534208 0x2600000039.0.0.0 -1493172224 0xA7000000 654311424 0x2700000040.0.0.0 -1476395008 0xA8000000 671088640 0x2800000041.0.0.0 -1459617792 0xA9000000 687865856 0x2900000042.0.0.0 -1442840576 0xAA000000 704643072 0x2A00000043.0.0.0 -1426063360 0xAB000000 721420288 0x2B00000044.0.0.0 -1409286144 0xAC000000 738197504 0x2C00000045.0.0.0 -1392508928 0xAD000000 754974720 0x2D00000046.0.0.0 -1375731712 0xAE000000 771751936 0x2E00000047.0.0.0 -1358954496 0xAF000000 788529152 0x2F00000048.0.0.0 -1342177280 0xB0000000 805306368 0x3000000049.0.0.0 -1325400064 0xB1000000 822083584 0x3100000050.0.0.0 -1308622848 0xB2000000 838860800 0x3200000051.0.0.0 -1291845632 0xB3000000 855638016 0x3300000052.0.0.0 -1275068416 0xB4000000 872415232 0x3400000053.0.0.0 -1258291200 0xB5000000 889192448 0x3500000054.0.0.0 -1241513984 0xB6000000 905969664 0x3600000055.0.0.0 -1224736768 0xB7000000 922746880 0x3700000056.0.0.0 -1207959552 0xB8000000 939524096 0x3800000057.0.0.0 -1191182336 0xB9000000 956301312 0x3900000058.0.0.0 -1174405120 0xBA000000 973078528 0x3A00000059.0.0.0 -1157627904 0xBB000000 989855744 0x3B00000060.0.0.0 -1140850688 0xBC000000 1006632960 0x3C00000061.0.0.0 -1124073472 0xBD000000 1023410176 0x3D00000062.0.0.0 -1107296256 0xBE000000 1040187392 0x3E00000063.0.0.0 -1090519040 0xBF000000 1056964608 0x3F00000064.0.0.0 -1073741824 0xC0000000 1073741824 0x4000000065.0.0.0 -1056964608 0xC1000000 1090519040 0x4100000066.0.0.0 -1040187392 0xC2000000 1107296256 0x4200000067.0.0.0 -1023410176 0xC3000000 1124073472 0x4300000068.0.0.0 -1006632960 0xC4000000 1140850688 0x4400000069.0.0.0 -989855744 0xC5000000 1157627904 0x4500000070.0.0.0 -973078528 0xC6000000 1174405120 0x4600000071.0.0.0 -956301312 0xC7000000 1191182336 0x4700000072.0.0.0 -939524096 0xC8000000 1207959552 0x4800000073.0.0.0 -922746880 0xC9000000 1224736768 0x4900000074.0.0.0 -905969664 0xCA000000 1241513984 0x4A00000075.0.0.0 -889192448 0xCB000000 1258291200 0x4B00000076.0.0.0 -872415232 0xCC000000 1275068416 0x4C00000077.0.0.0 -855638016 0xCD000000 1291845632 0x4D00000078.0.0.0 -838860800 0xCE000000 1308622848 0x4E00000079.0.0.0 -822083584 0xCF000000 1325400064 0x4F00000080.0.0.0 -805306368 0xD0000000 1342177280 0x5000000081.0.0.0 -788529152 0xD1000000 1358954496 0x5100000082.0.0.0 -771751936 0xD2000000 1375731712 0x5200000083.0.0.0 -754974720 0xD3000000 1392508928 0x5300000084.0.0.0 -738197504 0xD4000000 1409286144 0x5400000085.0.0.0 -721420288 0xD5000000 1426063360 0x5500000086.0.0.0 -704643072 0xD6000000 1442840576 0x5600000087.0.0.0 -687865856 0xD7000000 1459617792 0x5700000088.0.0.0 -671088640 0xD8000000 1476395008 0x5800000089.0.0.0 -654311424 0xD9000000 1493172224 0x5900000090.0.0.0 -637534208 0xDA000000 1509949440 0x5A00000091.0.0.0 -620756992 0xDB000000 1526726656 0x5B00000092.0.0.0 -603979776 0xDC000000 1543503872 0x5C00000093.0.0.0 -587202560 0xDD000000 1560281088 0x5D00000094.0.0.0 -570425344 0xDE000000 1577058304 0x5E00000095.0.0.0 -553648128 0xDF000000 1593835520 0x5F00000096.0.0.0 -536870912 0xE0000000 1610612736 0x6000000097.0.0.0 -520093696 0xE1000000 1627389952 0x6100000098.0.0.0 -503316480 0xE2000000 1644167168 0x6200000099.0.0.0 -486539264 0xE3000000 1660944384 0x63000000100.0.0.0 -469762048 0xE4000000 1677721600 0x64000000101.0.0.0 -452984832 0xE5000000 1694498816 0x65000000102.0.0.0 -436207616 0xE6000000 1711276032 0x66000000103.0.0.0 -419430400 0xE7000000 1728053248 0x67000000104.0.0.0 -402653184 0xE8000000 1744830464 0x68000000105.0.0.0 -385875968 0xE9000000 1761607680 0x69000000106.0.0.0 -369098752 0xEA000000 1778384896 0x6A000000107.0.0.0 -352321536 0xEB000000 1795162112 0x6B000000108.0.0.0 -335544320 0xEC000000 1811939328 0x6C000000109.0.0.0 -318767104 0xED000000 1828716544 0x6D000000110.0.0.0 -301989888 0xEE000000 1845493760 0x6E000000111.0.0.0 -285212672 0xEF000000 1862270976 0x6F000000112.0.0.0 -268435456 0xF0000000 1879048192 0x70000000113.0.0.0 -251658240 0xF1000000 1895825408 0x71000000114.0.0.0 -234881024 0xF2000000 1912602624 0x72000000115.0.0.0 -218103808 0xF3000000 1929379840 0x73000000116.0.0.0 -201326592 0xF4000000 1946157056 0x74000000117.0.0.0 -184549376 0xF5000000 1962934272 0x75000000118.0.0.0 -167772160 0xF6000000 1979711488 0x76000000119.0.0.0 -150994944 0xF7000000 1996488704 0x77000000120.0.0.0 -134217728 0xF8000000 2013265920 0x78000000121.0.0.0 -117440512 0xF9000000 2030043136 0x79000000122.0.0.0 -100663296 0xFA000000 2046820352 0x7A000000123.0.0.0 -83886080 0xFB000000 2063597568 0x7B000000124.0.0.0 -67108864 0xFC000000 2080374784 0x7C000000125.0.0.0 -50331648 0xFD000000 2097152000 0x7D000000126.0.0.0 -33554432 0xFE000000 2113929216 0x7E000000127.0.0.0 -16777216 0xFF000000 2130706432 0x7F000000128.0.0.0 0 0x00000000 -2147483648 0x80000000129.0.0.0 16777216 0x01000000 -2130706432 0x81000000130.0.0.0 33554432 0x02000000 -2113929216 0x82000000131.0.0.0 50331648 0x03000000 -2097152000 0x83000000132.0.0.0 67108864 0x04000000 -2080374784 0x84000000133.0.0.0 83886080 0x05000000 -2063597568 0x85000000134.0.0.0 100663296 0x06000000 -2046820352 0x86000000135.0.0.0 117440512 0x07000000 -2030043136 0x87000000136.0.0.0 134217728 0x08000000 -2013265920 0x88000000137.0.0.0 150994944 0x09000000 -1996488704 0x89000000138.0.0.0 167772160 0x0A000000 -1979711488 0x8A000000139.0.0.0 184549376 0x0B000000 -1962934272 0x8B000000140.0.0.0 201326592 0x0C000000 -1946157056 0x8C000000141.0.0.0 218103808 0x0D000000 -1929379840 0x8D000000142.0.0.0 234881024 0x0E000000 -1912602624 0x8E000000143.0.0.0 251658240 0x0F000000 -1895825408 0x8F000000144.0.0.0 268435456 0x10000000 -1879048192 0x90000000145.0.0.0 285212672 0x11000000 -1862270976 0x91000000146.0.0.0 301989888 0x12000000 -1845493760 0x92000000147.0.0.0 318767104 0x13000000 -1828716544 0x93000000148.0.0.0 335544320 0x14000000 -1811939328 0x94000000149.0.0.0 352321536 0x15000000 -1795162112 0x95000000150.0.0.0 369098752 0x16000000 -1778384896 0x96000000151.0.0.0 385875968 0x17000000 -1761607680 0x97000000152.0.0.0 402653184 0x18000000 -1744830464 0x98000000153.0.0.0 419430400 0x19000000 -1728053248 0x99000000154.0.0.0 436207616 0x1A000000 -1711276032 0x9A000000155.0.0.0 452984832 0x1B000000 -1694498816 0x9B000000156.0.0.0 469762048 0x1C000000 -1677721600 0x9C000000157.0.0.0 486539264 0x1D000000 -1660944384 0x9D000000158.0.0.0 503316480 0x1E000000 -1644167168 0x9E000000159.0.0.0 520093696 0x1F000000 -1627389952 0x9F000000160.0.0.0 536870912 0x20000000 -1610612736 0xA0000000161.0.0.0 553648128 0x21000000 -1593835520 0xA1000000162.0.0.0 570425344 0x22000000 -1577058304 0xA2000000163.0.0.0 587202560 0x23000000 -1560281088 0xA3000000164.0.0.0 603979776 0x24000000 -1543503872 0xA4000000165.0.0.0 620756992 0x25000000 -1526726656 0xA5000000166.0.0.0 637534208 0x26000000 -1509949440 0xA6000000167.0.0.0 654311424 0x27000000 -1493172224 0xA7000000168.0.0.0 671088640 0x28000000 -1476395008 0xA8000000169.0.0.0 687865856 0x29000000 -1459617792 0xA9000000170.0.0.0 704643072 0x2A000000 -1442840576 0xAA000000171.0.0.0 721420288 0x2B000000 -1426063360 0xAB000000172.0.0.0 738197504 0x2C000000 -1409286144 0xAC000000173.0.0.0 754974720 0x2D000000 -1392508928 0xAD000000174.0.0.0 771751936 0x2E000000 -1375731712 0xAE000000175.0.0.0 788529152 0x2F000000 -1358954496 0xAF000000176.0.0.0 805306368 0x30000000 -1342177280 0xB0000000177.0.0.0 822083584 0x31000000 -1325400064 0xB1000000178.0.0.0 838860800 0x32000000 -1308622848 0xB2000000179.0.0.0 855638016 0x33000000 -1291845632 0xB3000000180.0.0.0 872415232 0x34000000 -1275068416 0xB4000000181.0.0.0 889192448 0x35000000 -1258291200 0xB5000000182.0.0.0 905969664 0x36000000 -1241513984 0xB6000000183.0.0.0 922746880 0x37000000 -1224736768 0xB7000000184.0.0.0 939524096 0x38000000 -1207959552 0xB8000000185.0.0.0 956301312 0x39000000 -1191182336 0xB9000000186.0.0.0 973078528 0x3A000000 -1174405120 0xBA000000187.0.0.0 989855744 0x3B000000 -1157627904 0xBB000000188.0.0.0 1006632960 0x3C000000 -1140850688 0xBC000000189.0.0.0 1023410176 0x3D000000 -1124073472 0xBD000000190.0.0.0 1040187392 0x3E000000 -1107296256 0xBE000000191.0.0.0 1056964608 0x3F000000 -1090519040 0xBF000000192.0.0.0 1073741824 0x40000000 -1073741824 0xC0000000193.0.0.0 1090519040 0x41000000 -1056964608 0xC1000000194.0.0.0 1107296256 0x42000000 -1040187392 0xC2000000195.0.0.0 1124073472 0x43000000 -1023410176 0xC3000000196.0.0.0 1140850688 0x44000000 -1006632960 0xC4000000197.0.0.0 1157627904 0x45000000 -989855744 0xC5000000198.0.0.0 1174405120 0x46000000 -973078528 0xC6000000199.0.0.0 1191182336 0x47000000 -956301312 0xC7000000200.0.0.0 1207959552 0x48000000 -939524096 0xC8000000201.0.0.0 1224736768 0x49000000 -922746880 0xC9000000202.0.0.0 1241513984 0x4A000000 -905969664 0xCA000000203.0.0.0 1258291200 0x4B000000 -889192448 0xCB000000204.0.0.0 1275068416 0x4C000000 -872415232 0xCC000000205.0.0.0 1291845632 0x4D000000 -855638016 0xCD000000206.0.0.0 1308622848 0x4E000000 -838860800 0xCE000000207.0.0.0 1325400064 0x4F000000 -822083584 0xCF000000208.0.0.0 1342177280 0x50000000 -805306368 0xD0000000209.0.0.0 1358954496 0x51000000 -788529152 0xD1000000210.0.0.0 1375731712 0x52000000 -771751936 0xD2000000211.0.0.0 1392508928 0x53000000 -754974720 0xD3000000212.0.0.0 1409286144 0x54000000 -738197504 0xD4000000213.0.0.0 1426063360 0x55000000 -721420288 0xD5000000214.0.0.0 1442840576 0x56000000 -704643072 0xD6000000215.0.0.0 1459617792 0x57000000 -687865856 0xD7000000216.0.0.0 1476395008 0x58000000 -671088640 0xD8000000217.0.0.0 1493172224 0x59000000 -654311424 0xD9000000218.0.0.0 1509949440 0x5A000000 -637534208 0xDA000000219.0.0.0 1526726656 0x5B000000 -620756992 0xDB000000220.0.0.0 1543503872 0x5C000000 -603979776 0xDC000000221.0.0.0 1560281088 0x5D000000 -587202560 0xDD000000222.0.0.0 1577058304 0x5E000000 -570425344 0xDE000000223.0.0.0 1593835520 0x5F000000 -553648128 0xDF000000224.0.0.0 1610612736 0x60000000 -536870912 0xE0000000225.0.0.0 1627389952 0x61000000 -520093696 0xE1000000226.0.0.0 1644167168 0x62000000 -503316480 0xE2000000227.0.0.0 1660944384 0x63000000 -486539264 0xE3000000228.0.0.0 1677721600 0x64000000 -469762048 0xE4000000229.0.0.0 1694498816 0x65000000 -452984832 0xE5000000230.0.0.0 1711276032 0x66000000 -436207616 0xE6000000231.0.0.0 1728053248 0x67000000 -419430400 0xE7000000232.0.0.0 1744830464 0x68000000 -402653184 0xE8000000233.0.0.0 1761607680 0x69000000 -385875968 0xE9000000234.0.0.0 1778384896 0x6A000000 -369098752 0xEA000000235.0.0.0 1795162112 0x6B000000 -352321536 0xEB000000236.0.0.0 1811939328 0x6C000000 -335544320 0xEC000000237.0.0.0 1828716544 0x6D000000 -318767104 0xED000000238.0.0.0 1845493760 0x6E000000 -301989888 0xEE000000239.0.0.0 1862270976 0x6F000000 -285212672 0xEF000000240.0.0.0 1879048192 0x70000000 -268435456 0xF0000000241.0.0.0 1895825408 0x71000000 -251658240 0xF1000000242.0.0.0 1912602624 0x72000000 -234881024 0xF2000000243.0.0.0 1929379840 0x73000000 -218103808 0xF3000000244.0.0.0 1946157056 0x74000000 -201326592 0xF4000000245.0.0.0 1962934272 0x75000000 -184549376 0xF5000000246.0.0.0 1979711488 0x76000000 -167772160 0xF6000000247.0.0.0 1996488704 0x77000000 -150994944 0xF7000000248.0.0.0 2013265920 0x78000000 -134217728 0xF8000000249.0.0.0 2030043136 0x79000000 -117440512 0xF9000000250.0.0.0 2046820352 0x7A000000 -100663296 0xFA000000251.0.0.0 2063597568 0x7B000000 -83886080 0xFB000000252.0.0.0 2080374784 0x7C000000 -67108864 0xFC000000253.0.0.0 2097152000 0x7D000000 -50331648 0xFD000000254.0.0.0 2113929216 0x7E000000 -33554432 0xFE000000255.0.0.0 2130706432 0x7F000000 -16777216 0xFF000000[/code][quote] This is particularly important when using the transformation to convert a mask from dotted form to a bit pattern, because if your original transformation is used for the mask the test WHERE [IntegerAddress] & @IntegerMask = @IntegerNetworkwill sometimes give the wrong result: for example it will include 64.1.1.0 in the network <192.1.1.0 mask 255.255.255.255> because @IntegerMask will be positive instead of negative; and economy of code suggests that once you have the right transformation for the mask you may as well use it for all transformations from 3 dot format to int.[/quote]That's correct, which is why I didn't specify an integer mask as it simply wouldn't work.[quote]Also, I dislike that version of the test for a simple reason: it assumes that the subnet is always specified using the lowest address that it contains. But in practise how the subnet ios specified tends to vary according to where you are in the network - in a subnet with mask M, a machine with address A will tend to view the subnet as <A MASK M> rather than as <A&M MASK M>. That's why I would use a test which checks whether there is any bit position where the mask is 1 and the network and candidate addresses differ, if the subnet information was coming from the outside world as parameters to a stored proc. This could be expresssed (assuming that @IntegerMask doesn't have the top bit flipped) by WHERE [IntegerAddress] & @IntegerMask = @IntegerNetwork & @IntegerMaskas well as by the expression using XOR. Obviously when a subnet is stored in a table it makes a lot of sense to use its lowest address as the network address, but I would prefer not to impose that restriction at the API between the DB and the outside world.[/quote]Absolutely. The point I was trying to make, however, is that each operation you do compounds itself when multiplied over a large number of rows, which is often the case when it comes to IP addresses within a database. If you can do the same validation using two mathematical operations vs three mathematical operations "per row", the impact is still there, even if minimal. At the end of the day, the implementor must decide where to draw the line between the efficiency aquired and the flexibility needed.To further illustrate my point, the following tests show five different ways to test for IP addresses belonging to a network (including Clive Chinery's suggestion):Variables setup.[code]DECLARE @BinaryNetwork binary(4) SET @BinaryNetwork = [dbo].[ParseToBinary]('192.168.1.0')DECLARE @BinaryMask binary(4) SET @BinaryMask = [dbo].[ParseToBinary]('255.255.255.240')DECLARE @IntegerNetwork int SET @IntegerNetwork = [dbo].[ParseToInt]('192.168.1.0')DECLARE @StartIntegerAddress int SET @StartIntegerAddress = @IntegerNetwork + 1DECLARE @EndIntegerAddress int SET @EndIntegerAddress = (@IntegerNetwork | CAST(~ CAST(@BinaryMask AS int) AS binary(4))) - 1[/code]XOR using stored binary addresses.[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE ((@BinaryNetwork ^ CAST([BinaryAddress] AS int)) & @BinaryMask) = 0SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 125 ms, elapsed time = 117 ms.[/code]XOR using stored integer addresses.[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE ((@IntegerNetwork ^ [IntegerAddress]) & @BinaryMask) = 0SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 109 ms, elapsed time = 108 ms.[/code]AND using stored integer addresses.[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE [IntegerAddress] & @BinaryMask = @IntegerNetworkSET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 94 ms, elapsed time = 97 ms.[/code]BETWEEN using stored integer addresses.[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE [IntegerAddress] BETWEEN @StartIntegerAddress AND @EndIntegerAddressSET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 63 ms, elapsed time = 73 ms.[/code]Greater then, Lesser then comparison using integer addresses.[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE [IntegerAddress] >= @StartIntegerAddress AND [IntegerAddress] <= @EndIntegerAddressSET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 62 ms, elapsed time = 73 ms.[/code]Straight integer filtering is at a very clear advantage. I am undecided as to which method of integer translation is better (if there's such a thing as one being better then the other). Each of the variations offer advantages and disadvantages in equal proportions it seems.Tue, 03 Nov 2009 09:22:39 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxI agree that aggregate functions make little sense for IP Addresses. This is a very important discussion for developers such as myself as originating IP addresses need to be logged quickly and efficiently with regard to space.Tue, 03 Nov 2009 06:36:41 GMTClive Chineryhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Jean-Sebastien Carle (10/31/2009)[/b][hr]Generally speaking, IP addresses exist within a database as a result of logging. The logging will often cumulate over time to generate hundreds of thousands if not millions of rows. When operations are done over those rows, they usually revolve around verifying if an IP address already exists, verifying the number of times a specific IP address has been logged or finding logged entries that match either a list of addresses or networks. The efficiency of doing those operations is based on the performance achieved when SQL Server executes the mathematics, the aggregate functions, the joins and/or sorts required to do so.Binary does not perform as well as the integer although the difference may be negligable if either your dataset is small or your operations are simplistic.To illustrate, I used my test database. Within it I have two IP address tables. The first one ([dbo].[IPAddresses]) contains 100,000 random IP addresses in three forms varchar(15), int and binary. The second one ([dbo].[ManyIPAddresses]) contains 1,000,000 random IP addresses (10 copies of the [dbo].[IPAddresses] table).If we look at simply filtering 1,000,000 rows to find IP addresses from a certain subnet, here is the setup for our comparison:[code]DECLARE @BinaryNetwork binary(4) SET @BinaryNetwork = [dbo].[ParseToBinary]('192.168.1.0')DECLARE @BinaryMask binary(4) SET @BinaryMask = [dbo].[ParseToBinary]('255.255.255.0')DECLARE @IntegerNetwork int SET @IntegerNetwork = [dbo].[ParseToInt]('192.168.1.0')[/code][/quote][snip][quote]So as you can see, not only does binary have its mathematical limitations, it also has its performance limitations. That's not to mention the fact that certain operations simply cannot be done on binary fields. Attempting to perform an aggregate function like AVG on a binary field will yield the error "Operand data type binary is invalid for avg operator.". Although this is something that would never be performed on IP addresses, it is still to be noted. But I digress.With the obvious performance advantages offered by the integer datatype, I would not hesitate to store IP addresses in the same way as I described in my article. The cost of transformation at the time of insert is minimal at best. With the speed of computed columns, transformation from its stored integer form back to a human readable varchar(15) or a naturally represented binary(4) can be also be achieved at a minimal cost. So is storing an IP address in its natural representation more important then storing it for efficiency and performance? I don't believe so but perhaps that's a need you may have.[/quote]I am not a network programmer, so I'll get that out of the way up front. ;-)However, don't bitmasks always represent contiguous blocks of address space? Therefore, wouldn't you get better performance if you calculate the beginning and ending addresses in the range represented by the bitmask and then use BETWEEN rather than bitwise operators? This eliminates per-row implicit conversions within the query and it should be sargable. I'd be interested to see how it compares to the test results you posted.As for using AVG on IP addresses, I can't think of a case where that even makes sense. (Your comment indicates you agree.) As such, I don't see much point in noting it. In fact, it may be a VERY minor point, but I can see a case to be made for preferring binary(4) specifically because it doesn't even offer operations like AVG that have no meaning relevant to the data.AndrewTue, 03 Nov 2009 06:13:44 GMTAndrew in WVhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Jean-Sebastien Carle (10/31/2009)[/b][hr]<much snipped>[quote][b]Tom.Thomson (10/31/2009)[/b]It's easy enough - although I don't understand why you want me to use binary, since the code snippet in the comment that offended you is clearly based on it being int.[/quote]Because you yourself have repeated several times that the natural representation of an address is in its binary 32-bit unmodified form.<more snipped>[/quote]I think we have managed to get ourselves misunderstanding each-other quite a lot. When I said "the natural representation" I meant one that use the bit pattern that is the IP address - whether it's typed as binary(4) or as int doesn't come into it. That's why I didn't (as I pointed out) understand why you wanted me to provide the code for the binary(4) version. Your integer representation flips the top bit, so it isn't a natural representation, and I already had posted a suggested mod to your transformation which delivers an integer without flipping the top bit, which was the natural representation I was advocating. This is particularly important when using the transformation to convert a mask from dotted form to a bit pattern, because if your original transformation is used for the mask the test WHERE [IntegerAddress] & @IntegerMask = @IntegerNetworkwill sometimes give the wrong result: for example it will include 64.1.1.0 in the network <192.1.1.0 mask 255.255.255.255> because @IntegerMask will be positive instead of negative; and economy of code suggests that once you have the right transformation for the mask you may as well use it for all transformations from 3 dot format to int.Also, I dislike that version of the test for a simple reason: it assumes that the subnet is always specified using the lowest address that it contains. But in practise how the subnet ios specified tends to vary according to where you are in the network - in a subnet with mask M, a machine with address A will tend to view the subnet as <A MASK M> rather than as <A&M MASK M>. That's why I would use a test which checks whether there is any bit position where the mask is 1 and the network and candidate addresses differ, if the subnet information was coming from the outside world as parameters to a stored proc. This could be expresssed (assuming that @IntegerMask doesn't have the top bit flipped) by WHERE [IntegerAddress] & @IntegerMask = @IntegerNetwork & @IntegerMaskas well as by the expression using XOR. Obviously when a subnet is stored in a table it makes a lot of sense to use its lowest address as the network address, but I would prefer not to impose that restriction at the API between the DB and the outside world.I agree completely with you that efficiency matters. Bothe performance and storage have to be considered, and if one has an address table with various forms in computed columns and only one form in storage, the best form for storage will be integer because it comes top for both storage efficiency (best equal with a few other forms) and performance. But I still think that the integer that doesn't have the flipped top bit is a better representation than the one that does.When one is going for efficiency, one extra case that needs to be considered is the case where a table with a lot of addresses in it has to be filtered to get the rows in a particular subnet. I'm not sure that the optimiser will manage to use an index on the address if the column is subject to an & operation or an ^ operation in the filter; so I would probably want to convert the subnet to a pair of addresses, the lower and upper bound, for this case; then the index can certainly be used. Also one may want to discover whether two subnets and disjoint, or whether one is a subnet of the other. In these cases too it's useful to convert to lower bound - upper bound form. Should the two bounds be the standard representation of the subnet if it has to be held in the table, or should it be lower bound and mask If it's lower bound and upper bound, the mask can be held (if wanted) as a computed colum easily enough. If it's lower cound and mask, the upper bound can be a computed column. So I guess one would have to look at the performace and all operations and the frequency of each and cdecide accordingly - difficult in a world with incremental requirement discovery, incremental development, and incremental release.Tue, 03 Nov 2009 05:56:03 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxI'm not sure if I already said this in the thread, and it's a minor point, but I am not sure that int is the best thing about SQL Server generally - because when I performed an experiment on encoding char(6) alphanumeric strings (A-Z 0-9) - six bytes - into four bytes int in SQL Server 2000 as more economical indexed keys, the int representations seemed to perform worse as keys than the strings. I expect I have the details on file somewhere. That suggests that Microsoft worked hard on getting char key indexing to work really well. However, I may have overlooked other factors.On another occasion I found that there was not much performance difference between int, smallint, and tinyint, presumably because a modern server's processor does 32 bit or 64 bit computation anyway. And bit stunk (they may have improved it since), and has use limitations as mentioned, so in practical programming I usually put bit data in tinyint - I've also considered using a user-defined 'tinybit' data type which would just be tinyint with or without a constraint of being <= 1 or IN (0, 1). Having said that, I don't know what CAST costs here. Maybe nothing.One factor that my experiments probably excluded is hard disk access time, because my data probably ended up cached in RAM. Because of that, int ought to do better than char(6) in real applications, if - if - query performance is limited by disk speed and not by processor. In other words, if it isn't a fancy query that requires a lot of calculation per unit of data. But I've decided not to rely too much on what I think is going to work well in SQL Server.Tue, 03 Nov 2009 03:15:39 GMTrja.carnegiehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxThankyou (Ladies and) Gentlemen for this ongoing discussion. Recent items have shown me the need to make further comparions on what is the best way to store an IP Address.Mon, 02 Nov 2009 04:43:20 GMTClive Chineryhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Tom.Thomson (10/31/2009)[/b][hr]It seemed to me to be probable that you were a DBA - after all, most of the articles posted on SQL ServerCentral are written by DBAs, and you certainly appeared to give no consideration to using the natural representation ... if I gave the impression that I was suggesting any of that I'm very sorry, I certainly didn't intend to.[/quote]I'm not upset in any way, but there's a saying that I like to keep in the forefront of my mind. To assume makes an *** out of u and me.[quote]I did suggest that you were not a data comms programmer ...[/quote]If you mean on the embedded level, that is most certainly true. I do hold my own when it comes to networking nonetheless because of the large quantity of networks I have deployed and managed as well as the amount of network focused applications I have developed that involved either subnetting or packet filtering.[quote]... would know that the first RFC to specify a 32 bit internet address was RFC 760 of Jan 1980, rather than the more recent document that you referenced - and even that wasn't where it was first introduced ...[/quote]The reference to the RFC wasn't meant as a technical accuracy to the first mention as such and, if you notice my wording, I don't specify as much either. I was simply being a writer through that passage in an attempt to make the article seem less monotonous. Even the driest of us nerds can get bored of looking at flat code and table summaries.[quote]... have you written and/or debugged chunks of code to implement protocols in layer 4 (transport) and downward?[/quote]I cannot say that I have had the luxury to do so, no.[quote]... see snippet in the comment which has annoyed you ... the code snippet in the comment that offended ...[/quote]I am not quite sure how you infered that I was annoyed. I appologize if I led you to believe as such.[quote]it's pretty trivial to store the natural representation so the question to me was why on earth anyone would want to do anything else[/quote]Because storage is not the only thing to take into consideration. If, in your application or usage, the only requirement is to log and store IPv4 addresses, then I would perhaps even question if there was a need to store the data in SQL Server. Traditionally, SQL Server's role is not only for storage but also to act as a mechanism to interact, aggregate and analyse data as well.[quote]It's easy enough - although I don't understand why you want me to use binary, since the code snippet in the comment that offended you is clearly based on it being int.[/quote]Because you yourself have repeated several times that the natural representation of an address is in its binary 32-bit unmodified form. I simply wanted to see how you would achieve the required mathematics to accomplish subnet filtering while working around SQL Server's limitations. Specifically the fact that [url=http://msdn.microsoft.com/en-us/library/ms174965.aspx]"In a bitwise operation, only one expression can be of either binary or varbinary data type."[/url].[quote]... why on earth can either argument of ^ be binary but not both ...[/quote]As much as I agree, it is outside of our control. What we can control is how we choose to work around those restrictions to provide the best solution to satisfy our goal. That goal may be different for different people. Some may choose performance as their target, others storage efficiency and perhaps someone like yourself may choose to negate either for the purpose of natural representation. (Not to say that doing so cannot be done without achieving either of the other goals as well.)[quote]... one thing that sometimes makes me (mistakenly) think that binary might have an advantage: machines I worked on a (very) long time ago required int to be word-aligned, which made it a pain in the butt for byte-stream (even worse for bit-stream, like HDLC and SDLC - but of course bit-stuffing is now done in hardware) protocols, but that can be dealt with at the DB interface, it's not relevant internally, so I should ignore that thought.[/quote]SQL Server's 32 bit integer is its most used and most optimized datatype. Everything from indexes to storage and aggregate functions to joins have all been tweaked to gills in terms of efficiency and performance for this datatype. There are other datatypes that will perform similarly but I do not know of a datatype that can beat the integer.[quote]It's not actually difficult to produce code that works when the numbers are transformed as you suggest: it just needs one extra XOR (and some brackets) to test ...[/quote]I did not believe it as something that was difficult and difficulty is an arbitrary thing once wrapped inside a stored procedure or a function. It is mostly a curiosity to see your approach to the matter and, again, how you would work with SQL Server's limitations.[quote]... it no longer matches the definition of the subnet in a straightforward manner. And that, to me, seems to be pointless obscurity. One can fix it by saying "we'll use the mask as its own representation" but that would seem very strange when you've rejected using the IP address as its own representation because you have to work around the sign bit, since the masks and the IP addresses have identical structures and identical human-readable forms (with of course some constraints on which values are allowed to be used as masks).[/quote]I have to admit that at this point, I was no longer clear on the point you were trying to make as it is unclear what is pointlessly obscure, your XOR approach or my bitshifted approach.Now allow me to make some points of my own.I understand your point of view regarding storing IP addresses under their natural representations but being that SQL Server's integer datatype is its most efficient, I would still prefer to store the address as such regardless of the fact that it requires additional transformation to do so. At the end of the day, what is most important to me is how SQL Server performs with the data I choose to store within my database.Generally speaking, IP addresses exist within a database as a result of logging. The logging will often cumulate over time to generate hundreds of thousands if not millions of rows. When operations are done over those rows, they usually revolve around verifying if an IP address already exists, verifying the number of times a specific IP address has been logged or finding logged entries that match either a list of addresses or networks. The efficiency of doing those operations is based on the performance achieved when SQL Server executes the mathematics, the aggregate functions, the joins and/or sorts required to do so.Binary does not perform as well as the integer although the difference may be negligable if either your dataset is small or your operations are simplistic.To illustrate, I used my test database. Within it I have two IP address tables. The first one ([dbo].[IPAddresses]) contains 100,000 random IP addresses in three forms varchar(15), int and binary. The second one ([dbo].[ManyIPAddresses]) contains 1,000,000 random IP addresses (10 copies of the [dbo].[IPAddresses] table).If we look at simply filtering 1,000,000 rows to find IP addresses from a certain subnet, here is the setup for our comparison:[code]DECLARE @BinaryNetwork binary(4) SET @BinaryNetwork = [dbo].[ParseToBinary]('192.168.1.0')DECLARE @BinaryMask binary(4) SET @BinaryMask = [dbo].[ParseToBinary]('255.255.255.0')DECLARE @IntegerNetwork int SET @IntegerNetwork = [dbo].[ParseToInt]('192.168.1.0')[/code]Storing the address as binary(4) and filtering using XOR:[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE ((@BinaryNetwork ^ CAST([BinaryAddress] AS int)) & @BinaryMask) = 0SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 140 ms, elapsed time = 162 ms.[/code]Storing the address as an int and filtering using AND with an int mask:[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]WHERE [IntegerAddress] & @BinaryMask = @IntegerNetworkSET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 94 ms, elapsed time = 98 ms.[/code]Now if we compare the results between doing a binary JOIN vs an integer JOIN, this is what we achieve.Doing a binary JOIN:[code]SET STATISTICS TIME ONSELECT dbo.IPAddresses.StringAddressFROM dbo.IPAddressesINNER JOIN dbo.ManyIPAddressesON dbo.IPAddresses.BinaryAddress = dbo.ManyIPAddresses.BinaryAddress SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 1718 ms, elapsed time = 45913 ms.[/code]Doing an integer JOIN:[code]SET STATISTICS TIME ONSELECT dbo.IPAddresses.StringAddressFROM dbo.IPAddressesINNER JOIN dbo.ManyIPAddressesON dbo.IPAddresses.IntegerAddress = dbo.ManyIPAddresses.IntegerAddress SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 1032 ms, elapsed time = 45409 ms.[/code]As for sorting, this is what we achieve.Doing a binary SORT:[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]ORDER BY [BinaryAddress] ASCSET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 3390 ms, elapsed time = 46790 ms.[/code]Doing an integer SORT:[code]SET STATISTICS TIME ONSELECT [StringAddress]FROM [dbo].[ManyIPAddresses]ORDER BY [IntegerAddress] ASCSET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 2625 ms, elapsed time = 45974 ms.[/code]And let's finish with some grouping.Doing a binary GROUP BY:[code]SET STATISTICS TIME ONSELECT [BinaryAddress], COUNT([BinaryAddress])FROM [dbo].[ManyIPAddresses]GROUP BY [BinaryAddress]SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 610 ms, elapsed time = 3723 ms.[/code]Doing an integer GROUP BY:[code]SET STATISTICS TIME ONSELECT [IntegerAddress], COUNT([IntegerAddress])FROM [dbo].[ManyIPAddresses]GROUP BY [IntegerAddress]SET STATISTICS TIME OFFSQL Server Execution Times: CPU time = 484 ms, elapsed time = 2911 ms.[/code]So as you can see, not only does binary have its mathematical limitations, it also has its performance limitations. That's not to mention the fact that certain operations simply cannot be done on binary fields. Attempting to perform an aggregate function like AVG on a binary field will yield the error "Operand data type binary is invalid for avg operator.". Although this is something that would never be performed on IP addresses, it is still to be noted. But I digress.With the obvious performance advantages offered by the integer datatype, I would not hesitate to store IP addresses in the same way as I described in my article. The cost of transformation at the time of insert is minimal at best. With the speed of computed columns, transformation from its stored integer form back to a human readable varchar(15) or a naturally represented binary(4) can be also be achieved at a minimal cost. So is storing an IP address in its natural representation more important then storing it for efficiency and performance? I don't believe so but perhaps that's a need you may have.Sat, 31 Oct 2009 23:22:38 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxYou know they say about assumptions..Also I think the statement:[quote]It seemed to me to be probable that you were a DBA - after all, most of the articles posted on SQL ServerCentral are written by DBAs, and you certainly appeared to give no consideration to using the natural representation.[/quote]Is wrong, while I may be working as a DBA right now, that is not what I have been doing for the last several decades.. And very few of the DBAs that I know are only working in that role now, they all have a much wider range of experience.I had to think about whether I wanted to respond at all..[quote]That's not terribly surprising, he is a DBA not a data communications developer, but in my view DBAs should ask people with application field expertise before taking decisions like the choice of representation of an IP address.[/quote]I am taking exception to the premise of "not a data communications developer". That makes the statement that the front-end guy should be setting the datatypes, and that is a BAD assumption. Most (and I know I'm generalizing) front-end developers get into SQL so they can get data for their front-ends, and a data guy can usually tell the databases that were built by front-end guys, the front-end guys just didn't know any better, involvement of a data guy is usually pretty clear. Now as a subject matter expert in SQL I would ask the "data communications developer" what he needed and how he was going to use it and I would set the type, now if the developer was unhappy with that we would likely haggle. But then end decision is MINE, because it is MY function to be a an expert in SQL.CEWIISat, 31 Oct 2009 09:56:17 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Jean-Sebastien Carle (10/29/2009)[/b][hr]How are you so sure that I am a DBA? I am, in fact, a Senior Systems Architect and Developer and simply extend my skill with SQL Server. I have a very solid grasp of networking down to the bit level. I have not blatantly ignored any of its fundamental principles.[/quote]It seemed to me to be probable that you were a DBA - after all, most of the articles posted on SQL ServerCentral are written by DBAs, and you certainly appeared to give no consideration to using the natural representation. Nor did you say anything about transforming masks from varchar(15) form, which suggested to me that you hadn't thought of the impact of flipping the MS bit in the mask (flipping that sign bit is what your code does). I didn't at any point suggest that you had blatantly ignored any fundamental principle, nor that you didn't have a solid grasp of networking; if I gave the impression that I was suggesting any of that I'm very sorry, I certainly didn't intend to. I did suggest that you were not a data comms programmer, which I still think is true (for example I think a data comms programmer would know that the first RFC to specify a 32 bit internet address was RFC 760 of Jan 1980, rather than the more recent document that you referenced - and even that wasn't where it was first introduced)- or have you written and/or debugged chunks of code to implement protocols in layer 4 (transport) and downward? [quote]The perhaps unorthodox approach I took was the one that I believe had the best fit within SQL Server’s sometimes unexplainable limitations. It would appear that you’ve so closely focused on an IPv4 address’s natural representation that you’re ignoring the adaptations that are required within SQL Server to be able to do operations such as atomic subnet mask filtering.[/quote]Well, since T-SQL code to transform the string form to the natural representation is only trivially different from what you suggested (see snippet in the comment which has annoyed you) it doesn't seem to me that the choice of representation was really to do with SQL limitations; and I already explained in another comment how I would handle subnet checking, but as I just wrote that in algebraic notation, not in T-SQL, I've provided some T-SQL below. As to focus, well it's pretty trivial to store the natural representation so the question to me was why on earth anyone would want to do anything else. [quote]So I ask you, please show us how you would, using T-SQL, take the IP address 192.168.1.1 in binary form (0xC0A80101), apply a mask of 255.255.255.0 in binary form (0xFFFFFF00), and compare it to the network 192.168.1.0 in binary form (0xC0A80100) to see if the IP address is part of the network.[/quote]It's easy enough - although I don't understand why you want me to use binary, since the code snippet in the comment that offended you is clearly based on it being int. But code for binary is close to identical to that for int, so here it is:[code]-- first set up the addresses and mask specified by JS-Cdeclare @netAddress binary(4) set @netAddress = 0xC0A80100declare @netMask binary(4) set @netMask = 0xFFFFFF00declare @candidate binary(4) set @candidate = 0xC0A80101-- now the one-liner test to see whether the two addresses are in the same subnet if the specified mask is usedif ((@netAddress ^ cast(@candidate as int)) & @netMask ) = 0 select 'The candidate address is in the target network'else select 'The candidate address is not in the target network'go[/code]This works for all valid network addresses and netmasks, and all valid candidate addresses. It is symmetrical in the two addresses - what we are actually checking is whether the two subnets each containing one of the given addresses and both having the given mask are identical, so the cast is an annoyance (and one that really is caused by a ludicrous restriction in SQL - why on earth can either argument of ^ be binary but not both) which can be avoided by using int instead of binary(4) (and the symmetry of course merans that the cast can be moved to the other address instead in contexts where that would be more efficient): the real issue is about what bit pattern should be used and not about how it should be typed. There's one thing that sometimes makes me (mistakenly) think that binary might have an advantage: machines I worked on a (very) long time ago required int to be word-aligned, which made it a pain in the butt for byte-stream (even worse for bit-stream, like HDLC and SDLC - but of course bit-stuffing is now done in hardware) protocols, but that can be dealt with at the DB interface, it's not relevant internally, so I should ignore that thought.It's not actually difficult to produce code that works when the numbers are transformed as you suggest: it just needs one extra XOR (and some brackets) to test whether 192.168.1.42 mask 255.255.255.0 and 192.168.1.192 mask 255.255.255.0 are both the same when you are representing the addresses and mask by the integers 0x40A8012A, 0x40A801C0, and 0x7FFFFF00, even while making sure that you don't accidentally include include for example 64.168.1.200 in that subnet - but the test is no longer "no bits are 1 in the mask where the two addresses have different bits in that position", so it no longer matches the definition of the subnet in a straightforward manner. And that, to me, seems to be pointless obscurity. One can fix it by saying "we'll use the mask as its own representation" but that would seem very strange when you've rejected using the IP address as its own representation because you have to work around the sign bit, since the masks and the IP addresses have identical structures and identical human-readable forms (with of course some constraints on which values are allowed to be used as masks).Sat, 31 Oct 2009 05:56:55 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Tom.Thomson (10/29/2009)[/b][hr]... he is a DBA not a data communications developer, but in my view DBAs should ask people with application field expertise before taking decisions like the choice of representation of an IP address.[/quote]How are you so sure that I am a DBA? I am, in fact, a Senior Systems Architect and Developer and simply extend my skill with SQL Server. I have a very solid grasp of networking down to the bit level. I have not blatantly ignored any of its fundamental principles. The perhaps unorthodox approach I took was the one that I believe had the best fit within SQL Server’s sometimes unexplainable limitations. It would appear that you’ve so closely focused on an IPv4 address’s natural representation that you’re ignoring the adaptations that are required within SQL Server to be able to do operations such as atomic subnet mask filtering.So I ask you, please show us how you would, using T-SQL, take the IP address 192.168.1.1 in binary form (0xC0A80101), apply a mask of 255.255.255.0 in binary form (0xFFFFFF00), and compare it to the network 192.168.1.0 in binary form (0xC0A80100) to see if the IP address is part of the network.Thu, 29 Oct 2009 20:01:22 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Elliott W (10/29/2009)[/b][hr]Tom,I take exception to several things here.[/quote]Elliot, if you think I intended to offend the author or malign him in any way, you are quite wrong. If I gave that impression, I expressed myself badly.[quote]1. There was no confusion that the string version was for human readability and had nothing to do with storage.[/quote]There was a mention at the front of the article that the standard published way back when specified the IP address as 32 bits (4 octets) and that the string of 7 to 15 characters was there to make it easy to read. Then the article dived off into how to convert this string representation back to 32 bits with giving any consideration as to whether the 32-bit IP address might be a perfectly good representation.[quote]2. The statement: "anyone experienced in data communications who sees a 32 bit int column used to represent IPv4 address will naturally assume that the integer is the one represented by the same bit pattern as the IP address itself" I find to be invalid. If I see a field that is not clear I assume VERY LITTLE, maybe I'm the minority, but I wouldn't just assume that it is the 4 octet bit pattern, I would ask some questions. [/quote]I always hope people won't make unwarranted assumptions too; but after more than 4 decades of experience in computing I've reached (with sadness) the conclusion that although that is my hope I should not permit it to become my expectation.[quote]3. "It was as if the author knew that IP addresses were in theory 32-bit values but were not aware that that is what they are in practise, not just in theory, that the 32-bit value is what almost all software, other than user interface presentation software, actually uses, and therefor thought that any old 32-bit pattern would be just as good a representation as the 32-bit IP address itself as long as there was a one-to-one correspondance." We we reading the same article? I didn't see any issue here. I thought it was pretty clear, they do represent a 32-bit value BUT SQL can't hold the first octet without some manipulation.[/quote]But it is so trivial to get the IP address from the string representation - just change [code="plain"](CAST(PARSENAME(@IP,4) AS INT) - 128) * 16777216[/code]to read instead[code="plain"]CASE WHEN CAST(PARSENAME(@IP,4) AS INT) > 127 THEN CAST(PARSENAME(@IP,4) AS INT) - 256 ELSE CAST(PARSENAME(@IP,4) AS INT)END * 16777216[/code]and that changes the code in the paper to do it. I think if the author had considered using the natural representation and rejected it he would have mentioned it, so it appears to me that he didn't consider it. That's not terribly surprising, he is a DBA not a data communications developer, but in my view DBAs should ask people with application field expertise before taking decisions like the choice of representation of an IP address.As for comments about the risks of subsequent developers screwing up when the see this approach, I guess I have a greater awareness of how often developers quickly skim domcumentation and miss important points, leading to problems down the line; or perhaps I'm just more of a pessimist. Anyway, these risks are secondary to the risk that the real IP address will turn out to be needed once you have some sort of IP address in your DB.[quote]For a logging application I don't see any clear winner between binary(4) and int, especially with the use of the computed column to make the data readable when we query it for humans to read.[/quote]I don't disagree - there isn't much to choose between them for a logging application - but maybe the natural int representation has some advantages over the weird one of the article?. For a non-logging application, whether the natural representation is there or not is more important that whether it is there in its int verson or its binary(4) version.[quote]IF I were working on an application that dealt with IP addresses a lot and I thought for a second that I would be doing any submasking, I would almost certainly go with the int type.[/quote]That sounds as if you want to do subnet checking by arithmetic comparison rather than by bitwise XOR and AND; be warned that this will not work with the representation in the article - for example the subnet <126.0.0.0 MASK 249.0.0.0>, although it is a contiguous subrange of 32 bit iP address values (considered as 32 bit unsigned ints) is contiguous neither in the representation of the article nor in the natural representation as 32-bit signed ints. So it's better to use the bitwise masking operations (which are identical for binary(4) and for int). But the bitwise masking operations don't give the correct result if IP addresses and the netmask are all converted to 32 bit form using the article's algorithm - they do only if the natural representation is used for the netmask - in which case why not use it for the addresses as well? (I don't much care about binary(4) vs int, just about natural versus unnatural representation.)Thu, 29 Oct 2009 14:21:42 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Tom.Thomson (10/29/2009)[/b][hr]The point I was trying to make was that the article appeared to start from the point of view that what was needed was just an efficient 32-bit representation of a particular set of varchar(15) strings. Well, that's ignoring the fact that those strings are actually a human interface representation of 32 bit IPv4 adresses, so that the natural - and ultimately the most efficient (since real software using IP addresses often wants the real 32-bit IPv4 address, not some different 32-bit value) - representation is to use the IP address to represent itself. It was as if the author knew that IP addresses were in theory 32-bit values but were not aware that that is what they are in practise, not just in theory, that the 32-bit value is what almost all software, other than user interface presentation software, actually uses, and therefor thought that any old 32-bit pattern would be just as good a representation as the 32-bit IP address itself as long as there was a one-to-one correspondance.With the article's representation, there is a serious possibility of dangerous misunderstandings: anyone experienced in data communications who sees a 32 bit int column used to represent IPv4 address will naturally assume that the integer is the one represented by the same bit pattern as the IP address itself; so that approach would need caveats all over the documentation and in code comments and even then would risk people taking the integer as the IP address, even though it clearly isn't. So there's an "is this way of doing it really safe" question as well as efficiency concerns.[/quote]Tom,I take exception to several things here.1. There was no confusion that the string version was for human readability and had nothing to do with storage.2. The statement: "anyone experienced in data communications who sees a 32 bit int column used to represent IPv4 address will naturally assume that the integer is the one represented by the same bit pattern as the IP address itself" I find to be invalid. If I see a field that is not clear I assume VERY LITTLE, maybe I'm the minority, but I wouldn't just assume that it is the 4 octet bit pattern, I would ask some questions.3. "It was as if the author knew that IP addresses were in theory 32-bit values but were not aware that that is what they are in practise, not just in theory, that the 32-bit value is what almost all software, other than user interface presentation software, actually uses, and therefor thought that any old 32-bit pattern would be just as good a representation as the 32-bit IP address itself as long as there was a one-to-one correspondance." We we reading the same article? I didn't see any issue here. I thought it was pretty clear, they do represent a 32-bit value BUT SQL can't hold the first octet without some manipulation.4. Most people would use this implementation in a logging application so there isn't going to be much system-to-system communication. You highlight potential issues with "so that approach would need caveats all over the documentation and in code comments and even then would risk people taking the integer as the IP address, even though it clearly isn't" that cover what I view to be the exception. Could these things happen, sure but I think that the potential risk is not anywhere near as dire or as likely to happen as stated. Your data dictionary just needs to clearly state what it is and how you get to the value.You are clearly unhappy with this solution, so don't use it. You highlight some points that in my opinion are the exceptions that don't negate the use of this method. Developers will invariably make some assumptions that are wrong, but their assumptions don't negate the methodologies that they made the assumptions about.With that I'll climb off my soapbox..So now I'll take about MY assumptions..For a logging application I don't see any clear winner between binary(4) and int, especially with the use of the computed column to make the data readable when we query it for humans to read.IF I were working on an application that dealt with IP addresses a lot and I thought for a second that I would be doing any submasking, I would almost certainly go with the int type.My data dictionary would state that the field is an encoded field containing the 4 octets and the formula used to encode them. I would also state what the human readable field name is if there is one..My design documentation would reference which method I chose and some quick reasons why, it would also likely contain the formulas to go back and forth as well as a reference to there being a human readable column if applicable.I would expect my developers to read and understand this document and if not to ask questions.CEWIIThu, 29 Oct 2009 08:25:36 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Jean-Sebastien Carle (10/28/2009)[/b][hr]Also, the choice between varbinary(4) (which should really be binary(4)) and int comes down to deciding between being able to natively bitmask IPs with subnet from within SQL Server or not. The reason I chose int regardless of the shift into negative is that I can write a stored procedure to retrieve all IPs of a given subnet, which could not be possible with binary(4) and would require me to retrieve the full list and then bitmask filter it in the application layer.[/quote]Yes, it should be binary(4) not varbinary(4). But I don't understand at all why you think I can't do subnet checks in SQL using binary(4) or, for that matter, the 4 tinyints representation.The check for whether address a occurs in the subnet base b mask m is quite simple: ((a XOR b) AND m) = 0which is easy to translate into SQL - I guess we agree on that?(Of course if all three values are ints, and wre produced from the varchar(15) representation according to your scheme, this doesn't work! To make it work you would have to have converted the mask using an algorithm that would deliver an IP address from the varchar(15) representation instead of a different integer, so your system would have to end up having to have both conversions in it).If a is 4 tinyints a1 a2 a3 a4 and the base and mask values are similarly given in small parts, the check is (((a1 XOR b1) AND m1) = 0) and (((a2 XOR b2) AND m2) = 0) and (((a3 XOR b3) AND m3) = 0) and (((a3 XOR b3) AND m3) = 0)This too is easy to convert to SQL.If a is an array of 4 octets, and the other values also, the test is (((a[1] XOR b[1]) AND m[1] = 0) and ...etceterawhich is also trivial to turn into SQL. But it isn't even neccessary to worry about the 4 components if you are using binary(4) to represent the array of 4 octets, since bitwise XOR and AND are directly applicable to binary(4) values (see for example http://msdn.microsoft.com/en-us/library/ms190277%28SQL.105%29.aspx) - the only difference from the integer case is that you might write 0x00000000 instead of 0.So why can you not do it in SQL, why do you have to break out into the application layer?Thu, 29 Oct 2009 08:10:57 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxYes, see page one of postings..We have tested int to string using function and computed column and SQLCLRWe have tested binary(4) to string using function and computed column and SQLCLRCEWIIThu, 29 Oct 2009 07:58:30 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx>Now I'm going to test the retrieval of the varbinary(4) back to a string.. When I'm all done I will attach the code for all.Have you done this?Thu, 29 Oct 2009 07:47:51 GMTClive Chineryhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxMy intention was not to mislead or misinform, however do keep in mind that the article was dedicated to SQL Server efficiency. I am fully versed in how 32-bit network addresses function and I understand your concern for incorrect translation. As Elliott has pointed out before, there is no one shoe fits all solution. The SQL Server int datatype was chosen and worked around its limitations for it's native advantages. The binary datatype has unfortunate limitations that are not present with int. If native translation from SQL Server to your application is important or frequent because you do a lot of in application IP address processing then binary is the way to go for you.Thu, 29 Oct 2009 07:23:23 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Elliott W (10/28/2009)[/b][hr]I am not sure about this statement "I think this nicely illustrates that a little knowledge is a dangerous thing - it shows us what might happen when the DBA doesn't really understand what the data is but acts on his misunderstqanding". I don't see how this really applies..CEWII[/quote]The point I was trying to make was that the article appeared to start from the point of view that what was needed was just an efficient 32-bit representation of a particular set of varchar(15) strings. Well, that's ignoring the fact that those strings are actually a human interface representation of 32 bit IPv4 adresses, so that the natural - and ultimately the most efficient (since real software using IP addresses often wants the real 32-bit IPv4 address, not some different 32-bit value) - representation is to use the IP address to represent itself. It was as if the author knew that IP addresses were in theory 32-bit values but were not aware that that is what they are in practise, not just in theory, that the 32-bit value is what almost all software, other than user interface presentation software, actually uses, and therefor thought that any old 32-bit pattern would be just as good a representation as the 32-bit IP address itself as long as there was a one-to-one correspondance.With the article's representation, there is a serious possibility of dangerous misunderstandings: anyone experienced in data communications who sees a 32 bit int column used to represent IPv4 address will naturally assume that the integer is the one represented by the same bit pattern as the IP address itself; so that approach would need caveats all over the documentation and in code comments and even then would risk people taking the integer as the IP address, even though it clearly isn't. So there's an "is this way of doing it really safe" question as well as efficiency concerns.Thu, 29 Oct 2009 07:07:48 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxAlso, the choice between varbinary(4) (which should really be binary(4)) and int comes down to deciding between being able to natively bitmask IPs with subnet from within SQL Server or not. The reason I chose int regardless of the shift into negative is that I can write a stored procedure to retrieve all IPs of a given subnet, which could not be possible with binary(4) and would require me to retrieve the full list and then bitmask filter it in the application layer.Wed, 28 Oct 2009 12:31:00 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Tom.Thomson (10/28/2009)[/b][hr]Yes, of course we have to have some negative values. But I would be much happier if the integer stored actually consisted of the 32 bit IP address, instead of some bizarre different bit pattern, and the conversions in the article do not achieve that. If we are going to pass this IP address to some low leveldata comms software, we need the number to be negative when the relevant octet is above 127, and positive when it is under 128 -not the other way round. The dotted notation 1.1.1.1 represents the 32 bit value 0x01010101, not 0x81010101, and it is not useful to store it as the latter instead of the former. Of course it's trivial to alter the conversion to do the right thing.I think this nicely illustrates that a little knowledge is a dangerous thing - it shows us what might happen when the DBA doesn't really understand what the data is but acts on his misunderstqanding: with the approach in the article if some application component decides it wants the 32-bit IP address it has to do an extra conversion, because the database is providing only varchar and the wrong 32-bit pattern; that will probably destroy efficiency at some point in the future. It's much better if the DBA finds out what the external world uses and follows suit (in this case, uses the correct 32 bit pattern - which probably ought to be described as varbinary(4) rather than int, since IP protocol works in terms of streams of octets, not integers). Of course there will be casses where what the external world uses will be hopelessly ineficient - but that certainly isn't the case here: be under no illusion that any serious communications software anywhere uses the varbinary(15) representation internally, it's just a device for the human interface, internally the representation is 4 octets.[/quote]I think the underlying discussion eventually came to the conclusion that a calculated column abstracts the underlying storage from the presentation. Given this, whether you want to use the varbinary(4) or int methods is effectively irrelevant since the computed column hides it.I am not sure about this statement "I think this nicely illustrates that a little knowledge is a dangerous thing - it shows us what might happen when the DBA doesn't really understand what the data is but acts on his misunderstqanding". I don't see how this really applies..CEWIIWed, 28 Oct 2009 12:25:07 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]nimdil (8/19/2009)[/b][hr][quote][b]nicholastjh (8/19/2009)[/b][hr]Nice article. Very informative.But at the final function where 128 is subtracted from the result of ParseName, before being multiplied by 16777216 (2^24), could potentially yield negative results, if the first final octet is not above 128.For example: 127.0.0.1 would result in -16777215.[/quote]That's the whole point. Some IP addresses will map to negative integers. As there are 2^32 IP Addresses and 2^31 non-negative integers in SQL Server we have to use positive as well as negative values.[/quote]Yes, of course we have to have some negative values. But I would be much happier if the integer stored actually consisted of the 32 bit IP address, instead of some bizarre different bit pattern, and the conversions in the article do not achieve that. If we are going to pass this IP address to some low leveldata comms software, we need the number to be negative when the relevant octet is above 127, and positive when it is under 128 -not the other way round. The dotted notation 1.1.1.1 represents the 32 bit value 0x01010101, not 0x81010101, and it is not useful to store it as the latter instead of the former. Of course it's trivial to alter the conversion to do the right thing.I think this nicely illustrates that a little knowledge is a dangerous thing - it shows us what might happen when the DBA doesn't really understand what the data is but acts on his misunderstqanding: with the approach in the article if some application component decides it wants the 32-bit IP address it has to do an extra conversion, because the database is providing only varchar and the wrong 32-bit pattern; that will probably destroy efficiency at some point in the future. It's much better if the DBA finds out what the external world uses and follows suit (in this case, uses the correct 32 bit pattern - which probably ought to be described as varbinary(4) rather than int, since IP protocol works in terms of streams of octets, not integers). Of course there will be casses where what the external world uses will be hopelessly ineficient - but that certainly isn't the case here: be under no illusion that any serious communications software anywhere uses the varbinary(15) representation internally, it's just a device for the human interface, internally the representation is 4 octets.Wed, 28 Oct 2009 11:28:08 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx207ms over 100K records hardly seems worth it.. Good to know though..CEWIIMon, 31 Aug 2009 20:14:14 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxDynamically computed : [code](100000 row(s) affected)SQL Server Execution Times: CPU time = 204 ms, elapsed time = 4500 ms.[/code]Persisted : [code](100000 row(s) affected)SQL Server Execution Times: CPU time = 94 ms, elapsed time = 4293 ms.[/code]In terms of disk space, persisted required 1.484 MB of additional storage over 100,000 rows (an average of 15.56 bytes per row).Both are lightweight solutions, so you can trade up between what's more important for your particular situation : speed or disk space.Mon, 31 Aug 2009 20:07:45 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Jean-Sebastien Carle (8/31/2009)[/b][hr]I only used static text to calculate the int variation as a demonstration and for testing in previous posts of this thread. In a real world environment, I would not store the static text version, I would store the int version and use the computed column to retrieve the text version.[/quote]Wonderful, so we agree. Altogether, I thought this was a wonderful discussion with lots of variations and some cool ideas. I look forward to you addendum.Since that direction (int->text) would be deterministic you could persist it, I'd want to performance test it, but it is an option and it would depend on how often I used it..CEWIIMon, 31 Aug 2009 19:55:19 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Elliott W (8/31/2009)[/b][hr]Also, it looks like Jean-Sebastien's solution stores the text and calculates the int, in that case you spend up to 11 extra bytes to store the address. As a thought, if you store the int and calc the text, it is probably considered deterministic and could be persisted.. I was commenting originally on his solution and not yours..CEWII[/quote]I only used static text to calculate the int variation as a demonstration and for testing in previous posts of this thread. In a real world environment, I would not store the static text version, I would store the int version and use the computed column to retrieve the text version.Mon, 31 Aug 2009 19:48:04 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxAh... sorry. My mistake, Elliott. Thank you for the feedback. :-)And, to your good point and if I remember correctly, all of the calculated columns constructed from the saved 4 octets are, in fact, deterministic as you suggest they might be.Mon, 31 Aug 2009 10:15:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Jeff Moden (8/29/2009)[/b][hr]heh... understood... I just don't want developers to use a tool just because it's there... I want them to use it for all the right reasons as I suspect you do. And, my solution didn't require 11 bytes as you suggest... it only required 4 and, because of the computed columns, it allowed greatly expanded functionality without further obfuscation of the IP address.[/quote]I agree, don't use a tool simply because its there. But I've met a bunch of people who know a lot about SQL and very little about other tools. This leads to what I call the hammer and nail problem. When all you have is a hammer (SQL) every problem looks like nail.There are a lot of tools, some are better for some cases, some better for others. There is one thing I am (personally) fundamentally opposed to, that is SQLCLR user defined datatypes, I'm sure they would be great, right up until the moment you have to change the code, then it becomes a nightmare.. Keep all tools on your pallete and chose the one that fits your problem.Also, it looks like Jean-Sebastien's solution stores the text and calculates the int, in that case you spend up to 11 extra bytes to store the address. As a thought, if you store the int and calc the text, it is probably considered deterministic and could be persisted.. I was commenting originally on his solution and not yours..CEWIIMon, 31 Aug 2009 09:11:13 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Elliott W (8/26/2009)[/b][hr]I want the developers reading this to know that all tools should be on the workbench. And to keep an eye out for the right tool for the job. When you can do set based operations it is best to do them. SQLCLR is a tool, it doesn't fit all or even most scenarios. In this scenario the computed column is a good solution provided you store the string then you can compute the int, but it costs you ~11bytes/record, I think a better approach is to store the int and compute the string, you save about ~11bytes/record and don't lose functionality. Also in this scenario we can show that if you are going to be converting back and forth that the CLR module will perform better. Tradeoffs, speed for convenience..I unfortunately have seen way to many DBAs flat out not decline SQLCLR even if it could be shown to be the best solution. What really turned me to the dark side was a talk I attended at Dev Connections where (I think) Gert Drapers was showing that for some situations like text manipulation that a CLR module will hands down outperform T-SQL. And I think DBAs don't like them because they are opaque and can't look inside. This is why the sourcecode needs to be available to DBAs and the DBAs need to be knowledgeable enough to read them..Short answer, there are scenarios in the IP Addr discussion where the computed column is a clear winner, it gets a lot murkier outside of that what the best solution is.CEWII[/quote]heh... understood... I just don't want developers to use a tool just because it's there... I want them to use it for all the right reasons as I suspect you do. And, my solution didn't require 11 bytes as you suggest... it only required 4 and, because of the computed columns, it allowed greatly expanded functionality without further obfuscation of the IP address.Sat, 29 Aug 2009 13:51:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxNice supplement to the article I wrote [url=http://blog.developpez.com/elsuket/p7935/snippets/caracteriser-une-adresse-ip/]there[/url] ;)The code extracts the class of the IP Adress ;)@++ ;)Sat, 29 Aug 2009 10:58:21 GMTElSukethttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxI'm thinking the problem is parsename is probably tagged as non-deterministic.. Not sure why, if you feed the same value in you should always get the same results..I think I could make the CLR objects deterministic, I just have to add am attribute..I did a little further checking, new data bold old data unbold.100,000 Rows (averages)varbinary to string 474ms (CLR - IP address Method)string to varbinary 1611ms (CLR - IP Address Method)string to varbinary 648ms (CLR - string breaker)string to int 539ms (CLR - string breaker)string to int 406ms (computed column)[b]int to string 525ms (CLR - reverse of computed column)[/b][b]select string column 441ms (no computations, or CLR)[/b][b]select int column 395ms (no computations, or CLR)[/b]CEWIIWed, 26 Aug 2009 14:07:49 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxI wonder if there isn't a way to 'force' or 'convince' SQL Server that the computation IS deterministic which would allow it to be persisted...Wed, 26 Aug 2009 05:18:30 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxJeff,I had a reply just about done then I hit the back button.. So here is the really short version..I want the developers reading this to know that all tools should be on the workbench. And to keep an eye out for the right tool for the job. When you can do set based operations it is best to do them. SQLCLR is a tool, it doesn't fit all or even most scenarios. In this scenario the computed column is a good solution provided you store the string then you can compute the int, but it costs you ~11bytes/record, I think a better approach is to store the int and compute the string, you save about ~11bytes/record and don't lose functionality. Also in this scenario we can show that if you are going to be converting back and forth that the CLR module will perform better. Tradeoffs, speed for convenience..I unfortunately have seen way to many DBAs flat out not decline SQLCLR even if it could be shown to be the best solution. What really turned me to the dark side was a talk I attended at Dev Connections where (I think) Gert Drapers was showing that for some situations like text manipulation that a CLR module will hands down outperform T-SQL. And I think DBAs don't like them because they are opaque and can't look inside. This is why the sourcecode needs to be available to DBAs and the DBAs need to be knowledgeable enough to read them..Short answer, there are scenarios in the IP Addr discussion where the computed column is a clear winner, it gets a lot murkier outside of that what the best solution is.CEWIIWed, 26 Aug 2009 01:08:30 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxAnd I gotta say that computed column performs pretty well, its too bad that SQL doesn't think its deterministic because it could then be persisted..I got to thinking that there are a couple more CLR paths I could go down..Here are the breakdowns since I don't want to post any more code tonight..100,000 Rows (averages)varbinary to string 474ms (CLR - IP address Method)string to varbinary 1611ms (CLR - IP Address Method)string to varbinary 648ms (CLR - string breaker)string to int 539ms (CLR - string breaker)string to int 406ms (computed column)Conclusions:If you want to store the data as text in the table you can use the computed column and get pretty good performance on compares but cost you at least 11 bytes of storage per record.The varbinary method is a little slower than the int method but previous posts may provide insight into the limitations imposed.If you want to store the data as an int and use a computed column to make it human readable, I think you would save some disk space but the performance would be the same as if you stored the text..If you plan to convert back and forth then the CLR will likely perform better.CEWIIWed, 26 Aug 2009 00:34:50 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote][b]Elliott W (8/25/2009)[/b][hr][quote]Good point... No flames... just thoughts.... If all you intend to do is split the octets, then the six line T-SQL solution that uses PARSENAME is almost as fast, requires no externally compiled code, and requires no call to an external assembly. --Jeff Moden[/quote]I know there is a lot of FUD in the DBA community about SQLCLR, but being able to code in VB and read C# I have an understanding of what the developer is trying to accomplish and have a certain level of trust when I can examine the code. Some people see SQLCLR and immediately say no without consideration, I'm not saying that you are. But people who do that are acting in what *I* (the world according to Elliott) feel is an ignorant way, there are lots of things you can accomplish in SQL without resorting to SQL CLR, and quite a few of them make more sense in SQL CLR. So sayeth my personal experience.. So climbing off my soapbox..I have it coded and am getting ready to test it. When I'm done I will pack everything up and include it with my response.. For everyone to play with..CEWII[/quote]I have no FUD about SQLCLR nor do I believe that most of the rest of the DBA community does, either. I'll just state that except for certain RegEx functionality (which most good developers and DBA's can usually come real close to beating) and certain mathematical functionality, SQLCLR has met the same fate as Cursors and While Loops... people use it to overcome their lack of knowledge of T-SQL and set based programming and it usually costs a bit more in performance, resource usage, and maintenance hassles. The reasons why DBA's don't care for most CLR's is because of the problems I mentioned... it usually costs a bit more in performance, resource usage, and maintenance hassles just like you and Jean-Sebastien just proved (laptop mileage not withstanding, of course :-D). It has nothing to do with FUD. In fact, I'd like to turn this around and say there's more FUD about T-SQL and set based programming on the behalf of many developers than there will ever be by DBA's on the subject of SQLCLR's. It's why developers turn to Cursors, While Loops, and CLR's to begin with. ;-)Wed, 26 Aug 2009 00:31:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxOk, I think Jean-Sebastien might have me..String to varbinary[code](100000 row(s) affected) SQL Server Execution Times: CPU time = 547 ms, elapsed time = 1481 ms. SQL Server Execution Times: CPU time = 688 ms, elapsed time = 1579 ms. SQL Server Execution Times: CPU time = 719 ms, elapsed time = 1586 ms. SQL Server Execution Times: CPU time = 609 ms, elapsed time = 1668 ms. SQL Server Execution Times: CPU time = 656 ms, elapsed time = 1643 ms. SQL Server Execution Times: CPU time = 656 ms, elapsed time = 1585 ms. SQL Server Execution Times: CPU time = 625 ms, elapsed time = 1697 ms. SQL Server Execution Times: CPU time = 687 ms, elapsed time = 1601 ms. SQL Server Execution Times: CPU time = 500 ms, elapsed time = 1618 ms. SQL Server Execution Times: CPU time = 672 ms, elapsed time = 1647 ms.[/code]varbinary back to string[code](100000 row(s) affected) SQL Server Execution Times: CPU time = 203 ms, elapsed time = 500 ms. SQL Server Execution Times: CPU time = 172 ms, elapsed time = 461 ms. SQL Server Execution Times: CPU time = 203 ms, elapsed time = 464 ms. SQL Server Execution Times: CPU time = 172 ms, elapsed time = 460 ms. SQL Server Execution Times: CPU time = 156 ms, elapsed time = 425 ms. SQL Server Execution Times: CPU time = 219 ms, elapsed time = 465 ms. SQL Server Execution Times: CPU time = 203 ms, elapsed time = 526 ms. SQL Server Execution Times: CPU time = 140 ms, elapsed time = 436 ms. SQL Server Execution Times: CPU time = 188 ms, elapsed time = 501 ms. SQL Server Execution Times: CPU time = 156 ms, elapsed time = 502 ms.[/code]It doesn't look like perfomance is linear though, an order of manitude difference in source data did not translate into 10 times slower times..10,000 Rows (averages)string to varbinary 197msvarbinary to string 103ms100,000 Rows (averages)string to varbinary 1611msvarbinary to string 474msI can only counter this was on a laptop so mileage may vary..We'll see what everybody has to say..CEWIITue, 25 Aug 2009 22:12:16 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxYou may be right, I did 10000, which I went back to the article and it mentions 10,000 loops, but doesn't say how many rows. I see that you had 100000 in your tests above.. I shall retest..CEWIITue, 25 Aug 2009 21:50:36 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxI respectfully disagree. As has already been discussed, the performance hit is due to the use of UDFs. Creating a computed column on the parsename method demonstrates how fast parsename really is.[code][IntegerComputed] AS ((((CONVERT([int],parsename([StringFixed],(4)),0)-(128))*(16777216)+CONVERT([int],parsename([StringFixed],(3)),0)*(65536))+CONVERT([int],parsename([StringFixed],(2)),0)*(256))+CONVERT([int],parsename([StringFixed],(1)),0))[/code][code]SELECT [IntegerComputed] FROM [bdq].[dbo].[IPAddresses][/code][code](100000 row(s) affected)SQL Server Execution Times: CPU time = 172 ms, elapsed time = 979 ms.[/code]Which would make the SQLCLR method 1/6th slower then native T-SQL.Tue, 25 Aug 2009 21:48:31 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxand the conversion to string from varbinary is also rockin fast[code](10000 row(s) affected)-- Output of statistics time on for 10 runs in a row SQL Server Execution Times: CPU time = 46 ms, elapsed time = 122 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 109 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 110 ms. SQL Server Execution Times: CPU time = 47 ms, elapsed time = 104 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 82 ms. SQL Server Execution Times: CPU time = 63 ms, elapsed time = 115 ms. SQL Server Execution Times: CPU time = 47 ms, elapsed time = 113 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 83 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 108 ms. SQL Server Execution Times: CPU time = 15 ms, elapsed time = 83 ms.[/code]I have attached the code, there is the project that for the SQLCLR object, the compiled SQLCLR dll, scripts to install the DLL and build the tables and functions necessary. Also the queries to test the times.--Edited to protect the guilty.. --I also want to give credit to loganmerazzi for the CLR idea, I hadn't realized it at the time but he first mentioned it.CEWIITue, 25 Aug 2009 21:36:08 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[ See after next post ... ]Tue, 25 Aug 2009 21:27:19 GMTJean-Sebastien Carlehttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspxWell it looks like conversion from a string to a varbinary(4) is rockin fast with SQLCLR..[code](10000 row(s) affected)-- Output of statistics time on for 10 runs in a row SQL Server Execution Times: CPU time = 62 ms, elapsed time = 200 ms. SQL Server Execution Times: CPU time = 62 ms, elapsed time = 247 ms. SQL Server Execution Times: CPU time = 63 ms, elapsed time = 210 ms. SQL Server Execution Times: CPU time = 94 ms, elapsed time = 199 ms. SQL Server Execution Times: CPU time = 125 ms, elapsed time = 205 ms. SQL Server Execution Times: CPU time = 46 ms, elapsed time = 161 ms. SQL Server Execution Times: CPU time = 47 ms, elapsed time = 145 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 253 ms. SQL Server Execution Times: CPU time = 63 ms, elapsed time = 170 ms. SQL Server Execution Times: CPU time = 109 ms, elapsed time = 183 ms.[/code]On a 2GHz laptop with 2GB of RAM, running SQL 2008 Express. I wonder what it would look like on server class hardware..Now I'm going to test the retrieval of the varbinary(4) back to a string.. When I'm all done I will attach the code for all.-- Edited to protect the guilty..--CEWIITue, 25 Aug 2009 21:15:26 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic768952-1617-1.aspx[quote]Good point... No flames... just thoughts.... If all you intend to do is split the octets, then the six line T-SQL solution that uses PARSENAME is almost as fast, requires no externally compiled code, and requires no call to an external assembly. --Jeff Moden[/quote]I know there is a lot of FUD in the DBA community about SQLCLR, but being able to code in VB and read C# I have an understanding of what the developer is trying to accomplish and have a certain level of trust when I can examine the code. Some people see SQLCLR and immediately say no without consideration, I'm not saying that you are. But people who do that are acting in what *I* (the world according to Elliott) feel is an ignorant way, there are lots of things you can accomplish in SQL without resorting to SQL CLR, and quite a few of them make more sense in SQL CLR. So sayeth my personal experience.. So climbing off my soapbox..I have it coded and am getting ready to test it. When I'm done I will pack everything up and include it with my response.. For everyone to play with..CEWIITue, 25 Aug 2009 20:01:12 GMTElliott Whitlow