We often find ourselves in the need of generating unique numbers in our database applications.
Let us quickly take a tour of how to do that using Sql server 2005.
SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :
Generate GUID's :
UniqueIndentifiers are also knows as GUID's. To generate a GUID use :
SELECT NEWID() as Guid
generates e00ab1d2-7484-410b-a55a-bcfa131a727e on my machine
Generate only digits :
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo
generates 427357674589
Generate fixed digit unique numbers :
At times, we may also need to generate fixed digit numbers. You can do that in the following manner :
SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)
use GUID with string datatype
ReplyDeletecan we use this along with insert commnad. like if i want to generate guid for password field, the entry should be made automaticaly as soon as a row is inerted in table. is it posible
ReplyDeleteSure we can use it in insert Query directly.Like if Table has 2 fields
ReplyDelete1. userid
2. password
then query will be
insert into TableName(userid,password) values(@userid,newid())newid() can use directly in all type of querybut there are some problems while passing it in a procedure as parameter
Greate amit!! doing good job and this is a nice post.
ReplyDelete