Question primary key, how to generate custom value

evolet10000

Active member
Joined
Nov 22, 2012
Messages
40
Programming Experience
Beginner
hi there, i have a SQL SERVER 2008 table with 3 columns(P_ID(Primary key), PRODUCT_NAME, DESCRIPTION),
now, how can i generate a custom and unique(ofcourse) value for my primary key,
for example: sc00001 in the 1st row, and when i enter another data it will generate sc00002 something like that,
just wondering if someone here give teach me how? instead of using auto-increment function..

thanks in advance :D
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
A primary key should NOT be data. Store your SC field separately, choose its value when inserting new data in the table. If you need a unique value depending on the content of certain fields, create a MD5 hash of the fields you need to include with the HASHBYTES SQL function and store that in its own field. Before inserting new data you can hash the relevant fields in your record and see if the resulting hash already exists in the table.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
I recently did work for a client that wanted something similar. They wanted record numbers of the form APP000000, i.e. sequential six-digit numbers padded with zeroes if required and prefixed with "APP". To accommodate that I just used a regular old identity column as the PK and then added an extra column for the record number. That column was generated from the PK identity value by setting the (Formual) under Computed Column Specification to:
Code:
('APP'+right('000000'+CONVERT([varchar],[ApplicationID],(0)),(6)))
I also set Is Persisted to Yes so that the value was calculated once only when the record was first inserted.

The one potential issue with this approach is that you cannot guarantee that there won't be gaps in the sequence because if an error occurs during an insert then an ID may be skipped. There is no technical reason that that should ever be a problem, although some people are picky enough to make an issue of it anyway.
 
Top Bottom