SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Autoincrement from highest Id in the database		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zare
Starting Member

Croatia
1 Posts
Posted - 07/13/2012 :  08:35:19  Show Profile  Reply with Quote
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 values

table 1
Id Name
1 wtf

table2
Id Name
2 lol


and 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

USA
15566 Posts
Posted - 07/13/2012 :  09:35:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000