Create Primary Key Based on Identity Value

Consider I am having a table ([DBO].[WBCustomer]) with IDENTITY column ID.

Truncate table [DBO].[WBCustomer]
DBCC CHECKIDENT ('[DBO].[WBCustomer]', RESEED, 1)
WHEN I give

select IDENT_CURRENT('[DBO].[WBCustomer]')

gives me 1

After inserting a record, it gives me 1.

Since I having another column PID which is primary key column in the table([DBO].[WBCustomer]). I have to insert like E1, E2

Since both the time it returns the same value(i.e. after reseed and inserting a single record) primary key violation error occurred.

Thanks.

Seems like you're attempting to do autonumbering without gaps. DBCC CHECKIDENT is not something you want to be doing all the time, also seeing that it requires pretty elevated permissions for the current user.
In all, better not to use the actual values of the identity column, just use it as your criteria to find the previous PID:
DECLARE @LastPID VARCHAR(10)

SELECT @LastPID = c.PID
FROM dbo.WBCustomer c
WHERE c.ID = (SELEC MAX(x.ID) FROM dbo.WBCustomer x)

Now you have the previous PID, so you can increment that whichever way you need to.

Since max(id) will produce wrong value on deletion. When deleting the last record i am getting the same id once again.
So help me with producing by using IDENT_CURRENT

My suggestion was to no longer use the ID value for creating the PK value.
Only use MAX(ID) value to find the most recently added row, read the PID value from that row, then increment the number part of that PID.

I have tried to insert some records in an identity column and got confused on the below scenario. It seems to be a bug in sql server :0 I think so,DROP
TABLE Venkattempcreate
table Venkattemp (id int identity(1,1),VAL INT)INSERT
INTO Venkattemp(VAL) VALUES (1)Truncate
table VenkattempDBCC
CHECKIDENT ('Venkattemp', RESEED, 1)INSERT
INTO Venkattemp(VAL) VALUES (1)INSERT
INTO Venkattemp(VAL) VALUES (1)select
IDENT_CURRENT('Venkattemp')+1DELETE
Venkattemp WHERE ID =3select
IDENT_CURRENT('Venkattemp')+1-- Here, why its inserting value 3 in the identity column. This is an unexpected behaviour of sql serverINSERT
INTO Venkattemp(VAL) VALUES (5)SELECT
* FROM Venkattempselect
IDENT_CURRENT('Venkattemp')INSERT
INTO Venkattemp(VAL) VALUES (5)INSERT
INTO Venkattemp(VAL) VALUES (5)INSERT
INTO Venkattemp(VAL) VALUES (5)select
IDENT_CURRENT('Venkattemp')DELETE
Venkattemp WHERE ID =6select
IDENT_CURRENT('Venkattemp')-- Here, why its not inserting 6 in the below insert statement. This is an expected behaviour of sql serverINSERT
INTO Venkattemp(VAL) VALUES (5)SELECT
* FROM Venkattemp
http://venkattechnicalblog.blogspot.com/2010/01/peculiar-nature-of-identity-insert-in.html
Thanks and Regards,
Venkatesan Prabu .J

Well, it's not a bug if you're not using the proper syntax ...
The following does not reseed the identity column:
select IDENT_CURRENT('Venkattemp')+1
This query only retrieves the highest identity value inserted into the table, adds 1 to that value, and doesn't do anything with either value, nor with the identity column.
You seem to be looking for the DBCC CHECKIDENT syntax. This sort of data manipulation is not normally associated with "live" production systems, although it might be done during off-line maintenance work.
Other than that, you should not be concerned with the actual identity value.