Generating non-numeric IDENTITY columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Generating non-numeric IDENTITY columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-08 : 11:56:18

Christopher writes "Hi,

Using Microsoft SQL Server, how would one generate an identity that is a string or varchar?

What I need is to generate keys that look like TMP-10001, TMP-10002, etc.

My attempt:

(1) Create a table called "numGen" with the column "lastAssigned".
(2) Create a user-defined function called GenerateNextTempNum() that selects lastAssigned, increments it, writes it back, and returns the new value.

This doesn't work because user-defined functions can't modify tables.

There is no trigger on SELECT.

What I really want is a user-defined function that generates these temporary unique IDs of above format, but it seems that I am sandboxed!

--Chris"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-08-08 : 11:56:19

Would something like this work for you?

http://www.sqlteam.com/item.asp?ItemID=1417

wz2b
Starting Member

1 Post

Posted - 2005-08-08 : 19:14:52

Not exactly, but maybe. As the article points out, there are certain inconveniences associated with having non-numeric primary keys, but there are certain circumstances where it can't be avoided.

The CAD software I am using accesses the database directly. It can also add new parts. It wants to add them with the key column containing TMP-00001, TMP-00002, etc. Unfortunately, the way it does this is to select max(partnumber) from table then chop the number off (similar to the article), generate a new one, and use that for the next record. For all I know, it's not even threadsafe...I have no way of knowing, because it's not my software.

I might fix the problem by simply duplicating what they did, but do it on an insert trigger. Still, I think it would be nice to learn how to do this for future reference.

What I really want is a user-defined function that generates TMP-00001, TMP-00002, etc. and automatically generates a new one when the function is invoked. This would be the simplest thing to use:

INSERT INTO tablename SET id_column=GENERATE_TEMP_PARTNUM() ...

--C

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-08 : 19:58:07

Welcome to SqlTeam Chris,
this might give You some ideas;

CREATE TABLE tmp
(
	KeyCode AS 'TMP-' + RIGHT('0000'+LTRIM(KeySeed),5) UNIQUE
	,KeySeed INT IDENTITY(1,1) CHECK(KeySeed BETWEEN 1 AND 99999) PRIMARY KEY
)

INSERT tmp DEFAULT VALUES
INSERT tmp DEFAULT VALUES
INSERT tmp DEFAULT VALUES

SELECT KeyCode FROM tmp

DROP TABLE tmp

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 01:40:37

>>What I need is to generate keys that look like TMP-10001, TMP-10002, etc.

If TMP- is fixed one then you can have an identity column and in your presentation layer append TMP- to that column

Madhivanan

Failing to plan is Planning to fail

zelk
Starting Member

6 Posts

Posted - 2005-08-09 : 08:32:53

I belive one should always use stored procedures as the interface to the application layer. Therefore, make sure that your insert-procedure for that table, calls a function to generate the next id and use that to create your new record. The function uses the table itself to generate the next value by selecting the TOP 1 record in DESCENDING order. Use transactions. You might run into the problem that two records are being created at the very same time using the same id value so think about your error handling.

Hope that gives you some ideas...

/Ricky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources