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:
I want to automatically generate unique id in sql
From the MySQL documentation, only the NDB storage engine supports computed columns. My guess is that you are using the default storage engine InnoDB, so you would not be able to use computed columns.
In addition, you seem to be mixing SQL Server and MySQL syntax.
Taking both of these into account, I suggest using the following create statement, and then building a view on top of your table which generates the item ID you want:
CREATE TABLE Foods (
ID int NOT NULL AUTO_INCREMENT,
item_name varchar(10),
price int,
PRIMARY KEY(ID)
);
Then, create a view which can turn out the computed items you want:
CREATE VIEW Foods_View AS (
SELECT
ID,
RIGHT(CONCAT('00000000', CAST(ID AS CHAR(8))), 8) AS ItemId,
item_name,
price
FROM Foods
)
How to calculate the (custom id) for a row which has not been inserted yet?
You need to execute this query to get the next identity which will be generated for the table:
SELECT IDENT_CURRENT('table_name')+1;
For your case, it will have some other info concatenated with the next identity so the query will be like this:
SELECT 'UID' + RIGHT('00000000' + CAST(IDENT_CURRENT('table_name')+1 AS VARCHAR(8)), 8)
Of course you will need to write the C# code to send that query to the SQL Server.
Having said that, keep this in mind: When you get the value from that call and hold onto it, if during the time you are holding the value a record is inserted into that table, then the value is no longer the next value.
If you need the identiy value after a record is inserted in your application, please refer this answer.
SQL: Oracle 19c unique ID
Use an identity column and a virtual column:
CREATE TABLE table_name
(
id INT GENERATED ALWAYS AS IDENTITY
NOT NULL
CONSTRAINT table_name__id__pk PRIMARY KEY,
userid VARCHAR2(11)
GENERATED ALWAYS AS (
CAST('UID' || TO_CHAR(id, 'FM00000000') AS VARCHAR2(11))
),
name VARCHAR2(50)
);
Then:
INSERT INTO table_name (name)
SELECT 'Alice' FROM DUAL UNION ALL
SELECT 'Beryl' FROM DUAL UNION ALL
SELECT 'Carol' FROM DUAL;
SELECT * FROM table_name;
ID USERID NAME 1 UID00000001 Alice 2 UID00000002 Beryl 3 UID00000003 Carol How to generate a unique transaction id in a shopping cart using postgresql
You could use a sequence:
CREATE SEQUENCE unique_id_seq;Then you can generate the unique identifier with the following SQL expression:
'TRX' || to_char(current_date, 'YYYYMMDD') || lpad(nextval('unique_id_seq'), 18, '0')That will not be a random value, and the prefix is not necessary to guarantee uniqueness, but it should fullfill the requirement.
Related Topics
How to Create a Decimal Field in Access with Alter Table
Oracle- Split String Comma Delimited (String Contains Spaces and Consecutive Commas)
How to Use If/Else Statement to Update or Create New Xml Node Entry in SQL
Checking If a String Is Found in One of Multiple Columns in MySQL
Select Statement to Return Parent and Infinite Children
Looping Through Column Names with Dynamic SQL
Spark Replacement for Exists and In
Incorrect Syntax Near the Keyword 'With'...Previous Statement Must Be Terminated with a Semicolon
Difference Between === Null and Isnull in Spark Datadrame
Convert from Date to Epoch-Oracle
Local Temporary Table in Oracle 10 (For the Scope of Stored Procedure)
SQL for Ordering by Number - 1,2,3,4 etc Instead of 1,10,11,12
How to Find Out What Is Locking My Tables