Automatically assigning a random string to new records

I already have a customer table, but from now on new records have to be assigned a unique random alphanumeric string of 5 characters in a new column. Is there anyways I can do this without having to use Trigger (on insert?)

From stackoverflow

• You could use a default constraint that calls a function that generates your random string.

• For the trigger: Well.. I'd use a trigger It has the advantage of working with previous code.

  But you could also create a stored procedure that wraps your inserts into the table.

  For the unique 5 characters: You could create an md5 over your initial set and maybe time, then recode it from base 10 to base 255.

  The leftovers can be truncated.

• The default constraint calling a function would work best. However you should examine how many records you'll be creating.

  While 5 characters may be more then enough to uniquely identify your intended number of records (assuming A-Z, 0-9 I believe that's about 60 million codes), when you start randomly generating them you may find that you hit the birthday paradox rather quickly.

  Haoest : I like that approach, but I can not verify the uniqueness of the string generated by the function by looking through the customer table, though. Any ideas?

  David : Generally the function has a (fixed!) loop that generates a random code, and then does a IF EXISTS(SELECT...) to determine if the code has already been used. If it has, it goes through another interation of the loop until if finds one or runs out of tries and throws an error.

• I had a similar situation but was generating the string in the code then sending it to the DB. Of course this is used on a very small table so I was not to worried about speed. Thought it might help, though it assumes you are using c#.

      /// Generates a string and checks for existance
      /// <returns>Non-existant string as ID</returns>
      public static string GetRandomNumbers(int numChars, string Type)
      {
          string result = string.Empty;
          bool isUnique = false;
  
          while (!isUnique)
          {
              //Build the string
              result = MakeID(numChars);
              //Check if unsued
              isUnique = GetValueExists(result, Type);
          }
          return result;
      }
      /// Builds the string 
      public static string MakeID(int numChars)
      {
          string random = string.Empty;
  
          string[] chars = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
          Random rnd = new Random();
          for (int i = 0; i < numChars; i++)
          {
              random += chars[rnd.Next(0, 35)];
          }
          return random;
  
      }
      /// Checks database tables based on type for existance, if exists then retry
      /// <returns>true or false</returns>
      private static bool GetValueExists(string value, string Type)
      {
          bool result = false;
  
          string sql = "";
          if (Type == "URL")
          {
              sql = string.Format(@"IF EXISTS (SELECT COUNT(1) FROM myTable WHERE uniqueString = '{0}') 
              BEGIN 
                  SELECT 1 
              END  
              ELSE  
              BEGIN 
                  SELECT 0 
              END ", value);
          }
          //query the DB to see if it's in use
          result = //ExecuteSQL
  
          return result;
      }
  

  Haoest : Oohhh, I was hoping to implement this feature in DB level, but if it comes to that I will have to escalate.