How can i assign 5 digit Identity Increment in Microsoft Sql Server? HELP PLZZ.

Question

Hi i have created a database, now i have a table that contain the primary key on my bill number column with tinyint and in column properties i have changed the identity specification to yes and its identity increment and seeds are 1 now what i need is i want my starting value increment begins with 5 digits number, for instance instead of when its just arranged by 1 2 3 4 5 and etc i want its increment to be like 00001 00002 00003 00004 so on and so forth and when it reaches 00009 it must automatically change the next increment to 00010  for each of my row. but unfortunately i couldnt do it with any possible datatype for my column, can anybody concern about this?

thanks.

Answer 1

Hi i have created a database, now i have a table that contain the primary key on my bill number column with tinyint and in column properties i have changed the identity specification to yes and its identity increment and seeds are 1 now what i need is i want my starting value increment begins with 5 digits number, for instance instead of when its just arranged by 1 2 3 4 5 and etc i want its increment to be like 00001 00002 00003 00004 so on and so forth and when it reaches 00009 it must automatically change the next increment to 00010  for each of my row. but unfortunately i couldnt do it with any possible datatype for my column, can anybody concern about this?

thanks.

Answer 2

An identity column data type may be of type decimal, int, numeric, smallint, bigint, or tinyint.  As such, the leading zeros are insignificant (e.g. 1 = 0000000001).  However, you can create a computed column (perhaps persisted) based on the identity column like the example below to compute a char value as you describe.  Be aware that you will end up with duplicate values once the identity value exceeds 99999.


CREATE TABLE dbo.Foo(
Bar int NOT NULL IDENTITY(1,1),
FooBar AS RIGHT('0000' + CAST(Bar AS varchar(5)), 5)
)

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

the problem comes with the data type. if you use int it won't has leading zero. how about if your really need to be 5 digits then you can manipulate it on retrieval..

declare @table table (id int identity(1,1), data)

select left('00000', 5-len(id))+convert(varchar(5),id), data from @table

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.