Sql-server – SQL Server IDENTITY column automatic re-seed

Just an UPDATE using EXISTS will be your best bet:

UPDATE c
SET HadSeconds = 1
FROM Caketable C
WHERE EXISTS (SELECT 1
              FROM CakeTable C2
              WHERE c2.personid = c.personid
              AND c2.date > DATEADD(day, -2, c.date))

If you think this will run too long, you can always break it down by date range, cake type, etc to do smaller batches.

If you have a composite index on personid, date this should be pretty fast.

You could also do a self-join but I'm pretty confident the EXISTS will run a lot faster:

UPDATE c
SET HadSeconds = 1
FROM Caketable C
INNER JOIN CakeTable C2
  ON c2.personid = c.personid
  AND c2.date > DATEADD(day, -2, c.date)

The easiest way would be to start a new table. Inject the 30,000 rows you want to keep, then drop the old table and rename the new table.

BEGIN TRANSACTION;

CREATE TABLE dbo.copy_of_foo
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  col2 ..., ...
);

INSERT dbo.copy_of_foo(col2, ...) 
  SELECT TOP 30000 col2, ...
  FROM dbo.foo
  ORDER BY ...;

DROP TABLE dbo.foo;

EXEC sp_rename N'dbo.copy_of_foo', N'foo', N'OBJECT';

COMMIT TRANSACTION;

You may need to nest the order by and add OPTION (MAXDOP 1) if you want to ensure that you get the 30K most recent rows and the IDENTITY values are assigned in order from oldest to newest, e.g. hopefully you have some other column, such as a date time column, that can help identify the 30000 rows you want to keep:

INSERT dbo.copy_of_foo(col2, ...)
  SELECT col2, ...
  FROM 
  (
    SELECT TOP 30000 col2, ...
      FROM dbo.foo 
      ORDER BY date_time_column DESC
  ) AS x
  ORDER BY date_time_column
  OPTION (MAXDOP 1);

Keep in mind though that if someone reported an exception yesterday with row #42,000,564, good luck finding it today. As I suggested in the comment, maybe you shouldn't be exposing these obviously-otherwise-absolutely-meaningless surrogate identifiers to users.