SQL-Quick tip #1 - Range of Int

#tsql #sqlserver #sql #mssql

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Generate a range of int

When testing your code you may need a range on integer and the trick for generating such a range is to use the ROW_NUMER() OVER(PARTITION BY 1 ORDER BY somecolumnOfSomeTable)

In the code below we are using the syscolumns table, so be aware that this specific query will only generate up to about 3000 number. To increase the number of returned rows you have to be creative using syscolumns cross join with the syscolumns table that will return more than 9 million rows or use one of your own table that has the appropriate number of rows.

DECLARE @range TABLE ([Range] INT)
DECLARE @numberOfInts INT = 24

 INSERT INTO @range
 SELECT 0 AS Range
  UNION
 SELECT TOP (@numberOfInts) ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY A.name) AS 'Range'
   FROM syscolumns A

SELECT * 
  FROM @range