July 19, 2023 at 5:52 pm
            
#4239357

            
            
                                                
        

I am with Grant - I don't see any reason to be splitting a table.  No matter what - all development groups will still need access to the original...

Because of the row size limitation in SQL Server, I had to split the columns of a data warehouse table. The combined size of the rows exceeded the permitted limit.

To...

I've had to add an identity column (ID) so the table can be ordered by something:

DECLARE @T table (ID int IDENTITY(1, 1),  A int )

insert 
into...

There is nothing to order the table by. Maybe you should add an identity column to your table first.

Yes,...

There is an article on the same problem here: https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

What State did the error return? (1 or 2)

State 1 - The query timed out due to the...

drop table if exists #t1
drop table if exists #t2
drop table if exists #t3

go
set statistics io, time on
go
SELECT p.FIRST_NAME,
       p.LAST_NAME,
  ...

I've got a (8 years old) Dell XPS 8700 desktop with a  i7-4790 and 28GB RAM, and a SanDisk Ultra II 960GB SSD with the tempdb on...

Set up test data

set statistics io, time off
DROP TABLE IF EXISTS dbo.PATIENTS;

CREATE TABLE dbo.PATIENTS 
(
    FIRST_NAME        varchar(50),
...

Ah... I see my confusion here.  The op posted a recursive CTE where he said it wouldn't work  with the  word RECURSIVE  and Frederico said that RECURSIVE  is not...

I think you might want a recursive common table expression.

Something like this:

DECLARE @n int = 3;
WITH Factorial(F,n) AS 
(
    SELECT 1 ...

I think you might want a recursive common table expression.

Something like this:

DECLARE @n int = 3;
WITH Factorial(F,n) AS 
(
    SELECT 1   ...

You could do with reading this: How to post code problems

DROP TABLE IF EXISTS dbo.PATIENTS;

CREATE TABLE dbo.PATIENTS 
(
    FIRST_NAME  ...

If you use:

SET STATISTICS IO, TIME ON

you will also get the CPU usage for the query.