identity insql server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » identity insql server

identity insql server

Rate Topic

Display Mode

Topic Options

Author

Message

sunileadara1986

Posted Tuesday, May 13, 2008 11:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 06, 2008 12:28 AM
Points: 2, Visits: 15

i want to put identity to a column bt tht column datatype must be varchar and tht identity must be starts with P001.pls help me
thank u

Post #500181

jgrubb

Posted Thursday, May 15, 2008 7:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:39 AM
Points: 277, Visits: 502

First, be careful of your terms. "Identity" means something specific in sql server. In this case, you can't use it directly, since it must be a number or a guid. It exists because to rapidly assign unique values to a table with little overhead. It was not intended to be a user visible field or hold updatable information. Avoid exposing it. What you want is a unique key.

Some options:

1) Insert trigger. Create a column of any type, make it a unique index (possibly the Primary Key) and you can assign a value programatically, or get the last with a lookup. You can store the last value somewhere, for example in a table. I worked on an "Enterprise" application that had 1 table with 1 row for each application table to hold the last value. Not suggesting that, since I thought it silly, but you can go that route. I've also seen another that had a table with 1 row per table with the last value. Lock the record to enforce integrity. It does serialize inserts.

2) make a compound key, with say (assuming the value changes), a char field containing the prefix, and an int identity field. Then build a view that casts them together as a char.

There's more, but this is off the top of my head. Both have some performance impacts, and the shape of your data and usage patterns will indicate the best path.

Post #501304

Steve Jones - SSC Editor

Posted Thursday, May 15, 2008 9:02 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 31,436, Visits: 13,750

I'd use an int identity column and then a computed column or a view that concatenates them.

You can't do a varchar auto-incrementing field.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #501404

sunileadara1986

Posted Wednesday, May 21, 2008 10:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 06, 2008 12:28 AM
Points: 2, Visits: 15

thank u sir

Post #504977

« Prev Topic | Next Topic »

Permissions