I have primary key unique column ID nchar(10). I'm planning to generate in it numeric ID. Incremental order is ok, but not necessary. How to make optimal generator for this reason? Should I use stored procedure? Maybe MS SQL server have any features for this reason?
-
2You could make it an IDENTITY column for sequential numbers.shree.pat18– shree.pat182014-12-02 09:11:22 +00:00Commented Dec 2, 2014 at 9:11
-
1Why you're using nchar(10) you should use INT with PRIMARY KEY and IDENTITY property on same column.Vishwanath Dalvi– Vishwanath Dalvi2014-12-02 09:13:13 +00:00Commented Dec 2, 2014 at 9:13
-
2Why use a nchar for a numeric ID? That is like "Ok, let me try the worst and slowest and most problematic solution because I really want to look like I never read about data types".TomTom– TomTom2014-12-02 09:17:57 +00:00Commented Dec 2, 2014 at 9:17
Add a comment
|
3 Answers
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', ‘[email protected]');
SET @PkStudentid = SCOPE_IDENTITY();
print @PkStudentid
This will insert Studentid as 1 and next entry 2 and in increment order.
You need to use IDENTITY property in SQL Server.
Edited : use SCOPE_IDENTITY in SQL Server to get back latest inserted Studentid.
3 Comments
vico
Ok , that is fine and is what I need. But I need to get this unique ID during insertion
Vishwanath Dalvi
@vico Are you going to use the latest inserted Studentid somewhere?
vico
No, I will need to get it back to my programm
For primary key column you should have to use INT column with IDENTITY insert on.
Alternate solution:
Still if you don't want to use INT data type then the alternate solution is create column with default value LEFT(NEWID(), 10) with UNIQUE/PRIMARY KEY on it. Because NEWID() function generates different random string every time.
For Example:
SELECT LEFT(REPLACE(NEWID(),'-',''),(10))
Above query will give different random string.
Check SQL FIDDLE DEMO:
OUTPUT
| ID | NAME |
|------------|------|
| 482E5D4850 | pqr |
| 70369ED157 | abc |
| 768CC98442 | xyz |
Comments
ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY
Use the IDENTITY property in MS SQL along with PRIMARY KEY for uniqueness.
Identity columns can be used for generating key values. The identity property on a column guarantees the following:
- Each new value is generated based on the current seed & increment.
- Each new value for a particular transaction is different from other concurrent transactions on the table.
- (1,1) - Initial number and Increment value you can have then on your wish.
Comments
