DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
     -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
     -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
     --        for all rows.
     -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
     -- Jeff Moden

 SELECT TOP 1000000
        RowNum       = IDENTITY(INT,1,1),
        SomeInt      = ABS(CHECKSUM(NEWID()))%50000+1,
        SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        SomeCSV      = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
        SomeMoney    = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        SomeDate     = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        SomeHex12    = RIGHT(NEWID(),12)
   INTO dbo.JBMTest
   FROM Master.dbo.SysColumns t1,
        Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE dbo.JBMTest
        ADD PRIMARY KEY CLUSTERED (RowNum)

INSERT INTO dbo.JBMTest
	(SomeLetters2)
SELECT TOP 1000
 CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65)
  FROM Master.dbo.SysColumns t1,
    Master.dbo.SysColumns t2 

select *
from dbo.JBMTest
except 
select * 
from dbo.JBMTest

select *
from dbo.JBMTest as T1
where	 NOT EXISTS
	(select 1
	from	dbo.JBMTest as T2
	where	T2.RowNum	= T1.RowNum
	and	T2.SomeInt	= T1.SomeInt
	and	T2.SomeCSV	= T1.SomeCSV
	and	T2.SomeMoney	= T1.SomeMoney
	and	T2.SomeDate	= T1.SomeDate
	and	T2.SomeHex12	= T1.SomeHex12
	)