Tom.Thomson (11/3/2009)
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.
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.
IP IP1 IP2
--------------- ----------- ---------- ----------- ----------
0.0.0.0 -2147483648 0x80000000 0 0x00000000
1.0.0.0 -2130706432 0x81000000 16777216 0x01000000
2.0.0.0 -2113929216 0x82000000 33554432 0x02000000
3.0.0.0 -2097152000 0x83000000 50331648 0x03000000
4.0.0.0 -2080374784 0x84000000 67108864 0x04000000
5.0.0.0 -2063597568 0x85000000 83886080 0x05000000
6.0.0.0 -2046820352 0x86000000 100663296 0x06000000
7.0.0.0 -2030043136 0x87000000 117440512 0x07000000
8.0.0.0 -2013265920 0x88000000 134217728 0x08000000
9.0.0.0 -1996488704 0x89000000 150994944 0x09000000
10.0.0.0 -1979711488 0x8A000000 167772160 0x0A000000
11.0.0.0 -1962934272 0x8B000000 184549376 0x0B000000
12.0.0.0 -1946157056 0x8C000000 201326592 0x0C000000
13.0.0.0 -1929379840 0x8D000000 218103808 0x0D000000
14.0.0.0 -1912602624 0x8E000000 234881024 0x0E000000
15.0.0.0 -1895825408 0x8F000000 251658240 0x0F000000
16.0.0.0 -1879048192 0x90000000 268435456 0x10000000
17.0.0.0 -1862270976 0x91000000 285212672 0x11000000
18.0.0.0 -1845493760 0x92000000 301989888 0x12000000
19.0.0.0 -1828716544 0x93000000 318767104 0x13000000
20.0.0.0 -1811939328 0x94000000 335544320 0x14000000
21.0.0.0 -1795162112 0x95000000 352321536 0x15000000
22.0.0.0 -1778384896 0x96000000 369098752 0x16000000
23.0.0.0 -1761607680 0x97000000 385875968 0x17000000
24.0.0.0 -1744830464 0x98000000 402653184 0x18000000
25.0.0.0 -1728053248 0x99000000 419430400 0x19000000
26.0.0.0 -1711276032 0x9A000000 436207616 0x1A000000
27.0.0.0 -1694498816 0x9B000000 452984832 0x1B000000
28.0.0.0 -1677721600 0x9C000000 469762048 0x1C000000
29.0.0.0 -1660944384 0x9D000000 486539264 0x1D000000
30.0.0.0 -1644167168 0x9E000000 503316480 0x1E000000
31.0.0.0 -1627389952 0x9F000000 520093696 0x1F000000
32.0.0.0 -1610612736 0xA0000000 536870912 0x20000000
33.0.0.0 -1593835520 0xA1000000 553648128 0x21000000
34.0.0.0 -1577058304 0xA2000000 570425344 0x22000000
35.0.0.0 -1560281088 0xA3000000 587202560 0x23000000
36.0.0.0 -1543503872 0xA4000000 603979776 0x24000000
37.0.0.0 -1526726656 0xA5000000 620756992 0x25000000
38.0.0.0 -1509949440 0xA6000000 637534208 0x26000000
39.0.0.0 -1493172224 0xA7000000 654311424 0x27000000
40.0.0.0 -1476395008 0xA8000000 671088640 0x28000000
41.0.0.0 -1459617792 0xA9000000 687865856 0x29000000
42.0.0.0 -1442840576 0xAA000000 704643072 0x2A000000
43.0.0.0 -1426063360 0xAB000000 721420288 0x2B000000
44.0.0.0 -1409286144 0xAC000000 738197504 0x2C000000
45.0.0.0 -1392508928 0xAD000000 754974720 0x2D000000
46.0.0.0 -1375731712 0xAE000000 771751936 0x2E000000
47.0.0.0 -1358954496 0xAF000000 788529152 0x2F000000
48.0.0.0 -1342177280 0xB0000000 805306368 0x30000000
49.0.0.0 -1325400064 0xB1000000 822083584 0x31000000
50.0.0.0 -1308622848 0xB2000000 838860800 0x32000000
51.0.0.0 -1291845632 0xB3000000 855638016 0x33000000
52.0.0.0 -1275068416 0xB4000000 872415232 0x34000000
53.0.0.0 -1258291200 0xB5000000 889192448 0x35000000
54.0.0.0 -1241513984 0xB6000000 905969664 0x36000000
55.0.0.0 -1224736768 0xB7000000 922746880 0x37000000
56.0.0.0 -1207959552 0xB8000000 939524096 0x38000000
57.0.0.0 -1191182336 0xB9000000 956301312 0x39000000
58.0.0.0 -1174405120 0xBA000000 973078528 0x3A000000
59.0.0.0 -1157627904 0xBB000000 989855744 0x3B000000
60.0.0.0 -1140850688 0xBC000000 1006632960 0x3C000000
61.0.0.0 -1124073472 0xBD000000 1023410176 0x3D000000
62.0.0.0 -1107296256 0xBE000000 1040187392 0x3E000000
63.0.0.0 -1090519040 0xBF000000 1056964608 0x3F000000
64.0.0.0 -1073741824 0xC0000000 1073741824 0x40000000
65.0.0.0 -1056964608 0xC1000000 1090519040 0x41000000
66.0.0.0 -1040187392 0xC2000000 1107296256 0x42000000
67.0.0.0 -1023410176 0xC3000000 1124073472 0x43000000
68.0.0.0 -1006632960 0xC4000000 1140850688 0x44000000
69.0.0.0 -989855744 0xC5000000 1157627904 0x45000000
70.0.0.0 -973078528 0xC6000000 1174405120 0x46000000
71.0.0.0 -956301312 0xC7000000 1191182336 0x47000000
72.0.0.0 -939524096 0xC8000000 1207959552 0x48000000
73.0.0.0 -922746880 0xC9000000 1224736768 0x49000000
74.0.0.0 -905969664 0xCA000000 1241513984 0x4A000000
75.0.0.0 -889192448 0xCB000000 1258291200 0x4B000000
76.0.0.0 -872415232 0xCC000000 1275068416 0x4C000000
77.0.0.0 -855638016 0xCD000000 1291845632 0x4D000000
78.0.0.0 -838860800 0xCE000000 1308622848 0x4E000000
79.0.0.0 -822083584 0xCF000000 1325400064 0x4F000000
80.0.0.0 -805306368 0xD0000000 1342177280 0x50000000
81.0.0.0 -788529152 0xD1000000 1358954496 0x51000000
82.0.0.0 -771751936 0xD2000000 1375731712 0x52000000
83.0.0.0 -754974720 0xD3000000 1392508928 0x53000000
84.0.0.0 -738197504 0xD4000000 1409286144 0x54000000
85.0.0.0 -721420288 0xD5000000 1426063360 0x55000000
86.0.0.0 -704643072 0xD6000000 1442840576 0x56000000
87.0.0.0 -687865856 0xD7000000 1459617792 0x57000000
88.0.0.0 -671088640 0xD8000000 1476395008 0x58000000
89.0.0.0 -654311424 0xD9000000 1493172224 0x59000000
90.0.0.0 -637534208 0xDA000000 1509949440 0x5A000000
91.0.0.0 -620756992 0xDB000000 1526726656 0x5B000000
92.0.0.0 -603979776 0xDC000000 1543503872 0x5C000000
93.0.0.0 -587202560 0xDD000000 1560281088 0x5D000000
94.0.0.0 -570425344 0xDE000000 1577058304 0x5E000000
95.0.0.0 -553648128 0xDF000000 1593835520 0x5F000000
96.0.0.0 -536870912 0xE0000000 1610612736 0x60000000
97.0.0.0 -520093696 0xE1000000 1627389952 0x61000000
98.0.0.0 -503316480 0xE2000000 1644167168 0x62000000
99.0.0.0 -486539264 0xE3000000 1660944384 0x63000000
100.0.0.0 -469762048 0xE4000000 1677721600 0x64000000
101.0.0.0 -452984832 0xE5000000 1694498816 0x65000000
102.0.0.0 -436207616 0xE6000000 1711276032 0x66000000
103.0.0.0 -419430400 0xE7000000 1728053248 0x67000000
104.0.0.0 -402653184 0xE8000000 1744830464 0x68000000
105.0.0.0 -385875968 0xE9000000 1761607680 0x69000000
106.0.0.0 -369098752 0xEA000000 1778384896 0x6A000000
107.0.0.0 -352321536 0xEB000000 1795162112 0x6B000000
108.0.0.0 -335544320 0xEC000000 1811939328 0x6C000000
109.0.0.0 -318767104 0xED000000 1828716544 0x6D000000
110.0.0.0 -301989888 0xEE000000 1845493760 0x6E000000
111.0.0.0 -285212672 0xEF000000 1862270976 0x6F000000
112.0.0.0 -268435456 0xF0000000 1879048192 0x70000000
113.0.0.0 -251658240 0xF1000000 1895825408 0x71000000
114.0.0.0 -234881024 0xF2000000 1912602624 0x72000000
115.0.0.0 -218103808 0xF3000000 1929379840 0x73000000
116.0.0.0 -201326592 0xF4000000 1946157056 0x74000000
117.0.0.0 -184549376 0xF5000000 1962934272 0x75000000
118.0.0.0 -167772160 0xF6000000 1979711488 0x76000000
119.0.0.0 -150994944 0xF7000000 1996488704 0x77000000
120.0.0.0 -134217728 0xF8000000 2013265920 0x78000000
121.0.0.0 -117440512 0xF9000000 2030043136 0x79000000
122.0.0.0 -100663296 0xFA000000 2046820352 0x7A000000
123.0.0.0 -83886080 0xFB000000 2063597568 0x7B000000
124.0.0.0 -67108864 0xFC000000 2080374784 0x7C000000
125.0.0.0 -50331648 0xFD000000 2097152000 0x7D000000
126.0.0.0 -33554432 0xFE000000 2113929216 0x7E000000
127.0.0.0 -16777216 0xFF000000 2130706432 0x7F000000
128.0.0.0 0 0x00000000 -2147483648 0x80000000
129.0.0.0 16777216 0x01000000 -2130706432 0x81000000
130.0.0.0 33554432 0x02000000 -2113929216 0x82000000
131.0.0.0 50331648 0x03000000 -2097152000 0x83000000
132.0.0.0 67108864 0x04000000 -2080374784 0x84000000
133.0.0.0 83886080 0x05000000 -2063597568 0x85000000
134.0.0.0 100663296 0x06000000 -2046820352 0x86000000
135.0.0.0 117440512 0x07000000 -2030043136 0x87000000
136.0.0.0 134217728 0x08000000 -2013265920 0x88000000
137.0.0.0 150994944 0x09000000 -1996488704 0x89000000
138.0.0.0 167772160 0x0A000000 -1979711488 0x8A000000
139.0.0.0 184549376 0x0B000000 -1962934272 0x8B000000
140.0.0.0 201326592 0x0C000000 -1946157056 0x8C000000
141.0.0.0 218103808 0x0D000000 -1929379840 0x8D000000
142.0.0.0 234881024 0x0E000000 -1912602624 0x8E000000
143.0.0.0 251658240 0x0F000000 -1895825408 0x8F000000
144.0.0.0 268435456 0x10000000 -1879048192 0x90000000
145.0.0.0 285212672 0x11000000 -1862270976 0x91000000
146.0.0.0 301989888 0x12000000 -1845493760 0x92000000
147.0.0.0 318767104 0x13000000 -1828716544 0x93000000
148.0.0.0 335544320 0x14000000 -1811939328 0x94000000
149.0.0.0 352321536 0x15000000 -1795162112 0x95000000
150.0.0.0 369098752 0x16000000 -1778384896 0x96000000
151.0.0.0 385875968 0x17000000 -1761607680 0x97000000
152.0.0.0 402653184 0x18000000 -1744830464 0x98000000
153.0.0.0 419430400 0x19000000 -1728053248 0x99000000
154.0.0.0 436207616 0x1A000000 -1711276032 0x9A000000
155.0.0.0 452984832 0x1B000000 -1694498816 0x9B000000
156.0.0.0 469762048 0x1C000000 -1677721600 0x9C000000
157.0.0.0 486539264 0x1D000000 -1660944384 0x9D000000
158.0.0.0 503316480 0x1E000000 -1644167168 0x9E000000
159.0.0.0 520093696 0x1F000000 -1627389952 0x9F000000
160.0.0.0 536870912 0x20000000 -1610612736 0xA0000000
161.0.0.0 553648128 0x21000000 -1593835520 0xA1000000
162.0.0.0 570425344 0x22000000 -1577058304 0xA2000000
163.0.0.0 587202560 0x23000000 -1560281088 0xA3000000
164.0.0.0 603979776 0x24000000 -1543503872 0xA4000000
165.0.0.0 620756992 0x25000000 -1526726656 0xA5000000
166.0.0.0 637534208 0x26000000 -1509949440 0xA6000000
167.0.0.0 654311424 0x27000000 -1493172224 0xA7000000
168.0.0.0 671088640 0x28000000 -1476395008 0xA8000000
169.0.0.0 687865856 0x29000000 -1459617792 0xA9000000
170.0.0.0 704643072 0x2A000000 -1442840576 0xAA000000
171.0.0.0 721420288 0x2B000000 -1426063360 0xAB000000
172.0.0.0 738197504 0x2C000000 -1409286144 0xAC000000
173.0.0.0 754974720 0x2D000000 -1392508928 0xAD000000
174.0.0.0 771751936 0x2E000000 -1375731712 0xAE000000
175.0.0.0 788529152 0x2F000000 -1358954496 0xAF000000
176.0.0.0 805306368 0x30000000 -1342177280 0xB0000000
177.0.0.0 822083584 0x31000000 -1325400064 0xB1000000
178.0.0.0 838860800 0x32000000 -1308622848 0xB2000000
179.0.0.0 855638016 0x33000000 -1291845632 0xB3000000
180.0.0.0 872415232 0x34000000 -1275068416 0xB4000000
181.0.0.0 889192448 0x35000000 -1258291200 0xB5000000
182.0.0.0 905969664 0x36000000 -1241513984 0xB6000000
183.0.0.0 922746880 0x37000000 -1224736768 0xB7000000
184.0.0.0 939524096 0x38000000 -1207959552 0xB8000000
185.0.0.0 956301312 0x39000000 -1191182336 0xB9000000
186.0.0.0 973078528 0x3A000000 -1174405120 0xBA000000
187.0.0.0 989855744 0x3B000000 -1157627904 0xBB000000
188.0.0.0 1006632960 0x3C000000 -1140850688 0xBC000000
189.0.0.0 1023410176 0x3D000000 -1124073472 0xBD000000
190.0.0.0 1040187392 0x3E000000 -1107296256 0xBE000000
191.0.0.0 1056964608 0x3F000000 -1090519040 0xBF000000
192.0.0.0 1073741824 0x40000000 -1073741824 0xC0000000
193.0.0.0 1090519040 0x41000000 -1056964608 0xC1000000
194.0.0.0 1107296256 0x42000000 -1040187392 0xC2000000
195.0.0.0 1124073472 0x43000000 -1023410176 0xC3000000
196.0.0.0 1140850688 0x44000000 -1006632960 0xC4000000
197.0.0.0 1157627904 0x45000000 -989855744 0xC5000000
198.0.0.0 1174405120 0x46000000 -973078528 0xC6000000
199.0.0.0 1191182336 0x47000000 -956301312 0xC7000000
200.0.0.0 1207959552 0x48000000 -939524096 0xC8000000
201.0.0.0 1224736768 0x49000000 -922746880 0xC9000000
202.0.0.0 1241513984 0x4A000000 -905969664 0xCA000000
203.0.0.0 1258291200 0x4B000000 -889192448 0xCB000000
204.0.0.0 1275068416 0x4C000000 -872415232 0xCC000000
205.0.0.0 1291845632 0x4D000000 -855638016 0xCD000000
206.0.0.0 1308622848 0x4E000000 -838860800 0xCE000000
207.0.0.0 1325400064 0x4F000000 -822083584 0xCF000000
208.0.0.0 1342177280 0x50000000 -805306368 0xD0000000
209.0.0.0 1358954496 0x51000000 -788529152 0xD1000000
210.0.0.0 1375731712 0x52000000 -771751936 0xD2000000
211.0.0.0 1392508928 0x53000000 -754974720 0xD3000000
212.0.0.0 1409286144 0x54000000 -738197504 0xD4000000
213.0.0.0 1426063360 0x55000000 -721420288 0xD5000000
214.0.0.0 1442840576 0x56000000 -704643072 0xD6000000
215.0.0.0 1459617792 0x57000000 -687865856 0xD7000000
216.0.0.0 1476395008 0x58000000 -671088640 0xD8000000
217.0.0.0 1493172224 0x59000000 -654311424 0xD9000000
218.0.0.0 1509949440 0x5A000000 -637534208 0xDA000000
219.0.0.0 1526726656 0x5B000000 -620756992 0xDB000000
220.0.0.0 1543503872 0x5C000000 -603979776 0xDC000000
221.0.0.0 1560281088 0x5D000000 -587202560 0xDD000000
222.0.0.0 1577058304 0x5E000000 -570425344 0xDE000000
223.0.0.0 1593835520 0x5F000000 -553648128 0xDF000000
224.0.0.0 1610612736 0x60000000 -536870912 0xE0000000
225.0.0.0 1627389952 0x61000000 -520093696 0xE1000000
226.0.0.0 1644167168 0x62000000 -503316480 0xE2000000
227.0.0.0 1660944384 0x63000000 -486539264 0xE3000000
228.0.0.0 1677721600 0x64000000 -469762048 0xE4000000
229.0.0.0 1694498816 0x65000000 -452984832 0xE5000000
230.0.0.0 1711276032 0x66000000 -436207616 0xE6000000
231.0.0.0 1728053248 0x67000000 -419430400 0xE7000000
232.0.0.0 1744830464 0x68000000 -402653184 0xE8000000
233.0.0.0 1761607680 0x69000000 -385875968 0xE9000000
234.0.0.0 1778384896 0x6A000000 -369098752 0xEA000000
235.0.0.0 1795162112 0x6B000000 -352321536 0xEB000000
236.0.0.0 1811939328 0x6C000000 -335544320 0xEC000000
237.0.0.0 1828716544 0x6D000000 -318767104 0xED000000
238.0.0.0 1845493760 0x6E000000 -301989888 0xEE000000
239.0.0.0 1862270976 0x6F000000 -285212672 0xEF000000
240.0.0.0 1879048192 0x70000000 -268435456 0xF0000000
241.0.0.0 1895825408 0x71000000 -251658240 0xF1000000
242.0.0.0 1912602624 0x72000000 -234881024 0xF2000000
243.0.0.0 1929379840 0x73000000 -218103808 0xF3000000
244.0.0.0 1946157056 0x74000000 -201326592 0xF4000000
245.0.0.0 1962934272 0x75000000 -184549376 0xF5000000
246.0.0.0 1979711488 0x76000000 -167772160 0xF6000000
247.0.0.0 1996488704 0x77000000 -150994944 0xF7000000
248.0.0.0 2013265920 0x78000000 -134217728 0xF8000000
249.0.0.0 2030043136 0x79000000 -117440512 0xF9000000
250.0.0.0 2046820352 0x7A000000 -100663296 0xFA000000
251.0.0.0 2063597568 0x7B000000 -83886080 0xFB000000
252.0.0.0 2080374784 0x7C000000 -67108864 0xFC000000
253.0.0.0 2097152000 0x7D000000 -50331648 0xFD000000
254.0.0.0 2113929216 0x7E000000 -33554432 0xFE000000
255.0.0.0 2130706432 0x7F000000 -16777216 0xFF000000
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 = @IntegerNetwork
will 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.
That's correct, which is why I didn't specify an integer mask as it simply wouldn't work.
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 & @IntegerMask
as 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.
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.
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 + 1
DECLARE @EndIntegerAddress int SET @EndIntegerAddress = (@IntegerNetwork | CAST(~ CAST(@BinaryMask AS int) AS binary(4))) - 1
XOR using stored binary addresses.
SET STATISTICS TIME ON
SELECT [StringAddress]
FROM [dbo].[ManyIPAddresses]
WHERE ((@BinaryNetwork ^ CAST([BinaryAddress] AS int)) & @BinaryMask) = 0
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 117 ms.
XOR using stored integer addresses.
SET STATISTICS TIME ON
SELECT [StringAddress]
FROM [dbo].[ManyIPAddresses]
WHERE ((@IntegerNetwork ^ [IntegerAddress]) & @BinaryMask) = 0
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 108 ms.
AND using stored integer addresses.
SET STATISTICS TIME ON
SELECT [StringAddress]
FROM [dbo].[ManyIPAddresses]
WHERE [IntegerAddress] & @BinaryMask = @IntegerNetwork
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 97 ms.
BETWEEN using stored integer addresses.
SET STATISTICS TIME ON
SELECT [StringAddress]
FROM [dbo].[ManyIPAddresses]
WHERE [IntegerAddress] BETWEEN @StartIntegerAddress AND @EndIntegerAddress
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 73 ms.
Greater then, Lesser then comparison using integer addresses.
SET STATISTICS TIME ON
SELECT [StringAddress]
FROM [dbo].[ManyIPAddresses]
WHERE [IntegerAddress] >= @StartIntegerAddress AND [IntegerAddress] <= @EndIntegerAddress
SET STATISTICS TIME OFF
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 73 ms.
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.