T-SQL : generate combination of numbers and save into table

Question

1.00/5 (1 vote)

See more:

I have the query that generates all possible combinations of 6 numbers in the range from 1 to 90. However it stops working after some time generating the following error:

"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

Is there a way around this and how to save result to a table (not a temp table)?

What I have tried:

SQL

WITH Numbers(N)
     AS (SELECT number
         FROM  master..spt_values
         WHERE type='P'
         AND number BETWEEN 1 AND 90)
SELECT *
FROM   Numbers N1
       JOIN Numbers N2
         ON N2.N > N1.N
       JOIN Numbers N3
         ON N3.N > N2.N
       JOIN Numbers N4
         ON N4.N > N3.N
       JOIN Numbers N5
         ON N5.N > N4.N
       JOIN Numbers N6
         ON N6.N > N5.N

Posted 8-Sep-20 20:57pm

Zaur Bahramov

Updated 8-Sep-20 21:19pm

Add a Solution

2 solutions

Solution 1

Quote:
I have the query that generates all possible combinations of 6 numbers in the range from 1 to 90. However it stops working after some time

Your query generate billions of records in one go and you wonder why server chock ?
Feels like a wrong solution to another question, we usually avoid such queries.

Tell us what is the question/usage you try to answer.
Your approach is probably wrong.

Posted 8-Sep-20 21:10pm

Patrice T

Comments

Zaur Bahramov 9-Sep-20 4:24am

Actually, there are no repeats and the tuples are unique, so it is "only" 622,614,630 (90*89*88*87*86*85/6!).

Patrice T 9-Sep-20 4:31am

And you think only 600 millions records is reasonable ?

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2020-09-08T21:19:00

Reference: https://support.microsoft.com/en-in/help/2874903[^]

Cause:
This issue occurs because SSMS has insufficient memory to allocate for large results.

Note SSMS is a 32-bit process. Therefore, it is limited to 2 GB of memory. SSMS imposes an artificial limit on how much text that can be displayed per database field in the results window. This limit is 64 KB in "Grid" mode and 8 KB in "Text" mode. If the result set is too large, the memory that is required to display the query results may surpass the 2 GB limit of the SSMS process. Therefore, a large result set can cause the error that is mentioned in the "Symptoms" section.

As suggested there, to work around this issue, try one of the following methods:

Method 1: Output the results as text

Configure the query window to output the query results as text. A text output uses less memory than the grid, and it may be sufficient to display the query results. To make this change, follow these steps:

Right-click the query window.
Click Results to.
Click Results to Text.

Method 2: Output the results to a file

Configure the query window to output the query results to a file. A file output uses a minimal amount of memory. This reserves more memory for storing the results set. To make this change, follow these steps:

Right-click the query window.
Click Results to.
Click Results To File.
Run the query, and then select the location in which to save the results file.

Method 3: Use sqlcmd

Use the sqlcmd tool[^] instead of SSMS to run the SQL queries. This method enables queries to be run without the resources that are required by the SSMS UI. Additionally, you can use the 64-bit version of Sqlcmd.exe to avoid the memory restriction that affects the 32-bit SSMS process.

T-SQL : generate combination of numbers and save into table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

T-SQL : generate combination of numbers and save into table

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us