none
各位遇到过identity跳号的情况没有? RRS feed

  • 问题

  • 各位遇到过identity跳号的情况没有?没有执行删除操作。据说2012开始有这方面的变化,是不是出于性能的考虑。
    2014年11月25日 7:49

答案

  • 各位遇到过identity跳号的情况没有?没有执行删除操作。据说2012开始有这方面的变化,是不是出于性能的考虑。

    IDENTITY
    如果在经常进行删除操作的表中存在着标识列,那么在标识值之间可能会有间隔。如果这是要考虑的问题,那么请不要使用 IDENTITY 属性。但是,为了确保未产生间隔,或者填补现有的间隔,在用 SET IDENTITY_INSERT ON 显式输入标识值之前,请先对现有的标识值进行计算。

    要实现不断号要用补号的方法,在MSDN查看断号例子

    IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
       DROP TABLE img
    GO
    CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
    INSERT img(company_name) VALUES ('New Moon Books')
    --回滚
    BEGIN TRAN
    INSERT img(company_name) VALUES ('Lucerne Publishing')
    ROLLBACK
    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($IDENTITY) FROM img
     IF @minidentval = IDENT_SEED('img')
        SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
        FROM img t1
        WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
          NOT    EXISTS (SELECT * FROM img t2
              WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
     ELSE
        SELECT @nextidentval = IDENT_SEED('img')
    SET IDENTITY_INSERT img OFF
    
    
    SELECT * FROM img
    /*
    id_num	company_name
    1	New Moon Books
    3	Lucerne Publishing
    */
    SELECT @nextidentval --id_num(2)


    SQL2012--序列号

    CREATE SEQUENCE Schema.SequenceName
        AS int
        START WITH 1
        INCREMENT BY 1 ;

    http://msdn.microsoft.com/zh-cn/library/ff878058(v=sql.110).aspx



    Roy Wu(吳熹Blog)(微博)

    2014年11月29日 3:57
    版主

全部回复

  • 有沒有可能是因為中間有ROLLBACK而導致跳號呢?可以用下列的T-SQL來模擬這種狀況。

    use tempdb
    go
    if exists (select * from sys.tables where name = 't1')
        drop table t1
    go
    
    create table t1
    (c1 int identity,c2 varchar(10))
    go
    
    --第一次新增10筆資料
    insert into t1(c2) values ('test')
    go 10
    
    dbcc checkident('t1')
    
    --新增3筆資料然後故意Rollback
    begin tran
    insert into t1(c2) values ('test')
    go 3
    rollback tran
    go
    
    --已經造成跳號了,下一筆序號會從14號開始
    dbcc checkident('t1')
    
    
    insert into t1(c2) values ('ident')
    go
    
    select *
    from t1
    go
    參考資料:https://social.msdn.microsoft.com/forums/sqlserver/en-US/750ac939-b0fb-4ae1-8ce0-d3c1edec5697/identity-column-not-getting-sequential-values

    | SQL PASS Taiwan Page | SQL PASS Taiwan Group

    | My Blog

    2014年11月25日 8:52
  • the same as this thread :

    https://social.technet.microsoft.com/Forums/zh-CN/65ff4cb5-0a33-4d6b-b332-2ac9a728dcac/sql?forum=sqlserverzhchs

    read this thread pls 

    2014年11月25日 9:26
  • 没有测试出来 难道2014版又改回去了!
    2014年11月26日 1:31
  • Sql2014 works same way as in sql2012.
    2014年11月26日 3:01
  • 各位遇到过identity跳号的情况没有?没有执行删除操作。据说2012开始有这方面的变化,是不是出于性能的考虑。

    IDENTITY
    如果在经常进行删除操作的表中存在着标识列,那么在标识值之间可能会有间隔。如果这是要考虑的问题,那么请不要使用 IDENTITY 属性。但是,为了确保未产生间隔,或者填补现有的间隔,在用 SET IDENTITY_INSERT ON 显式输入标识值之前,请先对现有的标识值进行计算。

    要实现不断号要用补号的方法,在MSDN查看断号例子

    IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
       DROP TABLE img
    GO
    CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
    INSERT img(company_name) VALUES ('New Moon Books')
    --回滚
    BEGIN TRAN
    INSERT img(company_name) VALUES ('Lucerne Publishing')
    ROLLBACK
    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($IDENTITY) FROM img
     IF @minidentval = IDENT_SEED('img')
        SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
        FROM img t1
        WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
          NOT    EXISTS (SELECT * FROM img t2
              WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
     ELSE
        SELECT @nextidentval = IDENT_SEED('img')
    SET IDENTITY_INSERT img OFF
    
    
    SELECT * FROM img
    /*
    id_num	company_name
    1	New Moon Books
    3	Lucerne Publishing
    */
    SELECT @nextidentval --id_num(2)


    SQL2012--序列号

    CREATE SEQUENCE Schema.SequenceName
        AS int
        START WITH 1
        INCREMENT BY 1 ;

    http://msdn.microsoft.com/zh-cn/library/ff878058(v=sql.110).aspx



    Roy Wu(吳熹Blog)(微博)

    2014年11月29日 3:57
    版主