How do I homebrew an identity column?

Question

We're moving some processes from a Sql Server database environment which allows identity columns to a Sql Server database environment that does not allow identity columns. How can we effeciently create unique integer values for surrogate primary keys without using the identity construct?

As for why we can't use an identity column, Microsoft's Parallel Data Warehouse splits tables between physically distinct servers. Microsoft removed the identity feature from the PDW because you would have to communicate between all of the servers to find the new identity value for each insert in a distributed table, which pretty much negates the point of parallelism.

What kind of volume and frequency are you expecting when it comes to inserts into your table? — Jeff B, Jun 14 '13 at 15:41
What do you mean an "SQL Server database environment that doesn't allow identity columns"? Do you mean the DBAs refuse to create them? If so, you need new DBAs. — HardCode, Jun 14 '13 at 16:35
Volume? Let's say a hundred thousand a day, as a hypothetical figure. — quillbreaker, Jun 14 '13 at 16:57
HardCode: I believe you're going to see a lot of movement away from identity columns over the next decade, just because it mandates centralization. — quillbreaker, Jun 14 '13 at 16:58
What does PDW guidelines tell you? I'm assuming they suggest using GUIDs as your primary keys. — Lasse V. Karlsen, Jun 14 '13 at 17:30
There's an answer here: http://stackoverflow.com/questions/282322/sql-numbering-the-rows-returned-by-a-select-statement — quillbreaker, Nov 08 '13 at 16:08
Isn't this question better suited to https://dba.stackexchange.com ? — joce, May 02 '14 at 15:29

score 1 · Answer 1 · edited May 23 '17 at 12:21

Without knowing why you can't use an identity, or how you use them (e.g. primary key, count of records, sequence of events), I'm not sure anyone can recommend a particular replacement.

That said, I think you are looking to create a sequence.

SQL Server 2012 adds sequences.

Older versions of SQL Server require different methods to implement this, though they all have downfalls compared to native identities or sequences.

score 0 · Answer 2 · answered Jun 14 '13 at 15:41

I'd be interested to find out why your new environment does not allow identity columns (not saying there's not a good reason, but I've never encountered that before).

With that said, the best solution I can think of is to use a trigger on inserts that finds the max value of the pseudo-identity column and adds 1.

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

Here's one technique I've used. It's not without its warts, but it does get the job done without any annoying race conditions. It's my answer to sql server: generate primary key based on counter and another column value

edited May 23 '17 at 11:50

Community

1
1

answered Jun 14 '13 at 17:28

Nicholas Carey

71,308
16
93
135

How do I homebrew an identity column?

3 Answers3