How to automatically generate unique id in SQL like UID12345678?

by

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:

enter image description here

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  3. T-SQL split string based on delimiter
  4. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  5. Convert Date format into DD/MMM/YYYY format in SQL Server
  6. Using IF ELSE statement based on Count to execute different Insert statements
  7. Select SQL Server database size
  8. How to subtract 30 days from the current date using SQL Server
  9. How do I find the data directory for a SQL Server instance?
  10. Cannot Resolve Collation Conflict
  11. Try_Convert for SQL Server 2008 R2
  12. SQL Server: +(unary) operator on non-numeric Strings
  13. CROSS JOIN vs INNER JOIN in SQL
  14. How to cast the DateTime to Time
  15. How can I schedule a job to run a SQL query daily?
  16. SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]
  17. Reset identity seed after deleting records in SQL Server
  18. How to avoid the “divide by zero” error in SQL?
  19. Multiple INSERT statements vs. single INSERT with multiple VALUES
  20. Real life example, when to use OUTER / CROSS APPLY in SQL
  21. Common Table Expression, why semicolon?
  22. How do I find duplicates across multiple columns?
  23. How to get last 7 days data from current datetime to last 7 days in sql server
  24. sql varchar(max) vs varchar(fix)
  25. store arabic in SQL database
  26. Converting Select results into Insert script – SQL Server
  27. How to combine results of two queries into a single dataset
  28. How to get Previous Value for Null Values
  29. Display next event date
  30. Sql query for tree table

Leave a Comment