DEV Community

Cover image for SQL-Quick tip #1 - Range of Int
Allan Simonsen
Allan Simonsen

Posted on • Edited on

3 2 1

SQL-Quick tip #1 - Range of Int

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
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay