Microsoft Sql Server tables and constraints Part 2

This is my third blog about Microsoft Sql Server database administration. In last blog we talked about keys in Sql Server and found that there is no difference between syntax's of defining these keys in Oracle and Sql Server.
In this blog we will discuss auto generation of key values. Just like Synonym of Oracle database. I have demonstrated this by doing T-Sql script. and you can also achive this using Studio as well.

Comments added in between each step would help you to understand concept easily.


First I have created IDNTY_TAB2 table in SAMPLE Database using SAMPLE schema and defined ID as Primary key and set it to a IDENTITY column

USE [Sample]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [sample].[Idnty_Tab2](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Nm] [nchar](50) NOT NULL
) ON [PRIMARY]

GO

Now! I have inserted following rows in the table. Notice that I have not provided value of ID primary Key column. As this column is IDENTITY column. So its generating values its own starting from 1.

Insert into idnty_tab2 values ('James')
Insert into idnty_tab2 values ('Davis')
Insert into idnty_tab2 values ('Smith')
Insert into idnty_tab2 values ('Samantha')
Insert into idnty_tab2 values ('Nancy')
Insert into idnty_tab2 values ('Deran')

Select and confirm that you get to see all the rows.
Select * from idnty_tab2


Now! Delete the row and query again to see the result
Delete from Idnty_tab2 where id=3

Select * from idnty_tab2

Now try to insert new row and query the table and see the value of ID column
Insert into idnty_tab2 values ('Smith')

Select * from idnty_tab2
You will notice that I has not used missing 3 as the value of ID. So to use deleted ID value 3 we need to set
IDENTITY_INSERT on table..Following statement would set this parameter ON.

Set Identity_Insert Idnty_Tab2 On;

After setting this Parameter we need to provide columns name as well. without column name INSERT statement would fail.

Insert into idnty_tab2 values ('Harris'); --Would Fail with following message.
--Msg 545, Level 16, State 1, Line 2
--Explicit value must be specified for identity column in table 'Idnty_Tab2' either when
--IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Following statement would be successfull.
Insert into idnty_tab2 (id,nm) values (3,'Harris')

Confirm it once again
Select * from idnty_tab2 order by 1


Once you have inserted the missing values you should take this parameter OFF using follwoing parameter.
Set Identity_Insert Idnty_tab2 off;

Now start inserting the values again.
Insert into idnty_tab2 values ('James')

Select * from idnty_tab2 order by 1

Suppose we delete all the rows and again insert it.After insert when we query table and get to know that
instead from 1 ID is started from the higest value we had before we deleted all the rows. Sql Server instead of setting to 1 have started it from that value.

Delete from Idnty_Tab2

Insert into idnty_tab2 values ('James')

Select * from idnty_tab2 order by 1

Now to restart the feed from start we need to reset the seed by using following procedure

dbcc checkident('idnty_tab2',reseed,0)
Checking identity information: current identity value '9', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After using above procedure we insert values again and get to see that this time Sql Server has used 1 as starting value

Insert into idnty_tab2 values ('James')
Insert into idnty_tab2 values ('Davis')
Insert into idnty_tab2 values ('Smith')
Insert into idnty_tab2 values ('Samantha')
Insert into idnty_tab2 values ('Nancy')
Insert into idnty_tab2 values ('Deran')


Select * from idnty_tab2 order by 1

This is the behavior when we use DELETE statement, However if we use TRUNCATE statement than the counter is  automatically set to its starting value 1; Lets use Truncate statement and see what happens

Truncate table Idnty_Tab2;

Select * from idnty_tab2 order by 1

Insert into idnty_tab2 values ('James')
Insert into idnty_tab2 values ('Davis')
Insert into idnty_tab2 values ('Smith')
Insert into idnty_tab2 values ('Samantha')
Insert into idnty_tab2 values ('Nancy')
Insert into idnty_tab2 values ('Deran')

Select * from idnty_tab2 order by 1

Using TRUNCATE command we have no need to reset the counter. It happens with DELETE Statement only


I hope this would clear any doubt.
If have any doubt please write back to me.Any suggestion/comments would be appriciated.

Thanks