Generate a Unique Time-Based Id on a Table in SQL Server

Generate a unique time-based id on a table in SQL Server

First, read all the excellent advice from the comments and other answers about why you probably should do something different.

But, yes you can do this, and here's how. Basically just replace the last few digits of the datetime2 with the values from a sequence. The more digits you replace, the more rows you can insert at the same time without violating the unique constraint. In the example below I'm replacing everything past the 10th of a second with the sequence value. If you wanted to retain more digits of sub-second precision to store the actual clock time at which the insert was attempted, you would just reduce the maximum number of rows you can insert in a batch. So up to you.

Here it is:

drop table if exists MyTableWithUniqueDatetime2

drop sequence if exists seq_ts_nanoseconds
go
create sequence seq_ts_nanoseconds
start with 0 
increment by 100
minvalue 0
maxvalue 99999900
cycle

go
create or alter function GetTimestamp(@ts datetime2, @ns int)
returns datetime2(7)
as
begin
 return  dateadd(ns,@ns, dateadd(ns,-(datepart(ns,@ts) % 100000),@ts))
end
go
create table MyTableWithUniqueDatetime2
(
  id bigint identity,
  a int,
  b int,
  ts datetime2(7) default dbo.GetTimestamp(sysdatetime(), (next value for seq_ts_nanoseconds)) unique

)
go
select sysdatetime()
insert into MyTableWithUniqueDatetime2 (a,b)
output inserted.*
select top (1000) 1,2
from sys.objects o, sys.columns c, sys.columns c2

Generate unique Identifier for table

If you want a 32-bit unique identifier that is unique on each row . . . well, you have just defined the identity column:

create table . . . (
    id int identity primary key,
    . . .
);

This will, of course, be a sequential number, but that meets your conditions.

EDIT:

If you want newid() then use a default value:

create table . . . (
    id uniqueidentifier primary key default newid(),
    . . .
);

Of course, newsequentialid() is a better choice than newid(), but your sample code uses newid().

A uniqueidentifer is 16 bytes (128 bits), so it doesn't meet your needs. I don't think there is any other built-in mechanism for a 32-bit unique number that is guaranteed to be unique.

How to automatically generate unique id in SQL like UID12345678?

The only viable solution in my opinion is to use

• an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
• a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.tblUsers
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   UserID AS 'UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into tblUsers without specifying values for ID or UserID:

INSERT INTO dbo.tblUsersCol1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and UserID will contain values like UID00000001, UID00000002,...... and so on - automatically, safely, reliably, no duplicates.

Update: the column UserID is computed - but it still OF COURSE has a data type, as a quick peek into the Object Explorer reveals:

How to generate unique id based on start date and end date for the next 40 years in SQL table

The simplest way to do this is to have a lookup table, where each row contains one of the items you wish to track. Add a column that generates a default unique surrogate key (an “identity” column, in SQL Server, and virtually all RDBMSes have something similar) whenever a row is added.

That works if the value is just a date range, such as Jan 1, 2017 through Dec 31, 2017. If your unique value is based on the date range and the user name (example: MJ8 + Jan 1, 2017 + Dec 31, 2017), well, that’s probably a central table in your database schema, and is even more deserving of a surrogate key.

Surrogate keys also help manage situations where you have duplicates (say, two entries for Smith + Jan 1, 2017 + Dec 31, 2017) -- one could be ID = 3, the other ID = 8710.

A good argument for not basing a unique value on your “source data” is that it is smart data. Your unique id might end up being a string (or a very big number) like 2017010120171231. Awkward, but it works. The danger is, users (or developers) might see this and decide that, rather than “track it back to the source” (by perhaps joining on this key to the table containing the source data), they’ll just parse the string and convert each half back into the original date. This is generally not a good idea, as it slows down processing, produces hard to read (and debug and maintain) code, and can make things very difficult if the data being referenced changes.

How to Create and Insert Unique Id's Based on another column values in single table?

com/users/3611669/guidog - first of all , thank you so much for answer, I tried your answer, but don't know ,whats wrong, its not working exactly as per expectation, finally , I tried different methods ,and found out this answer bro/sis...

CREATE PROCEDURE SP_INSERT_WITH_REVISIONID       
(@Id int       
)       
AS    
BEGIN
DECLARE @RevisionId int
If EXISTS(SELECT  * FROM TBL_A WHERE A_Id=@Id)
 
BEGIN 
SET @RevisionId = ISNULL((        
      SELECT MAX(Revision_Id) + 1        
      FROM TBL_A  where  A_Id=@Id       
   ), 0) 
INSERT INTO TBL_A
select @RevisionId,
       A.A_ID,
       A.A_Name,
       A.A_Info  from TBL_DATA A where A_Id=@Id
       
END
ELSE
BEGIN
INSERT INTO TBL_A
select 0,
       A.A_ID,
       A.A_Name,
       A.A_Info  from TBL_DATA A where A_Id=@Id
       END     
       END

and My id is auto increment one. This one finally fixed my issue, I am sharing here ,so that ,it may help someone in future. Do let me know if there is any concern.

---

Related Topics