dwain.c (8/28/2012)
I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:
CREATE TABLE #t (ID BIGINT IDENTITY(201208280001, 1), value INT)
INSERT INTO #t SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
IF LEFT(IDENT_CURRENT('#t'), 8) <> CONVERT(VARCHAR(8), GETDATE(), 112)
BEGIN
DECLARE @newseed BIGINT = CAST(CONVERT(VARCHAR(8), GETDATE(), 112)+'0000' AS BIGINT)
DBCC CHECKIDENT('#t', RESEED, @newseed)
END
INSERT INTO #t SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
SELECT * FROM #t
DROP TABLE #t
That's seriously clever especially since it solves the multi-row insert problem that most people experience with custom daily dequences but what happens if the number of inserts exceeds 99,999 rows in a day? It won't even warn you that it did a wrap around. And, as fast as it is, there's still the possibility of someone getting in between the DECLARE and the DBCC.
What about inserts that last from slightly before midnight to slightly after midnight? If you want them to accurately reflect the date they were inserted into the table, you'll be disappointed. Of course, the same would be true of you had a default of GETDATE() on the table.
--Jeff Moden
Change is inevitable... Change for the better is not.