One simple question that used
to strike me is whether we can add a column to a table
and write a procedure or use any other method so that we can assign each row of the column with a
different value like a Serial No 1,2,3,4…
Also Check Out-
The answer to the above question is the below post –
YES, we can add a column, that is an identity column to an existing table with
records, when we add the new identity column on the table then the column will have values like a Serial No 1,2,3…
Syntax-
IDENTITY [ (seed, increment) ]
here,
seed - is the value that is used for the very first row
loaded into the table.
Increment - Is the incremental value that is added to
the identity value of the previous row that was loaded.
--CREATE THE DATABASE - TestDatabase, IF IT DOES NOT
EXISTS
IF NOT EXISTS(SELECT * FROM master.sys.sysdatabases WHERE name='TestDatabase')
BEGIN
CREATE DATABASE
TestDatabase
END
--DROP THE TABLE - TestTableTemp, IF IT EXISTS
IF OBJECT_ID('TestDatabase.dbo.TestTableTemp', 'U') IS NOT NULL
BEGIN
DROP TABLE
TestDatabase.dbo.TestTableTemp;
END
--CREATE THE TABLE – TestTableTemp
CREATE TABLE TestDatabase.dbo.TestTableTemp(
FirstName nvarchar(max) NULL,
LastName nvarchar(max)NULL,
Age int NULL,
)
--Insert Dummy
Values Into The Table - TestTableTemp
INSERT INTO TestDatabase.dbo.TestTableTemp(FirstName,LastName,Age)
VALUES ('Alpha','A','20'),
('Bravo','B','33'),
('Charlie','C','23'),
('Delta','D','23')
--ADD COLUMN
SLNo, THIS COLUMN IN THE IDENTITY COLUMN TO THE TABLE TestTableTemp
ALTER TABLE TestTableTemp
ADD SLNo Bigint IDENTITY(1,1)
--WE CAN SEE THAT IDENTITY VALUES HAVE BEEN ADDED TO
THE COLUMN SLNo
SELECT * FROM TestTableTemp
--CLEANUP SCRIPTS – DELETE THE TestDatabase
DROP DATABASE TestDatabase
Note –
1) We can have only one Identity column per table, If we
try to add more Identity columns then we get the following error
Error Description -
Multiple identity columns specified for table
''. Only one identity column per table is allowed.
2) If we want the values to start from other no like 100
instead of 1 then we can do it by changing the seed value for the Identity
property –
(Delete the existing column SLNo to add it again as
below)
ALTER TABLE TestTableTemp
ADD SLNo bigint IDENTITY(100,1)
3) If we want the values in the identity column to
increment by more than 1 then we can do it by changing the reseed value for the
Identity property –
(Delete the existing column SLNo to add it again as
below)
ALTER TABLE TestTableTemp
ADD SLNo bigint IDENTITY(1,4)
Further Reading –
IDENTITY (Property)
(Transact-SQL)
Also Check Out-
1) Create a New table by copying all rows or column data from an Existing table
2) Using DBCC CHECKIDENT to Fetch current Identity column value of a table or Reset identity column value of a table in Sql Server / DBCC reseed in sql server
No comments:
Post a Comment