IDENTITY COLUMN : It Automatically generate key values.
IDENTITYCOL: This Keyword automatically refers to the IDENTITY column of the table.
-- IDENTITY COLUMN : It Automatically generate key values.
-- CREATE a Dev table:
CREATE TABLE Dev
(
ID INT IDENTITY (1, 1),
NAME VARCHAR (5)
);
--ID value start with 1 and increase by 1.
-- INSERT some records:
INSERT INTO Dev (NAME)
SELECT 'Nancy'
INSERT INTO Dev (NAME)
SELECT 'Disha'
INSERT INTO Dev (NAME)
SELECT 'Carol'
GO
-- Check inserted records:
SELECT * FROM Dev -- 3 records, with ID value 1, 2, 3.
GO
/* Output :
ID NAME
1 Nancy
2 Disha
3 Carol
*/
-- IDENTITYCOL: This Keyword automatically refers to the IDENTITY column of the table.
SELECT * FROM Dev
WHERE IDENTITYCOL>1
/* Output : by using IDENTITYCOL we can filter the records
ID NAME
2 Disha
3 Carol
*/
-- inserting new duplicate name " Carol " but ID unique.
INSERT INTO Dev (NAME)
SELECT 'Carol'
SELECT * FROM Dev ;
/* Output :
ID NAME
1 Nancy
2 Disha
3 Carol
4 Carol
*/
-- Creating group of name and counting IDENTITYCOL
SELECT NAME, COUNT (IDENTITYCOL) as Total FROM Dev
GROUP BY NAME
/* output
NAME Total
Carol 2
Disha 1
Nancy 1
*/
SELECT IDENTITYCOL FROM Dev;
/* output
ID
1
2
3
4
*/
-- CREATE a Dev table:
CREATE TABLE Dev
(
ID INT IDENTITY (1, 2),
NAME VARCHAR (5)
);
-- INSERT some records:
INSERT INTO Dev (NAME)
SELECT 'Nancy'
INSERT INTO Dev (NAME)
SELECT 'Disha'
INSERT INTO Dev (NAME)
SELECT 'Carol'
GO
SELECT * FROM Dev;
/* output : ID start with 1 and increase by 2.
ID NAME
1 Nancy
3 Disha
5 Carol
*/
Happy Coding….. Thanks
