Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Reset the seed of an identity
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Divers
Thread ID:
00920053
Message ID:
00920094
Vues:
15
Well it is a shortcut installed with SQL server.
Or you can go to the windows msdn and read it there.
Use generic syntax for finding gaps in identity values
This example shows generic syntax for finding gaps in identity values when data is removed.

Note  The first part of the following Transact-SQL script is designed for illustration purposes only. You can run the Transact-SQL script that starts with the comment: - - Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- This is the beginning of the illustrative example.
SET IDENTITY_INSERT tablename ON

DECLARE @minidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN(IDENTITYCOL) FROM tablename
IF @minidentval = IDENT_SEED('tablename')
   SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tablename')
   FROM tablename t1
   WHERE IDENTITYCOL BETWEEN IDENT_SEED('tablename') AND 
      MAX(column_type) AND
      NOT EXISTS (SELECT * FROM tablename t2
         WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + 
            IDENT_INCR('tablename'))
ELSE
   SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column 
-- called id_num, which is an increasing identification number, and the 
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'img')
   DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN(IDENTITYCOL) FROM img
 IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('img')
    FROM img t1
    WHERE IDENTITYCOL BETWEEN IDENT_SEED('img') AND 32766 AND
      NOT    EXISTS (SELECT * FROM img t2
          WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('img'))
 ELSE
    SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF
Shawn Dorion
Geo Sektor Dot Com
Website: http://www.geosektor.com

Web Hosting Plans
Visit : http://WebHosting.Applications4u.com/
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform