Auto increment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Auto increment

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-10-28 : 01:33:14

Hi,
iam working for adminhelpdesk module.k.when new request created from user,i will have insert the request deatils to the requestdeatils tbl with the id of RE-001 and this incremented by 1 for the next request.
i mean iam passing only the reqestorname and issue something to the table,Requestid should be created automatically starts with RE-001 and incremented by 1 for further req.

any help much appreciated

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 01:48:43

One way is to have a identity column in the table and another column (id column as per your requirement ) with computed.

Example:

Create table test
(
TestID int identity,
Id as 'Pic' + TestID,
NextCol varchar(50)
)

But it comes with cost of performance:

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/14/performance-of-a-query-on-computed-column-can-degrade-in-sql-2005-with-the-increase-in-complexity-of-function-in-computed-column.aspx

If it is just for formatting then you can add the prefix in frontend to identity column.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-10-28 : 01:52:19

It is not formatiing kind of stuff...

once this id ha sbeen generated,we will have pass this to other tbl.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-10-28 : 03:03:26

can anyone help me out..please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-28 : 06:11:23

Create table test
(
TestID int identity,
Id as 'RE' + right('00'+cast(TestID as varchar(3)),3),
NextCol varchar(50)
)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources