Create unique value in MS SQL
DECLARE PkStudentid as INT
CREATE TABLE Student
(
Studentid int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
Firstname nvarchar (200) NULL,
Lastname nvarchar (200),
Email nvarchar (100) NULL
)
insert into Student (Firstname,Lastname,Email)
Values('Vivek', 'Johari', ‘vivekjohari@abc.com');
SET @PkStudentid = SCOPE_IDENTITY();
print @PkStudentid
This will add Studentid as 1 and next entry 2 and in ascending order.
You need to use the IDENTITY property in SQL Server.
Edited: Use SCOPE_IDENTITY in SQL Server to return the last inserted Studentid.
source to share
For primary key column, you need to use INT column with IDENTITY insert.
Alternative solution:
However, if you do not want to use the INT datatype, an alternative solution is the create column with a default LEFT(NEWID(), 10)
UNIQUE / PRIMARY KEY. Since the function NEWID()
generates different random strings every time.
Example:
SELECT LEFT(REPLACE(NEWID(),'-',''),(10))
The above query will give a different random string.
Check SQL FIDDLE DEMO :
OUTPUT
| ID | NAME |
|------------|------|
| 482E5D4850 | pqr |
| 70369ED157 | abc |
| 768CC98442 | xyz |
source to share
ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY
Use MS SQL IDENTITY property together with PRIMARY KEY for uniqueness.
Columns Identity
can be used to generate key values. The identity property on a column guarantees the following:
- Each new value is generated based on the current seed and increment.
- Each new value for a particular transaction is different from other concurrent transactions in the table.
- (1,1) - Initial number and Increment value, which you can get at will.
source to share