Presents your 
SQL SERVER E-NEWSLETTER for July 30, 2002

<------------------------------------------->

ISSUE A DBCC CHECKIDENT COMMAND

SQL Server automatically maintains the identity property, which you can
use as a primary key column. The seeding and incrementing of values is
determined at creation time. The default seed is 1 and the incremental
value is 1.

However, you can override this at creation time or alter the new seed
value afterwards by issuing a DBCC CHECKIDENT command against the table.
When issuing the DBCC CHECKIDENT command without the reseed value, the next
value will be one plus the maximum value in the table.

Over time, gaps will occur in the sequence of the identity values. It's
prudent to monitor the major identity gaps in these tables and good
practice to check the proximity of the maximum identity value to the maximum
value possible.

This becomes more essential if there's an extremely high amount of
activity against the database. With the int datatype, the maximum possible
value is approximately 2 billion. The identity value may inadvertently be
created larger than it should, which the check of the proximity to the
maximum value would catch. Introduced in SQL Server 2000, the bigint datatype
alleviates the maximum possible value that the int datatype has.

The following is a sample script that illustrates the DBCC CHECKIDENT
command and how gaps are created:

CREATE TABLE x_identity1
(pkey INT IDENTITY NOT NULL
   CONSTRAINT pk_x_identity1 PRIMARY KEY nonclustered,
col1 INT NULL)
GO
INSERT x_identity1 (col1) VALUES ( 32 )
GO
SET IDENTITY_INSERT x_identity1 ON
INSERT x_identity1 (pkey) VALUES (100)
SET IDENTITY_INSERT x_identity1 OFF
GO
INSERT x_identity1 (col1) VALUES ( 1000 )
GO
DBCC CHECKIDENT (x_identity1, RESEED, 1)
GO
INSERT x_identity1 (col1) VALUES ( 2222 )
GO
DBCC CHECKIDENT (x_identity1)
GO
INSERT x_identity1 (col1) VALUES ( 3456 )
GO
SELECT * FROM x_identity1
GO
SELECT COUNT(pkey) NbrRows, 2147483647 - MAX(pkey) HowManyBeforeMaxed,
MAX(pkey) - MIN(pkey) Range,
((MAX(pkey) - MIN(pkey)) * 1.0)/( COUNT(pkey) * 1.0) Density_1IsBest,
MIN(pkey) MinNbr, MAX(pkey) MaxNbr
FROM x_identity1
GO
DROP TABLE x_identity1
GO

----------------------------------------