SQL Server creates table with IDENTITY COLUMN - uniqueness
In SQL Server, I created a table with an ID column in which I did IDENTITY COLUMN,
EmployeeID int NOT NULL IDENTITY(100,10) PRIMARY KEY
As I understand it, when I use the IDENTITY function, it increments the EmployeeID automatically. I don't know / not sure:
- Is this IDENTITY number unique?
- Does SQL check the entire column in the table to confirm the generated number doesn't exist yet?
- Can I override this auto increment number?
- If I were to manually override this number, would the number I entered check to make sure it is not a duplicate / existing identification number?
Thanks for the help provided.
source to share
Is this IDENTITY number unique?
Yes, the Identity property is unique
Does SQL check the entire column in the table to confirm the generated number doesn't exist yet? \
Don't need what this property does, just incrementing the old value
Can I override this auto increment number?
Yes, you can. You must useSET IDENTITY_INSERT TABLENAME ON
If I were to manually override this number, would the number I entered check to make sure it is not a duplicate / existing identification number?
No, this will not be taken care of by SQL Server, you will need to make sure you have restrictions to take care of this
Below is a simple demonstration that
create table #temp
(
id int identity(1,1)
)
insert into #temp
default values
go 3
select * from #temp--now id column has 3
set identity_insert #temp on
insert into #temp (id)
values(4)
set identity_insert #temp off
select * from #temp--now id column has 4
insert into #temp
default values
go
select * from #temp--now id column has 5,next value from the last highest
Updating information from comments:
The id column will allow spaces after re-adding them, and also not updating them
source to share
Q and A
Q1: Is this IDENTITY number created unique?
A: No, according to SQL Server documentation (see Notes ):
The identity property on a column does not guarantee the following:
Uniqueness of value . Uniqueness must be enforced with a PRIMARY KEY or UNIQUE constraint, or a UNIQUE index.
See the demo below to see how easily we can get duplicate values in the IDENTITY column with no index / constraint UNIQUE
or no constraint PRIMARY KEY
.
Q2: Does SQL look for an entire column in the table to confirm the generated number doesn't exist yet?
A: No, according to SQL Server documentation (see Notes ):
The identity property on a column guarantees the following:
Each new value is generated based on the current seed & increment.
This also means that SQL Server will not run before SELECT MAX(ID) + 1 FROM dbo.Table
or to calculate the new ID value SELECT MAX(ID) + @increment_value FROM dbo.Table
.
Q3: Can I override this auto increment number?
A1: If you are trying to update a column that has a property IDENTITY
then the answer is No, we cannot update / redefine the column IDENTITY
(it doesn't matter if SET IDENTITY_INSERT dbo.Table
there is ON
or OFF
)
UPDATE dbo.Test1
SET ID = 1
Msg 8102, Level 16, State 1, Line 75
Cannot update identity column 'ID'.
More, we cannot delete this property ( IDENTITY
) without re-creating the current table.
A2: If by the number "auto increment number" you understand {current seed | "last inserted IDENTITY value"} ( ColumnName DataType IDENTITY(seed, increment)
), then the answer is yes (see demo).
A3: If by "auto increment number" you understand the increment number ( ColumnName DataType IDENTITY(seed, increment)
), then the answer will be.
Q4: If I manually override this number, will the input number be sure it is not a duplicate / existing ID?
See answers for Q1 and Q4.
Demo:
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.Test1') IS NOT NULL BEGIN DROP TABLE dbo.Test1 END
GO
CREATE TABLE dbo.Test1 (
ID INT IDENTITY(2, 3), -- There is no UNIQUE index / constraint or PRIMARY KEY constraint defined on ID column
Col1 VARCHAR(50)
)
GO
INSERT dbo.Test1 (Col1) VALUES ('A'), ('B'), ('C')
SELECT * FROM dbo.Test1
/*
Output:
ID Col1
----------- ----
2 A
5 B
8 C
*/
GO
-- Method 1 - duplicated values - reseed
DBCC CHECKIDENT('dbo.Test1', RESEED, 2)
/*
Output:
Checking identity information: current identity value '8'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
INSERT dbo.Test1 (Col1) VALUES ('D')
SELECT * FROM dbo.Test1
/*
ID Col1
----------- ----
2 A
5 B
8 C
5 D <-- New row
*/
GO
-- Method 2 - duplicated values - insert duplicated values
SET IDENTITY_INSERT dbo.Test1 ON
GO
INSERT dbo.Test1 (ID, Col1) VALUES (2, 'E'), (8, 'F')
SELECT * FROM dbo.Test1
/*
ID Col1
----------- ----
2 A
5 B
8 C
5 D
2 E <-- New row
8 F <-- New row
*/
GO
-- Method 3 - duplicated values - insert duplicated values
INSERT dbo.Test1 (ID, Col1) VALUES (1, 'A'), (1, 'AA')
SELECT * FROM dbo.Test1
/*
ID Col1
----------- ----
2 A
5 B
8 C
5 D
2 E
8 F
1 A <-- New row
1 AA <-- New row
*/
GO
SET IDENTITY_INSERT dbo.Test1 OFF
GO
source to share