SQL SERVER BASICS TUTORIAL -PART 5

* Indexes are used by the query to retrieve the data very quickly. We can create the index on both the tables and views.

                * Indexes are just like the index in the book. In case of book if u want to go for the specific chapter, first we look up the page number in the index then we will go for the specific page to locate that chapter. if suppose u don't have the page index, we need to look up the each and every page to locate  the specific chapter right.

                Consider that we are having a table with millions of records and i need to find the records by specific column and also we don't have the index on  that column   then what will happen is it will scan each and every row from the table. So this type of table scan will definitely hurt the performance right.

 

 

*/

 Run the below command to create the customerinfo table without idenity column :-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[CustomerInfo](

                [CustomerID] [int] NOT NULL,

                [FirstName] [nvarchar](50) NULL,

                [LastName] [nvarchar](50) NULL,

                [Age] [int] NULL,

                [Gender] [varchar](10) NULL,

 CONSTRAINT [PK_CustomerInfo] 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

SET ANSI_PADDING OFF

GO

If we look at the above customerinfo table, the [customerid] column is marked as primary key column.

Once we mentioned the particular column as primary key column, then by default clustered index will be created on that column.

To check it out whether index has been created or not run the following command.

SP_HELPINDEX  CustomerInfo

RESULT:-

PK_CustomerInfo            clustered, unique, primary key located on PRIMARY      CustomerID

Clustered index is nothing but it will determine or affect the physical stored order of the table.

If we look at the above customerinfo table, the customerid column is marked as primary key column .once we mentioned the particular column as primary key column, then by default clustered index will be created and then the data will be sorted by customerid column physically.

To check it out run the following command...if u look at the below script, you can come to know that we are trying to insert the records in a non-sequential  manner .even if you try to do so, it will be stored sequentially only as we have the clustered index on the customer  column.

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (3, N'Steve', N'Waugh', 34, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (2, N'Shane', N'Watson', 32, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (4, N'Peter', N'Siddle', 23, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (1, N'Mone', N'Morkel', 44, N'Male')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (6, N'Serena', N'Williams', 23, N'Female')

INSERT [dbo].[CustomerInfo] ([CustomerID], [FirstName], [LastName], [Age], [Gender]) VALUES (5, N'Maria', N'Sharapova', 25, N'Female')

To check It out run the following command:-

SELECT * FROM CustomerInfo

/*

CustomerID                   FirstName                LastName            Age        Gender

1                                              Mone                    Morkel                  44            Male

2                                              Shane                   Watson                  32            Male

3                                              Steve                    Waugh                  34            Male

4                                              Peter                     Siddle                   23            Male

5                                              Maria                    Sharapova            25            Female

6                                              Serena                  Williams               23            Female

*/

/*

                * We can have only one clustered index per table..but we can create clustered index on more than columns.This called composite clustered index.

                 * To create the clustered index on more than first we need to delete the existing clustered index on customerinfo table.

                * To do that just expand the customerinfo table in object browser .then  expand keys section and delete the primaykey or expand the indexes section and delete the clustered index.

To drop Index using query:-

 

  DROP INDEX TABLENAME.INDEXNAME

 

*/

Basic Syntax To Create The Index:-

CREATE INDEX [CLUSTERED OR NONCLUSTERED] INDEXNAME ON TABLENAME(COLUMNNAMELIST)

To Create The Clustered Index On More Than One Column:-

CREATE CLUSTERED INDEX INDX_CustomerInfo_GENDER_AGE ON CustomerInfo(GENDER DESC ,AGE ASC)

To Check It Out Whether Index Has Been Created Or Not Run The Following Command:-

SP_HELPINDEX CustomerInfo

Now run the following command and see the result:-

SELECT * FROM CustomerInfo

/*

CustomerID                 FirstName  LastName           Age        Gender

4                                              Peter     Siddle                     23            Male

2                                              Shane   Watson                   32             Male

3                                              Steve    Waugh                    34            Male

1                                              Mone    Morkel                   44            Male

6                                              Serena  Williams                 23            Female

5                                              Maria    Sharapova             25             Female

*/

/*

 If we look at the above result, you can come to know that clustered index will affect the physical stored order of the data in the table.

                As we created clustered index on gender and age column, the data will be sorted by gender in descending first and then will be sorted by age column in ascending order.

*/

/*

                                Non-clustered index is just like a book only. Index is stored in one location and the data is stored in another location. The index will have the pointer to locate the actual data .It won't affect the physical stored order of the data in the table.

 

*/

To Create Non-Clustered Index:-

CREATE INDEX INDX_CustomerInfo_LASTNAME ON CustomerInfo(LASTNAME)

 --OR—

CREATE NONCLUSTERED INDEX INDX_CustomerInfo_LASTNAME ON CustomerInfo(LASTNAME)

   Nonclustered key word is the optional one.

To Check It Out Whether Index Has Been Created Or Not Run The Following Command:-

SP_HELPINDEX CustomerInfo

/*

INDX_CustomerInfo_GENDER_AGE    clustered    located on PRIMARY                Gender(-), Age

INDX_CustomerInfo_LASTNAME          nonclustered   located on PRIMARY           LastName

*/

/*

                Once We Created Non-Clustered Index On The Lastname Column The Data Will Be Sorted And Stored Separately By Lastname Column With Row Address:-

  EX:-

 

Siddle       -> ROW ADDRESS

Watson                   -> ROW ADDRESS

Waugh     -> ROW ADDRESS

.

.

.

Here ROW ADDRESS is nothing but it is just like a Hexa- decimal number.

                Whenever we try to retrieve the data from customerinfo table by lastname , first it will look at the data from the index location and then fetch the data from  actual physical table by using its row address.

                Non clustered index is slower than clustered index as it has to extra look up to fetch the data.

                Note: we can have as many as non clustered indexes per table.

 

                Note: Index sometimes will hurt the performance also if u have more indexes on the specific table and do lot of modification (i.e. Insert,update or delete) on   the table as well. So we need to create the index on appropriate column only. And also we should not create more indexes on table if lot of modification happened.

 

 */

SQL SERVER BASICS TUTORIAL -PART 4

/*

Stored Procedure(SP): is a set of precompiled queries. Whenever you execute the normal query, it has to be compiled first, and then only it will get executed. When you execute the SP first time ,it will be compiled and executed at that time only and also it will create some execution plan when you execute next time it won’t get compiled unless you  make  any changes. It will get executed directly and reuse the existing execution plan to execute the query. So it will be faster as compare to normal sql query.

*/

Creating the EmployeeInfo table and populate the table with some data.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EmployeeInfo](

                [EmpID] [int] IDENTITY(1,1) NOT NULL,

                [FirstName] [nvarchar](50) NULL,

                [LastName] [nvarchar](50) NULL,

                [Age] [int] NULL,

                [Gender] [varchar](10) NULL,

 CONSTRAINT [PK_EmployeeInfo] PRIMARY KEY CLUSTERED

(

                [EmpID] 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

SET ANSI_PADDING OFF

GO

Inserting Records into the EmployeeInfo table as shown below:-

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Pat', N'Thomson', 24, N'Male')

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Ricky', N'Ponting', 25, N'Male')

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Venus', N'Williams', 32, N'Female')

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Serena', N'Williams', 35, N'Female')

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Steve', N'Waugh', 44, N'Male')

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Shane', N'Watson', 26, N'Male')

INSERT [dbo].[EmployeeInfo] ( [FirstName], [LastName], [Age], [Gender]) VALUES ( N'Maria', N'Sharapova', 33, N'Female')

Selecting records from dbo.EmployeeInfo table as shown below:-

SELECT * FROM EmployeeInfo

Now i am retreiving something like this as shown below:-

SELECT FIRSTNAME,LastName,Age FROM EmployeeInfo

/*

Whenever u need the above information u need to run this over and over again.Instead of doing like this we can wrap the above query into the stored procedure then we can call by its name as shown below.

SYNTAX TO CREATE SP:-

CREATE PROC OR PROCEDURE PROCEDURENAME (PARAMETERS(OPTIONAL ONE))

AS

BEGIN

      SET OF SQL STATEMENTS...

END

*/

CREATE PROC uspGetEmployeeInfo

AS

BEGIN

  SELECT FIRSTNAME,LastName,Age FROM EmployeeInfo

END

Once you created this sp , you can find that SP in programmability-->stored procedures folder in the object explorer under your database context.

     We can execute the sp in different ways as shown below.

1.       uspGetEmployeeInfo

2.       EXEC uspGetEmployeeInfo

3.       EXECUTE uspGetEmployeeInfo

4.       We can execute from the object explorer also by pressing F8 key.

To view the text of stored proc:-

SP_HELPTEXT uspGetEmployeeInfo

After created if you want to make any changes in stored proc you can use alter command as shown below.

ALTER PROC uspGetEmployeeInfo

AS

BEGIN

  SELECT FIRSTNAME,LastName,Age FROM EmployeeInfo WHERE Gender='MALE'

END

To encrypt the SP:-

ALTER PROC uspGetEmployeeInfo WITH ENCRYPTION

AS

BEGIN

  SELECT FIRSTNAME,LastName,Age FROM EmployeeInfo WHERE Gender='MALE'

END

Now try to view the text of the sp:-

SP_HELPTEXT uspGetEmployeeInfo

MSG:- T he text for object 'uspGetEmployeeInfo' is encrypted.

If you try to open through object browser then also it will throw some error.

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

We can even create the stored procedure with parameters as well .it will accept both the input parameters and output parameters.

Creating the sp with input parameters is shown below.

CREATE PROC uspGetEmployeeInfoByGender

@Gender varchar(10)

AS

BEGIN

   SELECT * FROM EmployeeInfo WHERE Gender=@Gender

END

To execute the SP:-

uspGetEmployeeInfoByGender 'FEMALE'

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

Stored proc with output parameters:-

    Look at the employeeinfo table.Here we have both the male and female employee.Now i am going to write the sp to count the employee by gender by using output parameter.

To create the sp with output parameter:-

CREATE PROC uspGetEmployeeCountByGender(@Gender varchar(10),@TotalEmployeeCount int output)

AS

BEGIN

  SELECT @TotalEmployeeCount= COUNT(EMPID) FROM EmployeeInfo WHERE Gender=@Gender

END

To execute the sp with output parameters:-

DECLARE @TOTALCOUNT INT

EXEC uspGetEmployeeCountByGender 'MALE',@TOTALCOUNT OUTPUT

PRINT @TOTALCOUNT

Just remove the output keyword above and execute.

DECLARE @TOTALCOUNT INT

EXEC uspGetEmployeeCountByGender 'MALE',@TOTALCOUNT

PRINT @TOTALCOUNT

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

Stored procedure with return value:-

Whenever we execute the sp, it will return zero or non-zero value.

Zero - indicates "success”. Non-zero - indicates failure of sp.

Execute the sp in object browser and check out the return value what it returns.

To execute the sp in the object explorer follow the below steps.

Just select the sp which u want to execute...right click--> execute stored procedure-->specify input parameter if applicable and click ok.

CREATE PROC uspGetEmployeeCount

AS

BEGIN

RETURN(SELECT COUNT(EMPID) FROM EmployeeInfo)

END

Now executing the SP as shown below:-

DECLARE @TOTALCOUNT INT

EXEC @TOTALCOUNT=uspGetEmployeeCount

PRINT @TOTALCOUNT

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

To check out what are all the table the particular sp is using and  what are all the sp's we are using in the specific table:-

SYNTAX:   SP_DEPENDS 'PROCNAME'

SP_DEPENDS uspGetEmployeeCount

SP_DEPENDS EmployeeInfo

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

SQL SERVER BASICS TUTORIAL-PART 3

/*

  GETTING LAST GENERATED IDENTITY COLUMN VALUE FROM THE TABLE:-

               WE CAN GET THE LAST GENERATED IDENTITY COLUMN VALUE FOR A TABLE BY USING 

     1)SCOPE_IDENTITY() :-IT WILL RETURN LAST GENERATED IDENTITY VALUE FOR THE CURRENT SESSION AND CURRENT SCOPE

     2)@@IDENTITY :- IT WILL RETURN LAST GENERATED IDENTITY VALUE FOR THE CURRENT SESSION ACROSS ALL SCOPES.

     3)IDENT_CURRENT('TABLENAME'):- IT WILL RETURN LAST GENERATED IDENTITY VALUE FOR THE SPECIFIC TABLE IN ALL SESSION ACROSS ALL SCOPES.

     
     NOTE: SESSION IS NOTHING BUT IF YOU OPEN THE NEW QUERY WINDOW , THEN ONE SESSION WILL BE STARTED.


*/

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

CREATE TWO TABLES WITH IDENTITY COLUMN AS SHOWN BELOW:--


CREATE TABLE IDENTITYTEST1(ID INT IDENTITY(1,1),NAME VARCHAR(50))

CREATE TABLE IDENTITYTEST2(ID INT IDENTITY(1,1),NAME VARCHAR(50))

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

NOW INSERT SOME RECORDS INTO THE IDENTITYTEST1 :-

INSERT IDENTITYTEST1 VALUES('NAME1')
INSERT IDENTITYTEST1 VALUES('NAME2')

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

       NOW SELECT RECORDS FROM IDENTITYTEST1:--

SELECT * FROM IDENTITYTEST1


/*  RESULT:-

ID NAME
1 NAME1
2 NAME2

   BY LOOKING AT RESULT, WE CAN SAY THAT THE LAST GENERATED IDENTITY VALUE IS 2...IF YOU WANT TO GET THAT LAST IDENTITY VALUE, WE CAN GET IT AS SHOWN BELOW...

*/

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

TO GET THE  LAST IDENTITY VALUE FOR IDENTITYTEST1 RUN THE FOLLOWING COMMAND:-

SELECT SCOPE_IDENTITY() 'SCOPEIDENTITY'

/*
    RESULT:-

SCOPEIDENTITY
  2

*/

SELECT @@IDENTITY  '@@IDENTITY'
/*

 RESULT:-

@@IDENTITY
 2
*/

SELECT IDENT_CURRENT('IDENTITYTEST1') 'IDENT_CURRENT'

/*
RESULT:-

IDENT_CURRENT
 2

*/


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

/*
  NOW YOU MAY ASK SOMETHING LIKE THIS..WHAT IS THE DIFFERENCE BETWEEN THEM AND WHAT TO USE WHEN?

  PLS REFER THE TOP OF THIS DOCUMENT TO KNOW WHAT IS SCOPE_IDENTITY,@@IDENTITY AND IDENT_CURRENT  BEFORE PROCEEDING.

         TO UNDERSTAND BETTER,I AM GOING TO CREATE ONE TRIGGER ON IDENTITYTEST1 TABLE.WHENEVER WE INSERT SOME RECORDS INTO THE IDENTITYTEST1 , THIS TRIGGER WILL FIRE  AND INSERT ONE RECORD INTO THE IDENTITYTEST2 TABLE AS WELL.

  NOTE: TRIGGER IS NOTHING BUT IT WILL FIRE AUTOMATICALLY WHEN WE PERFORM INSERT ,UPDATE OR DELETE OPERATION..

*/

--------------------------------------------------------------------------------------------------------------------------
    CREATING A TRIGGER ON IDENTITYTEST1 TABLE:-


CREATE TRIGGER trIDENTITYTEST ON IDENTITYTEST1 FOR INSERT
AS
BEGIN
  INSERT IDENTITYTEST2 VALUES('Name for IDENTITYTEST2')
END


     NOW INSERT SOME RECORDS INTO THE IDENTITYTEST1:-



INSERT IDENTITYTEST1 VALUES('NAME3')

--RUN THE BELOW COMMAN AND SEE THE RESULT...
SELECT * FROM IDENTITYTEST1
SELECT * FROM IDENTITYTEST2

/*  RESULT FOR IDENTITYTEST1:-

ID NAME
1 NAME1
2 NAME2
3 NAME3

*/


/*

RESULT FOR IDENTITYTEST2:-

1 Name for IDENTITYTEST2

*/
--------------------------------------------------------------------------------------------------------------------------
NOW RUN THE FOLLOWING COMMAND WITH IN THIS SAME QUERY WINDOW:-

SELECT SCOPE_IDENTITY() 'SCOPEIDENTITY'

/*  RESULT:-
SCOPEIDENTITY
  3
*/

SELECT @@IDENTITY  '@@IDENTITY'
/*  RESULT:-
@@IDENTITY
 1
*/

SELECT IDENT_CURRENT('IDENTITYTEST1') 'IDENT_CURRENT'

/*  RESULT:-
IDENT_CURRENT
   3
*/

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

/*

 
   SO FAR WHAT I HAVE DONE IS I CREATED A TRIGGER ON IDENTITYTEST1. WHENVER WE INSERT SOME RECORDS INTO IDENTITYTEST1 ,THE TRIGGER WILL FIRE AND  INSERT ONE RECORD INTO THE IDENTITYTEST2 .

   ACTUALLY FIRST THING WE ARE DOING HERE IS WE ARE INSERTING SOME RECORDS INTO IDENTITYTEST1 WITHIN THE SAME WINDOW(I.E.IN CUREENT SESSION AND CURRENT SCOPE).

   THAT'S WHY SCOPE_IDENTITY() RETURNS 3 AS LAST IDENTITY VALUE WHICH IS NOTHING BUT LAST IDENTITY VALUE OF IDENTITYTEST1.
 
   AFTER THE  INSERT HAPPENED IN THE IDENTITYTEST1 ONLY, THE TRIGGER WILL BE FIRED AND INSERT A RECORD INTO THE  IDENTITYTEST2 TABLE.(I.E IN CUREENT SESSION AND ACROSS ALL SCOPE)

   INSTEAD OF SAYING THIS AS ALL SCOPE WE MAY SAY AS LAST SCOPE OF THIS SESSION.
 
 
 
*/

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

IDENT_CURRENT('TABLENAME'):- RETURN LAST GENERATED IDENTITY VALUE FOR THE SPECIFIC TABLE IN ALL SESSION(I.E. ALL OPEN QUERY WINDOW) ACROSS ALL SCOPES.
 
   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'
 
   /*
   RESULT:-
   IDENT_CURRENT
   1
   */
 
   NOW INSERT SOME RECORDS INTO THE IDENTITYTEST2 FROM THE SAME WINDOW.
 
 
   INSERT IDENTITYTEST2 VALUES('Name for IDENTITYTEST2')
 
   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'
 
    /*
   RESULT:-
   IDENT_CURRENT
     2
   */
 
 
   NOW OPEN THE NEW QUERY WINDOW (I.E.STARTING THE NEW SESSION) AND RUN THE FOLLOWING COMMAND.

   INSERT IDENTITYTEST2 VALUES('Name for IDENTITYTEST2')

   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'

   /*
   RESULT:-
   IDENT_CURRENT
    3
   */
 
      AGAIN COME BACK TO STARTING WINDOW THEN RUN THE FOLLOWING COMMAND.
 
   SELECT IDENT_CURRENT('IDENTITYTEST2') 'IDENT_CURRENT'

   /*
   RESULT:-
   IDENT_CURRENT
    3
   */
 
   /*
 
   NOW U CAN UNDERSTAND TAHT IDENT_CURRENT WILL RETURN THE LAST GENERATED IDENTITY VALUE FOR THE SPECIFIC TABLE IN ALL SESSION(I.E. ALL OPEN QUERY WINDOW) ACROSS ALL SCOPES
   
   */
   ------------------------------------------------------------------------------------------------------------------------

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
*/
--------------------------------------------------------------------------------------------------------------------------