Is there a way to generate a ~million mac addresses more efficiently?

Question

Is there a way to generate a ~million MAC addresses more efficiently?

Here is what I am doing, however it is taking ~10 minutes to generate a million MAC addresses and insert them into my table:

DECLARE @StartRange BINARY(6) 
DECLARE @EndRange BINARY(6) 
SET @StartRange = 0x0036D1F00000 
SET @EndRange = 0x0036D1FFFFFF 
--select convert(bigint,+ @EndRange) - convert(bigint,+ @StartRange) = 1048575
WHILE(convert(bigint, (SELECT IDENT_CURRENT('Mac_Address'))) < (convert(bigint, @EndRange) - convert(bigint, @StartRange)))
BEGIN
insert into Mac_Address (MacAddress)
select convert(BINARY(6),(convert(bigint, (SELECT IDENT_CURRENT('Mac_Address'))) + convert(bigint, @StartRange)))
END

This is the code I found online to do this, and it executes in a few seconds. However it is generating MAC addresses into a system table:

DECLARE @StartRange BINARY(8) 
DECLARE @EndRange BINARY(8) 
SET @StartRange = 0x00000004A500114B 
SET @EndRange = 0x00000004A50F11FF 

--select convert(integer,+ @EndRange) - convert(integer,+ @StartRange) = 983220

select convert(BINARY(8),RW + convert(integer, @StartRange)) 
from
(select row_number() over(order by a.id) As RW from syscolumns,syscolumns a,syscolumns b ,syscolumns c) b --I do not understand this line very well.
where RW between 1 and (convert(integer, @EndRange) - convert(integer, @StartRange))

Answer 1

It's not "generating MAC addresses into a system table" as you say. It is merely using the syscolumns table as a means to get a lot of rows. You can know this because all the system tables are in the FROM clause. To insert to a table requires at least INSERT or SELECT ... INTO.

The reason it is fast is because SQL Server is optimized for "row-based" operations. Inside the engine, of course it has to loop over each row, but it is heavily optimized. When you instead treat T-SQL as a procedural language and loop over each item one at a time, it can't use those optimizations and has to execute each statement one at a time under a completely different kind of execution context.

The syscolumns table is itself meaningless. It has value because it is guaranteed to have a lot of rows. It could be any table with many rows--the tables in the FROM clause are just being cross-joined together to get their Cartesian Product (meaning a LOT of rows) so that the Row_Number() function has something to work with to create a rowset with numbers from 1 to 1,000,000.

If you like, you can use an approach like this that doesn't hit any "real" tables at all, which should perform just as fast or faster. All it's doing is taking the initial SELECT 1 UNION ALL SELECT 1 and turning it into 4.3 bilion rows through repeated CROSS JOINing (but using the old-style syntax of FROM Table1, Table2 which is the same as FROM Table1 CROSS JOIN Table2 but shorter).

I tested the following in SQL 2008 and it works perfectly. It takes 3 seconds on my SQL Server (SQL Fiddle is incorrectly throwing an error at this time and I have emailed the admin).

DECLARE
   @StartRange bigint = 0x0004A500114B,
   @EndRange bigint = 0x0004A50F11FF;

WITH -- make sure you terminate your prior statement by putting ; after it
L0 AS (SELECT 1 N UNION ALL SELECT 1),
L1 AS (SELECT 1 N FROM L0, L0 B),
L2 AS (SELECT 1 N FROM L1, L1 B),
L3 AS (SELECT 1 N FROM L2, L2 B),
L4 AS (SELECT 1 N FROM L3, L3 B),
L5 AS (SELECT 1 N FROM L4, L4 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) N FROM L5)
INSERT Mac_Address (MacAddress)
SELECT Convert(binary(6), N + @StartRange - 1)
FROM Nums
WHERE N <= @EndRange - @StartRange + 1

However, I am not 100% sure this will work in the Express edition of SQL Server 2012. There is actually nothing wrong with using the syscolumns version, you just have to add an INSERT statement and correct a few problems (including int rather than bigint and binary(8) instead of binary(6)). Note that you can get increased performance by adding WITH (NOLOCK) after each syscolumns table (or using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the query and SET TRANSACTION ISOLATION LEVEL READ COMMITTED after). This query performs very well, too (2 seconds on my server).

DECLARE
   @StartRange bigint = 0x0004A500114B,
   @EndRange bigint = 0x0004A50F11FF;

INSERT Mac_Address (MacAddress) -- Add this line
SELECT Convert(binary(6), N + @StartRange - 1) 
FROM
(
    SELECT N = Row_Number() OVER (ORDER BY (SELECT 1))
    FROM
       syscolumns WITH (NOLOCK),
       syscolumns a WITH (NOLOCK),
       syscolumns b WITH (NOLOCK),
       syscolumns c WITH (NOLOCK)
) b
WHERE N <= @EndRange - @StartRange + 1;

Answer 2

The second snippet is not inserting anything at all. It only uses the syscolumns table's (cross joined with itself) row number to generate integers.

You could use any other table for this (provided you have a sufficient row count or join it enough times to generate it)

Answer 3

It is not really using syscolumns. It is only joining it together to create a table with enough rows for your range. If you are uncomfortable with this, you can do something like:

with digits as (select 0 as dig union all
                select 1 union all
                select 2 union all
                select 3 union all
                select 4 union all
                select 5 union all
                select 6 union all
                select 7 union all
                select 8 union all
                select 9
               ),
      nums as (select dig1 + 10*dig2+100*dig3+1000*dig4+10000*dig5+100000*dig6 as num
               from dig dig1 cross join dig dig2 cross join
                    dig dig3 cross join dig dig4 cross join
                    dig dig5 cross join dig dig6
              )
select convert(BINARY(8), num + convert(integer, @StartRange)) 
from nums
where num + 1 between 1 and (convert(integer, @EndRange) - convert(integer, @StartRange))

Answer 4

The following is simple and runs in abount a minute on the online SqlFiddle:

DECLARE @StartRange bigint;
DECLARE @EndRange bigint;
SET @StartRange = 0x0036D1F00000;
SET @EndRange =   0x0036D1FFFFFF; 

WITH cteMacAddress AS (
  SELECT @StartRange MacAddress
  UNION ALL
  SELECT MacAddress+1 FROM cteMacAddress
  WHERE MacAddress <= @EndRange
)
INSERT Mac_Address
  SELECT CAST(MacAddress AS BINARY(6))
  FROM cteMacAddress
  OPTION (MAXRECURSION 0);

SELECT COUNT(*) GeneratedAddressCount
  FROM Mac_Address;