Facing problem Increamenting by one in Sql Server Field whose data type in character

Question

I have data base field intr_prod_id char(10) Now i want to generate the Primary key like IP-1. After 1P-10 it will not increamenting.
I write the Query for this above id Generation IP-10. It will increaming upto 10 after that it will not increamenting

Anuja Pawar · Answer

Hi Waqar The idea in database design is to keep each data element separate. And each element has its own datatype constraints and rules. That IP 2 is not one field but two. Same with XXXnnn or whatever. It is incorrect and it will severely limit your ability to use the data and use database features and facilities. Break it up into two discrete data items: column_1 CHAR(1) column_2 INTEGER Then set AUTOINCREMENT on column_2 And yes your Primary Key can be (column_1 column_2) so you have not lost whatever meaning IP 2 has for you. Try this CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY  IDwithChar AS 'IP' + RIGHT ( '-' + CAST(ID AS VARCHAR( 10 )) 6 ) PERSISTED ) Hope it helps J