SQL SERVER BASICS TUTORIAL-PART 2

/*
  1. Identity column
  2. How to create the table with identity column
  3. How to reset the idenity column

*/
--------------------------------------------------------------------------------------------------------------------------

  Creating a table with identity column:-

 
USE [TestDB]
GO

/****** Object:  Table [dbo].[Customer]    Script Date: 09/25/2014 12:02:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Age] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


--------------------------------------------------------------------------------------------------------------------------

/*
  Identity column: Once we have the identity column ,we can't supply any values to that column...if you try to do so ,it will throw error..

  Now try to insert some values into the IDENTITY COLUMN AND SEE WHAT HAPPENS......
*/

--------------------------------------------------------------------------------------------------------------------------
INSERT INTO Customer(CustomerID,FirstName,LastName,Age) VALUES (1,'SENTHIL','DHAKSHNA',33)--WILL FAIL...AND IT WILL THROW ERROR AS SHOWN BELOW..

Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

SO IF YOU WANT TO INSERT SOME RECORD YOU SHOULD NOT SUPPLY ANY VALUE FOR THE IDENTITY COLUMN..IT WILL BE AUTOMATICALLY GENERATED..

INSERT INTO Customer(FirstName,LastName,Age) VALUES ('SENTHIL','DHAKSHNA',33)

SELECT * FROM Customer

NOW TRY TO INSERT SOME MORE RECORDS ---

INSERT INTO Customer(FirstName,LastName,Age) VALUES ('SENTHIL1','DHAKSHNA1',32)
INSERT INTO Customer(FirstName,LastName,Age) VALUES ('SENTHIL2','DHAKSHNA2',31)



SELECT * FROM Customer

/*  RESULT:-
1 SENTHIL DHAKSHNA 33
2 SENTHIL1 DHAKSHNA1 31
3 SENTHIL2 DHAKSHNA2 32
*/
--------------------------------------------------------------------------------------------------------------------------
            NOW I AM GOING TO DELETE A RECORD A CUSTOMER TABLE WITH CUSOTMERID=1..SUPPLY THE FOLLOWING COMMAND FOR THAT..

DELETE FROM Customer WHERE CustomerID=1

NOW SELECT THE RECORD FROM TABLE...

SELECT * FROM Customer

/*  RESULT:-
2 SENTHIL1 DHAKSHNA1 31
3 SENTHIL2 DHAKSHNA2 32
*/

-----------------------------------------------------------------------------------------------------------------------------------------------
NOW TRY TO INSERT A NEW RECORD INTO THE TABLE..

INSERT INTO Customer(FirstName,LastName,Age) VALUES ('SENTHIL3','DHAKSHNA3',30)


NOW SELECT THE RECORD FROM TABLE...

SELECT * FROM Customer

/*  RESULT:-
2 SENTHIL1 DHAKSHNA1 31
3 SENTHIL2 DHAKSHNA2 32
4 SENTHIL3 DHAKSHNA3 30
    WHENEVER I TRY TO ADD A NEW RECORD , IT WILL BE INSERTED 4,5,6..SOMETHING LIKE THIS ONLY AS SHOWN ..BUT MY INTENTION IS
    I NEED TO INSERT A NEW RECORD WITH CUSTOMERID AS 1..BUT WE CAN'T DO THAT RIGHT NOW..BCOZ WE HAVE METNIONED THE CUSTOMERID
    COLUMN AS IDENITY COLUMN..ALREADY WE KNOW WE CANT SUPPLY EXPLICIT VALUE FOR THE IDENTITY COLUMN..
 
*/
-----------------------------------------------------------------------------------------------------------------------------------------------
IF YOU TRY TO DO SO,IT WILL THROW ERROR...
INSERT INTO Customer(CustomerID,FirstName,LastName,Age) VALUES (1,'SENTHIL4','DHAKSHNA4',33)

Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.
/*
  LOOK AT THE ABOVE ERROR MESSAGE WHAT IT SAYS..
  Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF
  SO IF  YOU WANT TO INSERT THE VALUE EXPLICITLY INTO THE IDENTITY COLUMN, WE NEED TO TURN ON THE "IDENTITY_INSERT" FOR THE CUSOTMER TABLE..

*/

TURN ON THE "IDENTITY_INSERT" FOR THE CUSOTMER TABLE AND TRY ...

SET IDENTITY_INSERT Customer ON

NOW RUN THE FOLLOWING COMMAND--

INSERT INTO Customer(CustomerID,FirstName,LastName,Age) VALUES (1,'SENTHIL4','DHAKSHNA4',33)

SELECT * FROM Customer

/*  RESULT:-

1 SENTHIL4 DHAKSHNA4 33
2 SENTHIL1 DHAKSHNA1 31
3 SENTHIL2 DHAKSHNA2 32
4 SENTHIL3 DHAKSHNA3 30

  Actually Our intention was we had to insert the record with customerid =1 ..that has been finished..Now if u try to insert
  the record without identity column value(i.e without CustomerID) , it won't allow...bcoz we turned on the IDENTITY_INSERT already..
  suppose if u don't wish to supply anything for CustomerID column, then we need to turn off the IDENTITY_INSERT for the customer table..

 
*/
-----------------------------------------------------------------------------------------------------------------------------------------------
Turning off the IDENTITY_INSERT for the customer table..

SET IDENTITY_INSERT Customer OFF
NOW RUN THE FOLLOWING COMMAND--
INSERT INTO Customer VALUES ('SENTHIL5','DHAKSHNA5',34)

SELECT * FROM Customer

/*  RESULT:-

1 SENTHIL4 DHAKSHNA4 33
2 SENTHIL1 DHAKSHNA1 31
3 SENTHIL2 DHAKSHNA2 32
4 SENTHIL3 DHAKSHNA3 30
5 SENTHIL5 DHAKSHNA5 34

*/

--------------------------------------------------------------------------------------------------------------------------
 NOW DELETING ALL THE RECORDS FROM CUSTOMER TABLE ,THEN TRY TO INSERT NEW RECORD INTO THE CUSTOMER TABLE AND SEE  WHAT HAPPENS..
DELETE FROM Customer

SELECT * FROM Customer --THERE ARE NO RECORDS IF YOU RUN IN THIS COMMAND NOW...

TRY TO INSERT NEW RECORD INTO THE CUSTOMER TABLE --

INSERT INTO Customer VALUES ('SENTHIL6','DHAKSHNA6',34)

SELECT * FROM Customer

/*  RESULT:-

 6 SENTHIL6 DHAKSHNA6 34

 YOU MIGHT  EXPECT  CUSTOMERID WOULD BE 1 AS THERE ARE NO RECORDS IN THE TABLE PREVIOUSLY..BUT IT WILL START WITH 6..I.E . IDENTITY COLUMN(CUSTOMERID FIELD) IS NOT GET RESET..
 TO SOLVE THIS WE HAVE SOME OTHER OPTION CALLED..DBCC(DATABASE CONSISTENCY CHECKER) COMMANDS...

*/

 DELETE FROM Customer

SELECT * FROM Customer --NO RECORDS FOUND NOW..
TO RESET THE VALUE FOR CUSTOMER ID COLUMN---
DBCC CHECKIDENT(CUSTOMER,RESEED,0)--YOU WILL GET THE BELOW MESSAGE AFTER RUNNING THIS COMMAND--

Checking identity information: current identity value '0', current column value '0'.
IF YOU LOOK AT THE ABOVE MESSAGE YOU CAN IDENTIFY THAT IDENTITY AND COLUMN VALUE IS RESET TO 0.
NOW IF YOU TRY TO INSERT ANY RECORD ,CUSTOMERID  WILL START FROM 1 ONLY..

INSERT INTO Customer VALUES ('SENTHIL6','DHAKSHNA6',34)

SELECT * FROM Customer

/*  RESULT:-
 
   1 SENTHIL6 DHAKSHNA6 34
*/
--------------------------------------------------------------------------------------------------------------------------