Arbitrary Groups in SQL Server Using Windowing

I often need to divide recordsets into groups for various reasons.  For example, if we’re testing an application, the testers might want a census of sample users they’re going to test with, and I want to assign each sample user to a test group to ensure that they’re only tested once.  This can be done using windowing in SQL Server (windowing was introduced in SQL Server 2005).

Here’s an example:

--create a sample dataset  
   
 create table #temp (MyRecord integer identity(1000, 7), [Name] nvarchar(50));  
   
 declare @x integer = 1;while @x <= 500 begin  
      insert into #temp ([Name]) values ('Sample ' + convert(nvarchar(40), getdate(), 121));  
      set @x = @x + 1;  
 end;  
   
 -- divide into groups  
   
 declare @Groups decimal(20, 4) = 30;-- how many groups we want. The overflow will go into another group  
   
 select  
   
      [Testing Group] =   
           convert(integer,   
                round(  
                     row_number() over (order by t.MyRecord)   
                     / (count(t.MyRecord) over () / (@Groups)) -- this is going to be the size of each group  
                     + 1,  
                     0, 1 -- arguments for the round function. This will cause the decimal to be truncated                 
                )  
           ),  
                                 
      t.*  
        
 from  
      #temp t;  

There are probably more elegant solutions, and the code looks a little messy, but it works.