question

Well I don't know how to give you what you've asked for, but I'm pretty sure I can give you what you want:

CREATE TABLE [#numbered_groups] (
  [ip_start] [bigint] NOT NULL,
  [country_code] [char] (2) COLLATE Latin1_General_CI_AS NULL,
  [row] [int] PRIMARY KEY CLUSTERED
) 

INSERT INTO [#numbered_groups] ([ip_start], [country_code], [row])
SELECT [ip_start],
       [country_code],
       ROW_NUMBER() OVER (ORDER BY [ip_start]) AS [row] 
FROM [dbo].[ip_group_country]

SELECT [gc].[ip_start],
       [gc].[country_code] FROM [#numbered_groups] [gc] LEFT OUTER JOIN [#numbered_groups] [gc1]
ON gc1.[row] = [gc].[row] - 1
WHERE [gc].[country_code] != ISNULL([gc1].[country_code], '')
ORDER BY [gc].[ip_start]

DROP TABLE [#numbered_groups]

One really bad approach is to use a SELECT INTO query with the IDENTITY function and an ORDER BY clause. Because of a quirk of the optimiser, The identity values are not guaranteed to have the same order as the order that is provided by the ORDER BY clause. It may work, but it may not. Even if you use TOP or SET ROWCOUNT, it doesn't guarantee the order of the rows.

If you either use Matt's approach, or create a table that contains a column with the IDENTITY property and then run an INSERT .. SELECT … GROUP BY ORDER BY query to populate this table, you'll get the correct sequence. As far as I know, there is no performance advantage either way. However, I'm not sure that every country enjoys an unbroken sequence in the table. Have you checked that?

What a dilema for me... Scot has been 100% truthful about what this is for but what this is for is a contest with a high value reward for winning. I usually believe in rewarding absolute honesty but, in this case, I could be giving someone a unfair advantage in a contest.

Scot... my apologies. If you're still interested in the answer after the challenge, post back and I'll take a crack at it (I have a super fast "gap" finder that we can warp). I will give you a "Google Hint" though... search for "SQL ISLANDS AND GAPS". Just be careful... some of the methods are comparatively slow.

--Jeff Moden

Matt,

On the subject of what to use the IDENTITY function for...

First, here's the link that talks about the order of SELECT/INTO with an IDENTITY function no being guaranteed...

link text

The problem with that fine article is... they don't show any code that actually fails. They only say that they could fail "in one of three ways". If you use OPTION(MAXDOP 1) to prevent parallelism, then none of the demonstration code posted on that article will ever fail.

Further, it looks like they fixed some major problems that occurred in SQL Server 2000 when they came out with SQL Server 2005. The following code proves that an ordered SELECT/INTO with an IDENTITY function can fail in SQL Server 2000 but works correctly in SQL Server 2005.

--===== Conditionally drop any temp tables to simplify doing reruns of the code
     IF OBJECT_ID('TempDB..#Insert') IS NOT NULL
        DROP TABLE #Insert  

     IF OBJECT_ID('TempDB..#SelectInto') IS NOT NULL
        DROP TABLE #SelectInto  

     IF OBJECT_ID('TempDB..#Data') IS NOT NULL
        DROP TABLE #Data  

     IF OBJECT_ID('TempDB..#Tally') IS NOT NULL
        DROP TABLE #Tally  

--===== Create and populate a temporary Tally table on the fly
 SELECT TOP 11001
        IDENTITY(INT,0,1) AS N
   INTO #Tally
   FROM Master.dbo.SysColumns sc1
  CROSS JOIN Master.dbo.SysColumns sc2  

--===== Add a Primary Key to maximize performance
  ALTER TABLE #Tally
    ADD PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100  

--===== Create some known, stable test data
 SELECT CAST(t1.N AS TINYINT)  AS SomeValue,
        CAST(t2.N AS INT)      AS SomeID,
        CAST(t3.N AS SMALLINT) AS SomeOtherID
   INTO #Data
   FROM #Tally t1,
        #Tally t2,
        #Tally t3
  WHERE t1.N BETWEEN 1 AND 10
    AND t2.N BETWEEN 1 AND 10
    AND t3.N BETWEEN 1 AND 10  

--===== Precreate the table that will use only INSERT
 CREATE TABLE #Insert
        (
        RowNum      INT IDENTITY(1,1),
        SomeValue   TINYINT,
        SomeID      INT,
        SomeOtherID SMALLINT,
        RefDate     DATETIME
        )  

--===== Do the ordered SELECT/INTO using the IDENTITY function
 SELECT IDENTITY(INT,1,1) AS RowNum,
        SomeValue,
        SomeID,
        SomeOtherID,
        GETDATE() AS RefDate
   INTO #SelectInto
   FROM #Data
  ORDER BY SomeValue, SomeID, SomeOtherID, GETDATE()
 OPTION (MAXDOP 1)  

--===== Do the ordered INSERT into an established table
 INSERT #Insert (SomeValue,SomeID,SomeOtherID,refdate)
 SELECT SomeValue,
        SomeID,
        SomeOtherID,
        GETDATE()
   FROM #Data 
  ORDER BY SomeValue, SomeID, SomeOtherID, GETDATE()
 OPTION (MAXDOP 1)  

--===== Show any differences between the two tables
 SELECT i.*, si.*
   FROM #Insert i
  INNER JOIN #SelectInto si 
     ON si.SomeValue   = i.SomeValue 
    AND si.SomeID      = i.SomeID
    AND si.SomeOtherID = i.SomeOtherID
  WHERE i.RowNum      <> si.RowNum

If you comment out the GETDATE() in the SELECT/INTO in ORDER BY, then it works correctly in SQL Server 2000, as well. The key here is that including GETDATE() in the ORDER BY is a computational mistake... since it will give the same value for all rows, there's just no sense in including it. GETDATE() is also "indeterminent" and unless you're trying to get a random order with something like NEWID(), I would never include an indeterminent function in an ORDER BY because that's a programming mistake the could screw up the order.

SELECT/INTO with an IDENTITY can also go out of order across a linked server and when using the OUTPUT clause and they both have work arounds. For the linked server problem, the work around is, in fact, to precreate the target table (which also solves another problem I won't get into here). For the problem that occurs with the OUTPUT clause, you must simply reference one of the columns actually being inserted and not just the IDENTITY column.

Just like the "quirky update", people will claim that SELECT/INTO with an IDENTITY could break with the next CU or SP so why use it? The answer is incredible speed. SELECT/INTO is twice as fast as an INSERT into an existing table and if you follow "the rules", it won't break once established. Still, a CU or SP could break it. So what do you do? The answer is either give up the speed and use an INSERT into an existing table or do the thing that everyone should do... before you install a CU, Hot Fix, or SP in production, you absolutely should do full regression testing on a test server. Even well documented and well established features can break or change without warning "in the interest of security".

As a side bar, I've been using SELECT/INTO with the IDENTITY function since SQL Server 2000 came out and have never had a problem with it. Of course, I don't do anything with it that doesn't make sense like doing an ORDER BY that includes an indeterminent function and I always use OPTION(MAXDOP 1). Use of "Black Arts" techniques is NOT for casual users... if someone doesn't want to do the necessary research and extreme testing or they're going to do willy-nilly upgrades of SQL Server with CUs, Hot Fixes, and SPs, then they should follow the generally accepted "safe thing to do". Me? I'll never forsake accuracy for speed but I'll definitely go for the speed if I've found no problems with accuracy. I've also found that CUs, Hot Fixes, and SPs don't affect "features" like this except in a good way... the optimizer has been getting better and better.

--Jeff Moden

Sorry about the formatting in the code... like so many forums, this one eats blank lines. It's a real aggravation and I wish the makers of technical forums like this one would figure out that people do take pride in the formatting of their code and accomodate them for once. Who ever picked out the colorization in the code needs to be taken out for a nice pork chop dinner.

--Jeff Moden