Ummm, there's a method that works on 10M+ row tables, which UDF's don't
really.
This method may be overkill, but the big joins are strictly
MERGE JOINS, which have very predictable performance.

For production batches, I always include audit queries to assure
everyone that IDENTITY really does produce contiguous integers
in exactly the right sequence. Haven't been disappointed yet ...
----------------------------------------------------------------------------
---------------------
-- Assume TABLE ZipMap(zipcode varchar(9), code varchar(9));
-- doesn't really matter what their types are

-- Number the rows sequentially, by ascending zip code.
CREATE UNIQUE CLUSTERED INDEX uc_zipcode ON ZipMap(zipcode)
SELECT IDENTITY(INT,1,1) AS seq, * INTO ZipMap2 FROM ZipMap

-- Step 1: find the boundaries between runs
-- (azip...) is the end of a run, (bzip...) is the start of the next run

CREATE UNIQUE CLUSTERED INDEX uc_seq ON ZipMap2(seq)
SELECT A.zipcode AS azip, A.code AS acode,
B.zipcode AS bzip, B.code AS bcode
INTO ZipMap3
FROM ZipMap2 A
JOIN ZipMap2 B ON B.seq = A.seq+1 AND B.code <> A.code

-- Don't forget the start of the very first run, and the end of the last
run:
INSERT ZipMap3
SELECT TOP 1 '', '', zipcode, code
FROM ZipMap2 ORDER BY seq

INSERT ZipMap3
SELECT TOP 1 zipcode, code, 'zzz', ''
FROM ZipMap2 ORDER BY seq DESC

-- Step 2: sequentially number each (end,begin) range,
-- so you can match it up with following row:
CREATE UNIQUE CLUSTERED INDEX uc_azip ON ZipMap3(azip)
SELECT IDENTITY(INT,1,1) AS seq, * INTO ZipMap4 FROM ZipMap3

CREATE UNIQUE CLUSTERED INDEX uc_seq ON ZipMap4(seq)

SELECT A.bzip AS ZipStart, B.azip AS ZipEnd, A.bcode AS Code
FROM ZipMap4 A JOIN ZipMap4 B ON B.seq = A.seq+1

[quoted text, click to view]