Generate unique random integers without collisions in sql server

Here is the solution to generate unique number  in sql server. We may use one of these calculations to generate a number in this set:

SELECT

  1000000 + (CONVERT(INT, CRYPT_GEN_RANDOM(3)) % 1000000),

  1000000 + (CONVERT(INT, RAND()*1000000) % 1000000),

  1000000 + (ABS(CHECKSUM(NEWID())) % 1000000);

But think again, Is number unique ?

Generating a random number on its own is not difficult, using methods like RAND() or CHECKSUM(NEWID()). The problem comes when you have to detect collisions. Let's take a quick look at a typical approach, assuming we want CustomerID values between 1 and 1,000,000: 

DECLARE @rc INT = 0,

  @CustomerID INT = ABS(CHECKSUM(NEWID())) % 1000000 + 1;

              -- or ABS(CONVERT(INT,CRYPT_GEN_RANDOM(3))) % 1000000 + 1;

              -- or CONVERT(INT, RAND() * 1000000) + 1;

WHILE @rc = 0

BEGIN

  IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerID = @CustomerID)

  BEGIN

    INSERT dbo.Customers(CustomerID) SELECT @CustomerID;

    SET @rc = 1;

  END

  ELSE

  BEGIN

    SELECT @CustomerID = ABS(CHECKSUM(NEWID())) % 1000000 + 1,

      @rc = 0;

  END

END

Wait, As the table gets larger, not only does checking for duplicates get more expensive (More time required to complete the process)

Different Approach

One idea is to pre-calculate a large number of random number and store it in different table 

CREATE TABLE dbo.RandomIDs

(

  RowNumber INT PRIMARY KEY CLUSTERED,

  NextID INT 

) WITH (DATA_COMPRESSION = PAGE);

-- data compression used to minimize impact to disk and memory

-- if not on Enterprise or CPU is your bottleneck, don't use it

;WITH x AS

(

  SELECT TOP (1000000) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])

    FROM sys.all_objects AS s1

    CROSS JOIN sys.all_objects AS s2

    ORDER BY s1.[object_id]

)

INSERT dbo.RandomIDs(RowNumber, NextID)

  SELECT rn, ROW_NUMBER() OVER (ORDER BY NEWID()) + 1000000

  FROM x;

Now, in order to generate the next ID, we can simply delete the lowest RowNumber available, and output its NextID for use. We'll use a CTE to determine the TOP (1) row so that we don't rely on "natural" order 

DECLARE @t TABLE(NextID INT);

;WITH NextIDGenerator AS

(

  SELECT TOP (1) NextID FROM dbo.RandomIDs ORDER BY RowNumber

)

DELETE NextIDGenerator OUTPUT deleted.NextID INTO @t;

INSERT dbo.Users(UserID /* , other columns */)

  SELECT NextID /* , other parameters */ FROM @t;

Decorator Design Pattern - C#

Attaching additional responsibilities to an object dynamically. Decorators provide a flexible alternative to subclassing for extending functionality.

The classes and objects participating in this pattern are:

• Component 
  • defines the interface for objects that can have responsibilities added to them dynamically.
• ConcreteComponent   
  • defines an object to which additional responsibilities can be attached.
• Decorator 
  • maintains a reference to a Component object and defines an interface that conforms to Component's interface.
• ConcreteDecorator 
  • adds responsibilities to the component.

Code :-

//Component
namespace DecoratorDemo.Component
{
    public interface IHome
    {
        string Make { get; }
        double GetPrice();
    }
}


//Concrete Component

  public class MyHome : Component.IHome
    {
        public string Make {
            get
            {
                return "MyHome";
            }
        }

        public double GetPrice()
        {
            return 8000000;
        }
    }


//Decorator
 public abstract class HomeDecorator : IHome
    {
        private readonly IHome home;

        public HomeDecorator(IHome home)
        {
            this.home = home;
        }
        public string Make
        {
            get
            {
                return "MyHome";
            }
        }

        public double GetPrice()
        {
            return home.GetPrice();
        }
        public abstract double GetDiscountPrice();
    }

  // Concrete Decorator
  public class OfferPrice : HomeDecorator
    {
        public OfferPrice(IHome home)  : base(home)
        {

        }
        public override double GetDiscountPrice()
        {
            return .8 * base.GetPrice();   
        }
    }

// Execution

 class Program
    {
        static void Main(string[] args)
        {
            IHome home = new MyHome();
            HomeDecorator decorator = new OfferPrice(home);

            Console.WriteLine(string.Format("Make : {0} Price {1} DiscountPrice {2}", decorator.Make, decorator.GetPrice(), decorator.GetDiscountPrice()));
        }
    }


OutPut
Make : MyHome Price 8000000 DiscountPrice 6400000