CODEWITHSUNDEEP
c#asp.netsql-server-2008
Karadous
Karadous

Reputation: 1625

How to fill a field with random unique numbers in SQL2008

I have a table in which there is an int field. This field should be filled with random number and it should be unique. What is the best solution? Should I generate a random number and check if it exists in the table? By the way I'm developing a web application using ASP.NET and C#.

Upvotes: 0

Views: 523

Answers (4)

Adam
Adam

Reputation: 26917

Do you really need it to be random? If you just need an unique integer value, use Identity. Check this link

Edit: For random number (BigInt) you may try this:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo

Found it in here

Upvotes: 2

Nilish
Nilish

Reputation: 1076

From Front End using C# 

Guid.NewGuid().ToString("N") //Replaces all - will Empty values. 
                               Final value will be numeric

From Database

newid()

Both these techniques will give you unique results.

Upvotes: 0

angus
angus

Reputation: 690

Try http://msdn.microsoft.com/en-us/library/system.random.aspx to generate the random number and then query the database to check if the value exists. You should be able to determine it by selecting rows where 'int field' = 'random number' and checking whether the @@rowcount = 0.

Upvotes: 0

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

Use GUID:

public string GetRandomGUID(int length)
{
  // Get the GUID
  string guidResult = System.Guid.NewGuid().ToString();

  // Remove the hyphens
  guidResult = guidResult.Replace("-", string.Empty);

  // Make sure length is valid
  if (length <= 0 || length > guidResult.Length)
    throw new ArgumentException("Length must be between 1 and " + guidResult.Length);

  // Return the first length bytes
  return guidResult.Substring(0, length);
}

Upvotes: 0

Related Questions