Identiy column

 Identity is a state or fact which distinguishes  one object  from another objects  , so identity column is one of the way of make rows  distinguishable in a table .

Decalring

                     
   IDENTITY  ( seed , increment ) 

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 TABLE employees

(

 id_num int IDENTITY(1,1),

 fname varchar (20),

 minit char(1),

 lname varchar(30)

)

DATA Insertion on Table

Sql server automatically takes care of incrementing and inserting this column value.

We can forcefully insert identity by running following

        SET IDENTITY_INSERT tablename off  

Important point

   Next identity column value will be the next value of the max value inserted on the column (if the value exists or not in the table)

Delete vs Truncate

Most Frequently Asked Question from /  by  SQL Developers

	Delete	Truncate
1	DML (Data Manipulation Language)	DDL( Data Definition Language )
2	AS delete is DML so  Data Definition Language  log are created	No transaction log are created
3	We can delete all rows from table or based on condition ie. Specifying condition on where clause.	Will delete all rows .
4	As delete creates logs it is slow but can rolled back	It is very fast roll back is not possible.
5	Data from reference tables  can be deleted .(On cascaded )	Truncate table does not work on referenced tables .
6	Identity columns are not reseeded  .	All Identity column are reseeded

7

Delete can leave empty pages in table since as page removal requires a table lock which doesn't necessarily happen.

Truncate removes all pages. No empty pages are left behind in a table.

8

Delete command acquires table and/or page and row locks for the whole table.

Truncate acquires only table and page locks for the table. Since no row lock are used less memory is required.

9

On Delete all delete triggers will be fired.

No trigger will be fired upon Truncate command.

·        




E      Execution of each command has a log ( Not  transaction log)