SQL Server chooses the highest number as its current seed for a positive increment value or the lowest for a negative increment value : IDENTITY « Sequence « SQL Server / T-SQL






  1. SQL Server / T-SQL
  2. Sequence
  3. IDENTITY

SQL Server chooses the highest number as its current seed for a positive increment value or the lowest for a negative increment value


1>
2> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL
3>                       , MyDescription nVarChar(50) NOT NULL)
4>
5> INSERT MyTable (MyDescription) VALUES ('Auto Record 1')
6> INSERT MyTable (MyDescription) VALUES ('Auto Record 2')
7>
8>
9> -- Now you explicitly enter a MyID value with the following script:
10>
11> SET IDENTITY_INSERT MyTable ON INSERT MyTable (MyID, MyDescription)
12> VALUES (5, 'Manual Record 1') SET IDENTITY_INSERT MyTable OFF
13>
14> -- SQL Server will always choose the highest number as its current seed for a positive -- increment value or the lowest for a negative increment value
15>
16> INSERT MyTable (MyDescription)
17> VALUES ('Auto Record 3')
18>
19> SELECT * FROM MyTable
20>
21> drop table MyTable
22> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
MyID        MyDescription
----------- --------------------------------------------------
          1 Auto Record 1
         11 Auto Record 2
          5 Manual Record 1
         21 Auto Record 3

(4 rows affected)
1>








Related examples in the same category

1.IDENTITY (1, 1)
2.Identity starting with -1000000
3.SQL Server does not allow explicit values to be inserted into it
4.Now you explicitly enter a value for Identity column
5.Using Identity during data insert
6.Using IDENTITY value
7.Get current IDENTITY value
8.Seed value was negative and the increment was positive
9.Created with an IDENTITY property that is set to start at 1,000,000 and decrement by 100 for every row added
10.IDENTITY [ (seed , increment ) ]
11.IDENTITY as PRIMARY KEY