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.
| Author |
Topic |
|
zare
Starting Member
1 Post |
 Posted - 2012-07-13 : 08:35:19
|
| I'm interested if there is a simple (fast) way to increment my identity fields in all tables in my database so they are "unique globaly"For instance, if I have two tables Table1 and Table2 with valuestable 1Id Name1 wtftable2Id Name2 loland I want to insert another value into table 1 and I want it to have it's Id value incremented to '3' because there's already Id=2 in table 2, how do I do that? Consider that I have dozens of tables.Thx in adv! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-13 : 09:35:50
|
| SQL Server 2012 introduced sequences to accomplish that feature. If you can't upgrade you can try the following:1. Add a rowversion column to all the tables you want to increment.2. Make the ID columns default to CAST(@@DBTS as bigint)@@DBTS is the database-level rowversion value. Any UPDATE or INSERT operations on a table with a rowversion column will increment it. Naturally the downside is that UPDATEs increment it as well as INSERTs, and it also increments even if the transaction rolls back. If you don't care about this behavior and just want an incrementing value, this is probably the easiest way to do it without sequences. |
 |
|
|
|
|
|
Autoincrement from highest Id in the database