Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

identity insql server Expand / Collapse
Author
Message
Posted Tuesday, May 13, 2008 11:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 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
Posted Thursday, May 15, 2008 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:17 AM
Points: 277, Visits: 512

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
Posted Thursday, May 15, 2008 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:02 PM
Points: 33,153, Visits: 15,284
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
Posted Wednesday, May 21, 2008 10:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2008 12:28 AM
Points: 2, Visits: 15
thank u sir

Post #504977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse