TSQL - IDENTITY Property

IDENTITY is pretty much a very important property of a database. In post i will different places the use of (Creation, Seeding and Reseeding) IDENTITY property.
1) SQL Server allows only ONE identity column per table.
       Ex:
       Create Table #Identity_Post (RankNumber int identity(1,1),
                                                    First_Name varchar(40))
2) Identity(seed,increment)
      Ex: identity(1,1) means starting number is 1 and it will add 1 to next number
      identity(1000,5) means starting number is 1000 and it will add 5 to next number

3) SET IDENTITY_INSERT [Table_Name] [ON/OFF]
This property is one very usefull, When we want to insert a deleted rowid  (identity number) again in the table we can use this property.
     Ex:
     CREATE TABLE #TempTable (Rowid int IDENTITY(1,1), Name nvarchar(20))
     INSERT INTO #TempTable (Name) VALUES ('BiSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('BwSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('IsSpecialist')
     INSERT INTO #TempTable (Name) VALUES ('RsSpecialist')

     ----Delete a row so that we can create a gap in between the rows.

     DELETE from #TempTable WHERE Name = 'RsSpecialist'

     SELECT * FROM #TempTable

     ----Now it will through an error    

     INSERT INTO #TempTable (Rowid, Name) VALUES(4, 'AsSpecialist')

     ----SET IDENTITY_INSERT to ON.    

     SET IDENTITY_INSERT #TempTable ON

     ----Now its a identity magic    

     INSERT INTO #TempTable (Rowid, Name) VALUES(4, 'AsSpecialist')

     ----Then SET IDENTITY_INSERT  OFF, so that next time it won't allow the explicit insert.    

     SET IDENTITY_INSERT #TempTable OFF

     SELECT * FROM #TempTable

     DROP TABLE #TempTable