other than identity option

Question

other than identity option

saqlainkhan

Ten Centuries

Points: 1126
More actions
December 14, 2009 at 12:11 pm

#218732

i know identity can be used with int
scenario
we need to code the page with PXXXX starting from P1000. every new insert should increment this value. the field is a char(5).
I am trying to avoid a trigger. are there any other options?
Noli Timere

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

You could use an integer column with the IDENTITY set on it and have it begin at 1000. Then create a computed column that takes the value 'P' and concatenates your IDENTITY column onto it. This would save you from having to lookup the MAX value of your column each time your want to insert.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Two options...

1. Have two columns. One a Char(1) with default value of P and the other an Identity. When selecting Concat both fields. PK based on two fields.

2. Have a Char and take care of the updateed (New row) in the application layer.

Both are not good solutions.

-Roy

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's a code example of what John and roy are talking about with the calculated column:

Create Table Example(P int identity(1000,1) , --starts at 1000

PCODE AS 'P' + CONVERT(varchar,(P)) PERSISTED, --gets calculated automatically,PERSISTED so it is stored in the table/faster access

SOMESTUFF VARCHAR(30) )

INSERT INTO Example(someStuff)

SELECT 'AUTO' UNION ALL

SELECT 'PCODE' UNION ALL

SELECT 'EXAMPLE'

SELECT * FROM Example

--results

P PCODE SOMESTUFF

1000 P1000 AUTO

1001 P1001 PCODE

1002 P1002 EXAMPLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

saqlainkhan Ten Centuries Points: 1126 More actions · Answer 4

saqlainkhan

Ten Centuries

Points: 1126

December 14, 2009 at 1:51 pm

#1092206

Thank you all. will try it out.

Noli Timere

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

John Rowan (12/14/2009)
You could use an integer column with the IDENTITY set on it and have it begin at 1000. Then create a computed column that takes the value 'P' and concatenates your IDENTITY column onto it. This would save you from having to lookup the MAX value of your column each time your want to insert.

It may also save the occasional deadlock and duplicate ID. Nicely done, John.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)