t-sql autogenerate code

ukerandi
ukerandi used Ask the Experts™
on
I have used following code to create unique id, if we deleted details is it possible to
come same number
NEWID()
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
No, NEWID will always give you a new ID. You can't generate same ID again.
If you want to get the unique value always and wanted to get back the value then use date time functions such as getdate()
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Vitor is correct in that the answer is 'No, you can't do that'.

If this is a hard business requirement to resurrect previously deleted NEWID() values, you may want to consider a 'soft delete' where there is a bit column named something like IsDeleted that contains a 1 for deleted rows and 0 for active rows.  That way the record still exists even when 'deleted', and to make the row active again all you have to do is change IsDeleted back to 0.

Either that or build some kind of backup/restore strategy where rows marked as deleted are moved to another table/database/whatever, and there is functionality to make those rows active again by a separate process to access them which moves the row back.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
NEWID() can in theory give you duplicate values.  It's extremely unlikely, but in theory it can happen, and MS makes no assurances that it won't.  Odds are, though, it won't happen in your lifetime, so it will be someone else's problem if it does :).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial