CODEWITHSUNDEEP
sqlsql-servert-sql
ComputerGenius
ComputerGenius

Reputation: 43

Creating my Custom Unique Key

I have a table in my SQL Server. Currently I am using the identity column to uniquely identify each record but my changing needs required a unique key generated in a certain format (as specified by my client). I have tried to generate the unique key from my application by appending a unique integer (that is incremented on every insert) to the format specified my client is not satisfied with my current solution. It would be great if I can be directed to a better technique to solve my problem rather then my current solution.

The format is like: 

PRN-YEAR-MyAppGeneratedInt

Upvotes: 0

Views: 1096

Answers (3)

paparazzo
paparazzo

Reputation: 45096

You could create a Computed Column and just append the identity

('Custom_'+CONVERT(varchar(10),iden))

Upvotes: 0

Tayyab
Tayyab

Reputation: 1217

You can create a SEQUENCE to serve your purpose which were introduced in SQL Server 2012. A real detailed explanation about SEQUENCE can be found here.

Hope this helps :)

As per you specified in the comments the format let me also give you an example that how you can solve your problem using a sequence:

First create a sequence like:

CREATE SEQUENCE SeqName
AS int
START WITH 1
INCREMENT BY 1
CYCLE 
CACHE

Next you can use this sequence to generate your desired unique key in you app program.

  1. Get the next value for sequence "SELECT NEXT VALUE FOR SeqName;"
  2. Create a string using the value like :String key= "PRN"+year+SeqValue;
  3. Finally store this string as your unique key in your Insert statement.

You can write the application code as per you need :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Basically, keep the current identity column. That is the best way for you to identify and manage rows in the table.

If the client needs another unique key, then add it. Presumably, it will be a string (given that it has a "format"). You can possibly create the key as a generated column. Alternatively, you may need to use a trigger to calculate it.

In general, integers are better for identity columns, even if end users never see them. Here are some advantages:

  • They encode the ordering of row insertion in the database. You can, for instance, get the last inserted row.
  • They are more efficient for foreign key references (because numbers are fixed-length and generally shorter than strings).
  • They make it possible to directly address a row, when data needs to be fixed.

Upvotes: 4

Related Questions